October 12, 2006 at 7:24 pm
Hi all,
I'm writing an application which calls a few stored procedures on SQL Server 2000. These stored procedures pull data from multiple different database servers, via Link Server from the main database.
Now, the for each environment (dev, test, prod), the link servers are named differently. So if I run my app in one environment and then run it in a different one, I'll have to ensure the appropriate script (with the correct environment's link server names) is used.
What is the best way to handle this when executing my script from the application?
The following are the options I came up with, but aren't sure if any of them are the best:
1. Avoid using link servers. Store each database connection details in a config file, and open multiple DB connections, etc. This option will be very inefficient because of the multiple DB connections.
2. Pass into the stored proc, an environmentType parameter and have the stored proc do a few IF's (or a CASE statement) and decide which link server to use.
3. Pass into the stored proc, a link server name (depending on the environment the system is running on) and dynamically use this link server name to execute the SQL.
Any help would be appreciated.
Cheers,
Paul
October 14, 2006 at 8:11 pm
OK, I'm not in a place where I can verify this, but I'm pretty sure the link server name is an alias on your server. That is, that you are not prevented from creating a link server name called "prod" that points to \\dev101 on your test box and an alias called "prod" pointing to \\prod101 on your production box.
(Somebody correct me if I'm wrong, but I'm pretty sure I've done this in the past.)
Anyway then your T-SQL source need not be modified between production/test/dev environments.
October 15, 2006 at 9:43 am
If you would like to use stored procedures in your central control box, I agree with John that you need to link servers. If you would like to use stored procedures in remote servers, you may try to use DTC.
October 15, 2006 at 5:33 pm
Thanks everyone for their feedback. I've managed to convince the DBA's to make our TEST and PROD environments identical (which is how they should have been in the first place)... and so they have each have a link server with the same name (alias), but point to different phycial servers.
Cheers,
Paul
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply