SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a variable for a linked server name in stored procedures


Using a variable for a linked server name in stored procedures

Author
Message
Dizzy-486998
Dizzy-486998
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 91
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
Mike_D
Mike_D
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 615
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
Dizzy-486998
Dizzy-486998
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 91
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.
Mike_D
Mike_D
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1174 Visits: 615
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
Florian Reischl
Florian Reischl
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8415 Visits: 3934
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
Dizzy-486998
Dizzy-486998
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 91
Hmmm. I've looked at the documentation on that. It could be a possibility but I'll have to think about it
rdrag
rdrag
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 66
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
Dave Blount
Dave Blount
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 78
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search