April 1, 2009 at 3:15 am
Hi,
We have a number of SQL db environments for our CRM system, e.g. LIVE, TEST, DEVELOP, etc. and occasionally we want to take an up to date copy of the LIVE environment and restore it over the other environments. When we do this we have to manually change some stored procedures that use a Linked Server to point to the corresponding ERP database environment for integration purposes.
For instance, in the LIVE SQL db there may be a stored procedure that gets data from the ERP database using syntax like this:
SELECT ... FROM OPENQUERY(ERPLIVE, 'SELECT ... FROM .... WHERE ...')
where ERPLIVE is the Linked Server name for the connection to the LIVE ERP database.
In the TEST SQL db this syntax would normally read:
SELECT ... FROM OPENQUERY(ERPTEST, 'SELECT ... FROM .... WHERE ...')
where ERPTEST is the Linked Server name for the connection to the TEST ERP database.
When we restore of a copy of the LIVE SQL db over the TEST SQL db then the stored procedures in TEST will now need updating to change the Linked Server name from ERPLIVE to ERPTEST.
What I would like to know is if there is an easier way to do this, for instance using a variable for the Linked Server name in the stored procedure that gets the correct Linked Server name for that database. I was thinking maybe having a table with 2 columns, 1 for the db name and 1 for the linked server name so that the variable reads the Linked Server name for that db from this table.
I'm not sure if it's possible, how to go about doing it, or if there is a better way altogether.
Thanks
April 1, 2009 at 3:44 am
I haven't used OPENQUERY a lot in my time but this should work. Use a generic name for your linked server, e.g. ERPSERVER and then, after creating it in all of your environments use sp_setnetname to set the network name of the the target. So in live you would run sp_setnetname 'ERPSERVER','ERPLIVE', in test you would run sp_setnetname 'ERPSERVER','ERPTEST' etc.
Using the generic name means your code is applicable in any of your environments so you don't have to re-create anything after a database restore.
Running sp_setnetname alters the column srvnetname in master.dbo.sysservers.
Hope that helps,
Mike
April 1, 2009 at 4:14 am
Thanks for the quick response. I took a look at this sysservers table and I don't think this solution will help because all four database environments are on the same SQL server.
So the setup I have is 1 SQL Server with all my SQL database environments on, with a Linked Server set up on this SQL server called ERPLIVE with the connection details to the Oracle server/db for the LIVE ERP system, and a similar Linked Server on this SQL server called ERPTEST with the connection details to the Oracle server/db for the TEST ERP system.
Hence, I can't use the srvnetname as the variable because I have the LIVE and TEST (and other) integration routines running on the same server.
April 1, 2009 at 4:28 am
Blast! Foiled again. In that case your idea of a table with the relevant columns and data is a perfectly reasonable thing to do. You can then build up a dynamic SQL string and EXEC it. Should be okay.
Mike
April 1, 2009 at 4:31 am
If you are using SQL Server 2005/2008 you should have a look for "CREATE SYNONYM".
Greets
Flo
April 1, 2009 at 5:33 am
Hmmm. I've looked at the documentation on that. It could be a possibility but I'll have to think about it
December 6, 2012 at 9:28 am
I found this post where Microsoft gives the solution... execute the string variable within a string variable. Double your single quotes, double your fun!
September 12, 2013 at 6:14 am
That works OK when you have one single query you wish to run against one linked server.
However the situation I have is that I have two environments each with several linked servers.
Depending on which environment calls the sproc, I need each query to use the relevant linked server.
Additional issue is that I need to JOIN data from all of them.
Eg - (and this is a dumb example, but it illustrates what I'm trying to do)
environment1:- [SQLSRv1].[DBOrders_EMEA] and [SQLSRV2].[DBCustomers_EMEA] and [localhost].[DBHome]
environment2:- [SQLSRV3].[DBOrders_USA] and [SQLSRV4].[DBCustomers_USA] and [localhost].[DBHome]
with the query being run from [localhost].[dbhome].
So based on what params are passed into the sproc, determine which environment to query against.
Ideally (although I know this isn't possible), something like this is what I was aiming for.
DECLARE @OrdersDB VARCHAR(100)
DECLARE @CustomersDB VARCHAR (100)
IF BLAH BLAH /* and equates to it being in EMEA*/
BGEIN
SELECT @ordersDB = '[SQLSRv1].[DBOrders_EMEA]'
SELECT @customersDB = ' [SQLSRV2].[DBCustomers_EMEA] '
END
SELECT TOP 1 foo.ID, bar.blah, O.lastorder, C.age
FROM Foo
INNER JOIN bar on bar.ID = foo.barID
LEFT OUTER JOIN @OrdersDB.dbo.OrderTable O ON foo.orderID = O.OrderID
INNER JOIN @CustomersDB.dbo.CustomersTable C ON Bar.CustomerID = C.CustomerID
ORDER By O.DateProcessed DESC
The joins are a lot more complex and lengthy than that and the data sets are of the order of a couple of millions rows.
I cant seem to ram this into the querytype specified in the http://support.microsoft.com/kb/314520/en-us KB, mainly I guess as I'm joining on > 1 server. Any ideas? Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply