October 12, 2006 at 7:23 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 15, 2006 at 5:55 pm
I believe that using sp_addserver is the best choice.
October 16, 2006 at 6:13 pm
How about this:
If @@servername = 'DEV-Environment'
exec link_server_1....
if @@servername = 'Test-Environment'
exec link_server_2...
if @@servername = 'Prod-Environment'
exec link_server_3...
It is "simple and stupid" conforming to KISS (keep it simple and stupid) principle.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply