Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a variable for a linked server name in stored procedures Expand / Collapse
Author
Message
Posted Wednesday, April 01, 2009 3:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 10:14 AM
Points: 15, Visits: 88
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
Post #687714
Posted Wednesday, April 01, 2009 3:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
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
Post #687732
Posted Wednesday, April 01, 2009 4:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 10:14 AM
Points: 15, Visits: 88
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.
Post #687742
Posted Wednesday, April 01, 2009 4:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
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
Post #687747
Posted Wednesday, April 01, 2009 4:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
If you are using SQL Server 2005/2008 you should have a look for "CREATE SYNONYM".

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #687751
Posted Wednesday, April 01, 2009 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 10:14 AM
Points: 15, Visits: 88
Hmmm. I've looked at the documentation on that. It could be a possibility but I'll have to think about it
Post #687786
Posted Thursday, December 06, 2012 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 03, 2013 7:46 PM
Points: 9, Visits: 63
I found this post where Microsoft gives the solution... execute the string variable within a string variable. Double your single quotes, double your fun!

http://support.microsoft.com/kb/314520

Post #1393606
Posted Thursday, September 12, 2013 6:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 4:38 AM
Points: 4, Visits: 68
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.
Post #1494122
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse