How would one create a datasource to use in TSQL?

Question

How would one create a datasource to use in TSQL?

ShawnTherrien

Default port

Points: 1481
More actions
December 1, 2009 at 1:48 pm

#217300

I have dozens of stored procedures pointing to another server and at times they need to point to another server. Reporting, ETLs, etc.
All my procedures are straight up TSQL. How would I best encapsulate data sources so I could change one value and be pointing at another server?
After the fact (months of development based on another dbas work), I realize that if it was done in dynamic TSQL, we could store the servername in the database and change it whenever we need.
I'm using SSIS for some ETLs and love the datasource in that application, but nothing obvious stands out in SQL Server. I'm not sure if I just got painted into a corner through bad practices or if I just haven't discovered the proper door yet.
My ad hoc solution at this point would be to create a linked server and have all the TSQL use this reference and change where it points where I need. Are there drawbacks with doing this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Logicalman1998 SSC Journeyman Points: 75 More actions · Answer 1

One method to be able to vary any item in a select statement is to use a string method to create the statement and then execute the string .. e.g. .. DECLARE @sSQL as Varchar(8000): SET @sSQL = "SELECT ... ":

By using a parameter for the Stored Procedure, you can then use an IF statement to select what source to bolt onto the string. e.g. ...

If @SourceID = 1 .... SET @sSQL = "FROM myserver1.db1.dbo.tbl1"

If @SourceID = 2 .... SET @sSQL = "FROM myserver2.db2.dbo.tbl2"

Then use the EXEC (@sSQL) to run the statement.

Always more than one method though....

Garadin One Orange Chip Points: 29613 More actions · Answer 2

This may help you:

http://msdn.microsoft.com/en-us/library/ms177544.aspx

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ShawnTherrien Default port Points: 1481 More actions · Answer 3

No, dynamic SQL definitely is a good solution. given this case a year in the past, thats the route I would have gone... or likely through SSIS for the case of the import, not the reports.

At this point debugging a 9 hour ETL (in one case) is a good argument for not rewriting everything. Could take weeks to debug if I introduce errors.

-- Thank you for that information on the SYNONYM. Great solution for the reporting where you can pass in an indication of which server, use dynamic SQL to create the SYNONYM, then normal TSQL to report off of it.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

That is exactly what I was going to suggest.. Synonyms are a great way to "hide" exactly what is on the other end and this is a perfect example of why they work so well.

I have one caution, if the other end is SQL 2000 you may have intermittent issues.

Please see:

MS Connect Article on the problem related to SQL 2000 endpoint

CEWII

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

Agreed... SYNONYMs are great. If you can't use them for some reason, a "pass through" view will do the same wonderful thing except instead of ALTER SYNONYM, you would use ALTER VIEW instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

I moved from pass-thru views to SYNONYMs on a recent project. Love it..

CEWII