tip on linked server references when migrating code

Question

tip on linked server references when migrating code

Johnny B

SSCrazy

Points: 2965
More actions
May 3, 2006 at 6:26 pm

#366096

I know its easy to just do a global search and replace in a good editor, but is there any way to store a global variable such as the linked server machine name and use a variable and pass it to the stored procedures to be used in the linked queries?
anyone have an idea on this. i have not tried it but have been told that parameters cant be used in from clauses within the code blocks. thanks in advance for any help
SQL 2012 Standard VPS Windows 2012 Server Standard

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

If you don't use dynamic query, I don't think you can you use parameters in the FROM clause in any part of the four-part name, like server1.myDB.dbo.MyTable.

Linked server name is a logical name and should be netural to physical server name or location. Instead of changing server name in each query, you can change the linked server definition to use current name in the query, or you may need to define a new linked server with this name if you have other queries using the linked server with a different name.

E.g. you have a linked server ServerA, and you have SP1, PS2 using ServerA for distributed query;

Now you migrate code from other apps, which query the same physical linked server by the name ServerB in SP3 and SP4.

In this case you can not change linked server name to ServerB because SP1 and SP2 are using them. Just add a new server ServerB, which connects to the same physical server.

Johnny B SSCrazy Points: 2965 More actions · Answer 2

so the linked server names are independant from the machine names? all these servers are on the same network. so the following will have to be true in order to make your suggestion work (assuming i understand you correctly)

SQLServer name 'abc' -------->link name 'jkl' --------->slqServer name 'xyz'

SQLServer name 'def' -------->link name 'jkl' --------->sqlServer name 'uvw'

My understanding is the lined server name is the 'instance' name of the server. Is that incorrect?

Also... if there are identical link names targeting two different servers, how is that managed in one enterprise manager consule?

SQL 2012 Standard VPS Windows 2012 Server Standard

peterhe SSChampion Points: 11469 More actions · Answer 3

It's upto what provider you use. If you use SQLOLEDB, you can give any legal SQL identifier as the server name. E.g.:

exec sp_addlinkedserver 'MyLinkedServer1', '', N'SQLOLEDB', 'SQL Host/Instance name', '','','Your DB'

exec sp_addlinkedsrvlogin @rmtsrvname = 'MyLinkedServer1', @useself = 'false', @locallogin = NULL,@rmtuser ='sa', @rmtpassword = NULL

Then, in your query, you can use 'MyLinkedServer1' as the server name, e.g. SELECT * FROM MyLinkedServer1.MyDB.dbo.Table1

If the physical server of the linked server is changed, you just need to redefine the linked server 'MyLinkedServer1' . You do not need to change your queries.

The linked server name must be unique.