Using a variable for a linked server name in stored procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Using a variable for a linked server name in...

Using a variable for a linked server name in stored procedures

Rate Topic

Display Mode

Topic Options

Author

Message

Dizzy-486998

Posted Wednesday, April 01, 2009 3:15 AM

Grasshopper

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

Mike_D

Posted Wednesday, April 01, 2009 3:44 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:27 AM
Points: 469, Visits: 596

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

Dizzy-486998

Posted Wednesday, April 01, 2009 4:14 AM

Grasshopper

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

Mike_D

Posted Wednesday, April 01, 2009 4:28 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:27 AM
Points: 469, Visits: 596

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

Florian Reischl

Posted Wednesday, April 01, 2009 4:31 AM

SSCrazy

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

Dizzy-486998

Posted Wednesday, April 01, 2009 5:33 AM

Grasshopper

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

rdrag

Posted Thursday, December 06, 2012 9:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 9, Visits: 62

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

« Prev Topic | Next Topic »

Permissions