October 12, 2006 at 7:28 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 12, 2006 at 11:20 pm
I don't like any of these options. I don't think you should be using different linked server names in dev/test/prod. If you do, that means you are traversing different code paths in dev/test/prod, so you can't really guarantee what works in test will work in prod.
It would be best to use the same linked server names in each environment, just change which servers they point to in each. That way there's just one code path.
If you can't move away from the different names though, I would say option 2 is the better of the 3 above.
---------------------------------------
elsasoft.org
October 12, 2006 at 11:26 pm
Thanks for the info.
yes, I agree, and have been saying that for a while now. But it is out of my control...
I agree, maybe not DEV, but TEST should be a mirror of PROD... and this entire setup annoys me to tears.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply