Database Discussion Boards

devvvy10-Jul-11 0:11

10-Jul-11 0:11

Many thanks

I understand the word "Heap" as I am a programmer - but how is it possible for SQL server to put in heap/memory RID's of all these million rows tables!

dev

Feeding parameters from a C# app into an SSIS package that converts query results to CSV and FTP [modified]

Alaric_5-Jul-11 12:34

Alaric_

5-Jul-11 12:34

Hi guys. This post is building upon the solution from my previous post in here on Jun 29th.

Synopsis: Building a scheduling/routing application on top of a data-driven app that lives in SQLServer that identifies customers that have dropped our service for "Winback" call center campaigns (and the like)

The logic for each of the campaigns is encapsulated within a query run within a stored procedure that is located on our Reporting database (it runs there because Reporting has the $**T indexed out of it) The stored procedure is executed from our local production database across a server link. My application knows absolutely nothing about the underlying stored proc. It knows of a command that it is wishing to execute for a particular campaign, and I have a mapping table that stores a pointer to the remote stored proc. I have a brokerage entry point exposed as a stored proc to my application that accepts parameters, dips into the local Mapping table, pulls the name of the stored proc, executes it across the database link, receives the result set, generates a GUID for the batch, enters the results into a local table called "Solicitations" with the GUID as a correlator and returns the GUID to the calling client.

...I was proud that I was able to accomplish all of that. I am nowhere *NEAR* what one would call a "DBA" and all of my "education" is in Oracle9i. I'm currently "working" in SQL Server 2008R2.

But now: All of the previous work has been the "sourcing" side of our process. Being able to get the data out of Reporting and into our local table is just the data pump that allows our campaigns to run. After the calling client receives the GUID as its return, I need to pass the GUID into an SSIS package that can receive that GUID, identify the campaign that is being run, dip into the mapping table to pick up the filename associated with that campaign, pull the relevant rows out of the Solicitations table, map them to a canonical format, and FTP them to an FTP server that acts as an integration point between our Customer/Account system and the system that manages call routing for our call center.

I have created an SSIS package that has a Data Flow task that Flat File Exports the Solicitations table to csv as "testy.csv" on my local file system and then an FTP task that picks that resulting file up and :21's it to the integration point. I went to the server and picked the file up and it was the file I was expecting. All of the guts of the "happy path" flow are there.

My actual question:
What I am needing to do is:
1) configure the working Data Flow Task such that it can receive a GUID and select items from a view where the correlator in the table matches the GUID for the batch. 
2) I'm also needing to create a task that receives that GUID and retrieves the OutputFilename from the Campaign Mapping table so that it knows which file to pick up and transport.
3) I'm needing to modify the FTP Task such that the filename is the variable set in (2) <-I'm thinking #3 is going to be the easy part.


Has anyone ever done this before? Can anyone provide an assist on how to accomplish 1-3 above?

Thank you very much!

"I need build Skynet. Plz send code"

modified message subject on Wednesday, July 6, 2011 1:24 AM

Re: Feeding parameters from a C# app into an SSIS package that converts query results to CSV and FTP

Alaric_7-Jul-11 6:44

Alaric_

7-Jul-11 6:44

I found an answer to my question. I created a stored procedure that accepts the GUID as a parameter and within the proc, I (create, add, and kick off) a job that executes the SSIS package as its only step, passing the GUID into the command line args of the command property of sp_add_jobstep.

Right now, the only thing in my way is that the server was restarted last night and the Job Agent wasn't part of the restart....so I'm waiting for server support to get back from lunch so that I can test out my implementation.

If anyone has any questions about my approach, I'd be more than happy to share.

"I need build Skynet. Plz send code"

Low-level filtering of IsDeleted bit field

army_man716554-Jul-11 11:39

army_man71655

4-Jul-11 11:39

Never actually deleting records can provide an extra layer of data protection.

A common approach is to have an IsDeleted flag on each database table to specify whether the record is "deleted" or not. However, without the proper low-level mechanism in place to filter out the records, bugs can begin to invade the application space (and they're not always easy to spot!).

In my case, I have written my own Entity classes to handle CRUD operations. The SELECT methods within these classes automatically filter on IsDeleted and the classes are auto-generated by a tool I wrote to ensure this issue is minimized. However, I recognize some folks may want to use other techniques to address this issue.

What technique(s) do you prefer? My goal is to initiate some dialogue and gain some feedback as to the various mechanisms used by fellow members on this particular subject matter. Thanks in advance.

Re: Low-level filtering of IsDeleted bit field

Johan Hakkesteegt5-Jul-11 1:48

Johan Hakkesteegt

5-Jul-11 1:48

The first thing that comes to mind, is the method used in the SAP ERP package that we use: history tables.

These tables are pretty much copies of productive tables, but with a different key structure (don't know exactly how), and are used to track changes to records, and keep copies of deleted records.

So for example, when I delete a product card from the system, it really is deleted from the products table, but a copy is left in the products-history table, with the status 'deleted'.

It works well for straightforward objects such as products and business partners, but there are volatile objects such as sales documents (having header and row data) that can cause the database to grow beyond application specs.

My advice is free, and you may get what you paid for.

History table

David Mujica5-Jul-11 3:25

David Mujica

5-Jul-11 3:25

Excellent idea. Simple, yet effective.

Here are some ideas to expand on that idea.

The history table could contain the following fields:
1) identity column which is the primary key
2) Date/timestamp of the user who performed the delete
3) A XML column where you could store an XML representation of the data that was deleted.

This provides a straight forward method to support all kinds of table layouts.

Maybe this logic could be implemeted as a stored procedure and your developers wouldn't even know about these gruesome details.

My 2 cents.
Good luck Cool | :cool:

See here about storing XML documents in SQL Server.
http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx[^]

Re: History table

Eddy Vluggen5-Jul-11 13:19

Eddy Vluggen

5-Jul-11 13:19

I do not see much advantages, unless both tables are located in separate files. Otherwise you'd be adding a lot of extra work, just to give it a new status. I'd still prefer having them in the same table, with an (indexed) attribute to make a distinction between the records with the status "deleted" and the ones that aren't.

Yes, you can omit checking that single attribute in each query on that table, that could be considered an advantage. There's more disadvantages; you'll be maintaining two definitions of the same table in each database, and it'd add extra strain in terms of physically moving the data and updating indexes.

Most of the time, a simple DateTime to indicate the date/time of removal (null if not removed) is enough to provide simple "undelete" functionality. That's from a YAGNI/KISS viewpoint - it might make sense to have history-tables depending on your needs.

In that case I'd recommend to add some triggers and log it in a copy of the database, located on the same server. You can easily write a script that clears all tables in a database, and it'd be easy to make separate backup-plans.

Bastard Programmer from Hell Suspicious | :suss:

Re: Low-level filtering of IsDeleted bit field

jschell5-Jul-11 6:20

jschell

5-Jul-11 6:20

Member 8037172 wrote:
What technique(s) do you prefer?

My technique is to first start by looking at the business requirements of the application and the enterprise.

Attempting a solution without doing that is pointless.

For example there is a significant difference in user auditing versus supporting an undo.

Not to mention that volume for any situation is a significant factor. If you expect 1000 inserts/updates a second it much more significant than if you expect 1 an hour.

One must at least consider if time should be spent on archiving strategies.

Re: Low-level filtering of IsDeleted bit field

Mycroft Holmes5-Jul-11 14:21

Mycroft Holmes

5-Jul-11 14:21

I'm with JSchell here, it really is a requirements decision rather than a general rule. Different databases serving different types of business needs require different delete strategies.

I often use a disabled flag on the record but never make it a general rule. About the only general rules we have are an audit trigger (spit I hate triggers) and Modified/ModifiedBy on every table.

Never underestimate the power of human stupidity
RAH

Re: Low-level filtering of IsDeleted bit field

Shameel7-Jul-11 3:20

Shameel

7-Jul-11 3:20

I would create a View with a WHERE IsDeleted = false condition and use the view in my select methods. And I would also make sure that the IsDeleted column is indexed.

UnQuote a nvarchar() string and use it as a table name

reza assar4-Jul-11 1:57

reza assar

4-Jul-11 1:57

hi all
suppose i have a table named "PlaceHolder" with this fields

ID int
TableName nvarchar
FieldName nvarchar

these fields will indicate specified table.field in my databse
so i want to retrieve them and use it`s content as a tableName.fieldName in my select transaction

SELECT [SQL1].[NewFieldName]------> REPLACED WITH '[TableName].[FieldName]' RETREIEVED BY INNER SELECT FROM
	(SELECT '['+[TableName]+']' + TableName + '.['+[FieldName]+']' NewFieldName
	 FROM [PlaceHolder]
	 WHERE ....
	) AS SQL1
WHERE ...

it will show me the names of some [tables].[field] but i want it`s values in these fields

Re: UnQuote a nvarchar() string and use it as a table name

Shameel4-Jul-11 4:46

Shameel

4-Jul-11 4:46

Construct a dynamic query in a string and use EXECUTESQL to execute the query.

Re: UnQuote a nvarchar() string and use it as a table name

PIEBALDconsult4-Jul-11 6:01

PIEBALDconsult

4-Jul-11 6:01

I prefer to use String.Format to construct something like that; all those quotes, plusses, and brackets get in the way of understanding what's going on.
Otherwise it should work fine.

Re: UnQuote a nvarchar() string and use it as a table name

Niladri_Biswas4-Jul-11 17:03

Niladri_Biswas

4-Jul-11 17:03

Hi try this

Input:

Declare @tblPlaceholder table(ID int,TableName Nvarchar(20),FieldName Nvarchar(20))
insert into @tblPlaceholder 
	select 1,'spt_values','name' union all select 1,'spt_values','number' union all
	select 2,'spt_monitor','lastrun' union all select 2,'spt_monitor','cpu_busy'
--select * from @tblPlaceholder

Query:

Declare @t table(ID int,Query VARCHAR(2000))
Declare @QueryList VARCHAR(2000)
Declare @i int 
set @i = 1

-- Step 1: Build the query and insert the same into a table variable
Insert into @t 
Select ID, Query = ' Select ' + FieldNames + ' from ' + TableName
From
(
Select ID,TableName, FieldNames = Stuff((select ',' + CAST(TableName as Nvarchar(20)) + '.' + CAST(FieldName as Nvarchar(20)) 
			from @tblPlaceholder t2 where t2.ID = t1.ID
			FOR XML PATH('')),1,1,'')
From @tblPlaceholder t1
Group By t1.ID,t1.TableName)X

/* output of step 1
select * from @t

ID	Query
1	 Select spt_values.name,spt_values.number from spt_values
2	 Select spt_monitor.lastrun,spt_monitor.cpu_busy from spt_monitor
*/


-- Step 2 : loop thru the ID and execute the queries
While (@i <= 2) -- since there are two id's.. this can even be configurable as max(id)
Begin
	  SELECT @QueryList = (Select Query from @t where ID = @i)
	  exec(@QueryList)	  
	  set @i  += 1
End

/* Final output

	Output of Select spt_values.name,spt_values.number from spt_values
	
	name	number
	(rpt)	-32768
	YES OR NO	-1
	SYSREMOTELOGINS TYPES	-1
	SYSREMOTELOGINS TYPES (UPDATE)	-1
	
	
	Output of Select spt_monitor.lastrun,spt_monitor.cpu_busy from spt_monitor
	
	lastrun					cpu_busy
	2008-07-0916:46:13.877	10
*/

