Stored Procedure across Multiple SQL servers

[ksutphin]

I have many situations where I have to write stored procedures across multilple servers because our shipping systems is on a different server than our accounting server.
For Example
Select *
From accserver.db1.dbo.Table1 t1 join
shpserver.db2.dbo.Table2 t2 on t1.Order = t2.Order

This always works fine. The problem comes about if we have to restore to a back up server. The other day our main accounting server went down and we had to restore to the accounting databases to a back up server, with a different name. When this happens, we have to alter the stored procedures running on the shipping server to read as follows..
Select *
From accdkupserver.db1.dbo.Table1 t1 join
shpserver.db2.dbo.Table2 t2 on t1.Order = t2.Order

This becomes a hassle because we have a lot of stored procedures to change, and then change back when we get the main server back on line.

Is there a way to use a variable for the path ('accserver.db1.') that can possibly pull from a table?

If that's not possible is there a way to do what would basically be a 'replace' for the server name in all the stored procedures? I'm thinking this may be possible using sysobjects but I'm not sure.

ksutphin

 

[NoCoolHandle]

Ok.. There is a problem in what you want...

To be able to change the servername in a query requries you submit an adhoc (dynamic) sql statement to the server..

This practice can cause security issues that might effect the execution..

One option that comes to mind (and this is a workaround)
is you could potentially have a table in a database that contains a row that indicates if you need to use the backup server and then have an if statement that querys the backup server if it is decided that it is necessary.

Another option might be to see if you can query the main server and if it retruns an empty set then the if sends you to the backup server.

Bottom line the best way to do this is to have "both" querys available and to use an if something exists begin / end around the choice you need. It might pay to have these qerys in their own stored procs and then call this vrs the other, rather than the alternative that requires you have both sets of logic in each stored proc..

Avoid Dynamic Sql!!!

HTH


Rob

 

[JamesLean]

There is actually an easy solution to this problem.

When you set up your linked server(s), rather than use the default setup, where the linked server name is the actual SQL Server name, you can give the linked server some generic alias. Then all you need to do is change the SQL Server name in the linked server properties and any queries that reference that linked server will now be pointing to the correct SQL server.

If you use Ent Mgr to set up your linked server, enter the alias in the "Linked Server" box but choose "Other Data Source" (Microsoft OLE DB Provider for SQL Server) rather than "SQL Server". You then just need to enter the actual SQL Server name in the "Data source" box (this is the bit you need to change if you have to swap servers).

--James