Accessing Linked Server via Variable

[JohnEfford]

I have two linked servers that I want to access. Currently the way I am doing this to build the command as shown...

If @Department = 'P' 
    Set @Server = N'Pervasive_1'
Else        
    Set @Sever= N'Pervasive_2'

Set @SqlCommand = N'Select * OpenQuery(' + @Server +  N',''Select * from Products'')' ....

then

Exec sp_executesql @SqlCommand

this is ok for selections but really long winded for updates and insertions as the products table has lots of feilds.

The following would be a far easier method

If @Department = 'P' 
    Set @Server = N'Pervasive_1'
Else        
    Set @Sever= N'Pervasive_2'

Select * openquery(@Server,'Select * from products') .....

This gives the error message

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@Server'.

I have tried declaring the @Server variable as Varchar and NVarchar. Does any one know how to achieve this.

 

[psprague]

A couple of thoughts:

The first is to isolate the calls to the servers into different stored procs so the indiviaual names could be hardcoded:

If @Department = 'P'
exec do_stuff_with_server_a
else
exec do_stuff_with_server_b

the other thought is have a single linked server alias and switch which one it actually points at.

If @Department = 'P'
exec SetMyLinkedServer 'Server_A'
else
exec SetMyLinkedServer 'Server_B'

 

[JohnEfford]

I did not choose the first option because it seemed I was replicating code. But having coded a very tortous update statment by building the command as text string I have now decided that this is the way to go as although I am replicating code, the code is easer to read and therfore maintain. If anyone know how to use a variable to select the linked server I would still be interested to know as this option would be the cleanest.

 

[philhege]

Dynamic SQL simply does not allow the passing of variables in constructed statements (which is what OPENQUERY is). They're not available to the level that is actually running the statement.

Read BOL and TechNet for more info.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....