I have given the demonstration by using two system tables (spt_values and spt_monitor) found in the Master database.

The comments about the steps are mentioned in the code itself. Hope this will help you

Let me know in case of any concern.

Thanks

Niladri Biswas

Re: UnQuote a nvarchar() string and use it as a table name

reza assar6-Jul-11 1:04

reza assar

6-Jul-11 1:04

hi thanx to ur answer
it took a time fore me to understand what just going on
i will try it and answer back
Smile | :)

GetDate

berba4-Jul-11 0:10

berba

4-Jul-11 0:10

Hi,

When I run the following select getdate(), I'm getting the value below:
2011-07-04 13:54:22.427

I want to get only the value 2011-07-04...

Can anybody advise?

Note: I don't want to convert it to varchar.

Cheers

Re: GetDate

thatraja4-Jul-11 0:25

thatraja

4-Jul-11 0:25

berba wrote:
Note: I don't want to convert it to varchar.

Try this Get Only DateTime Part without using Convert[^]

thatraja

My Tip/Tricks
My Dad had a Heart Attack on this day so don't...

Re: GetDate

Simon_Whale4-Jul-11 0:40

Simon_Whale

4-Jul-11 0:40

If you are using SQL Server 2008 then use the date datatype

select cast(getdate() as date)

returns 2011-07-04

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: GetDate

berba4-Jul-11 2:44

berba

4-Jul-11 2:44

I'm using SQL2000

Re: GetDate

Simon_Whale4-Jul-11 2:49

Simon_Whale

4-Jul-11 2:49

Without using the convert function to manipulate the date then your best answer is from thatraja

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: GetDate

Shameel4-Jul-11 4:44

Shameel

4-Jul-11 4:44

SELECT DATEADD(dd,0,DATEDIFF(dd,0,GETDATE())) does the trick.

A Quick SQL/Integrated Authentication issue (Looks like I am missing something trivial)

Vasudevan Deepak Kumar30-Jun-11 7:24

Vasudevan Deepak Kumar

30-Jun-11 7:24

A simple code as below:

<script language="C#" runat="server">
    void Page_Load(object sender, EventArgs e)
    {
        try
        {
            SqlConnection objConnection = new SqlConnection("Data Source=MySQLServer;Initial Catalog=gqs;Integrated Security=True;");
            objConnection.Open();
        }
        catch (SqlException objException)
        {
            Response.Write(objException.Message);
        }
        Response.Write("<br><Br>The page is executing as " +Thread.CurrentPrincipal.Identity.Name);
    }
</script>

gives the following output:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
The page is executing as MyDepartmentDomain\deepak

When the thread is running as me how does SSPI pickup Anonymous Logon?

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

The woods are lovely, dark and deep,
But I have promises to keep,
And miles to go before I sleep,
And miles to go before I sleep!

Re: A Quick SQL/Integrated Authentication issue (Looks like I am missing something trivial)

Pete O'Hanlon30-Jun-11 7:44

Pete O'Hanlon

30-Jun-11 7:44

The issue is because the thing that's actually connecting to the database is the IIS worker process, which is running with the anonymous login here.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Re: A Quick SQL/Integrated Authentication issue (Looks like I am missing something trivial)

Vasudevan Deepak Kumar30-Jun-11 7:53

Vasudevan Deepak Kumar

30-Jun-11 7:53

Dear Pete,

The Virtual Directory has 'Anonymous Authentication' disabled. It is a Windows Server 2008 R2. The virtual directory has only Windows Authentication enabled.

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

The woods are lovely, dark and deep,
But I have promises to keep,
And miles to go before I sleep,
And miles to go before I sleep!

Re: A Quick SQL/Integrated Authentication issue (Looks like I am missing something trivial)

J4amieC1-Jul-11 1:18

J4amieC

1-Jul-11 1:18

As Pete said, this is the identity of the AppDomain. Its got nothing to do with the authentication mode of the website or virtual directory.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.