Fully qualified table names with SP_ExecuteSql to access remote server

Question

Trying to update a table on a linked server (SQL 2000/2005) but my server name will not be known ahead of time. I'm trying this:

DECLARE @Sql NVARCHAR(4000)
DECLARE @ParamDef NVARCHAR(4000)
DECLARE @SERVER_NAME VARCHAR(35)

SET @Sql = 'UPDATE
@server_name_param.dba_sandbox.dbo.SomeTable
SET SomeCol=''data'''

SET @ParamDef = N'@server_name_param VARCHAR(35)'

print @Sql

exec sp_executesql @Sql, @ParamDef, @server_name_param=@SERVER_NAME

Which returns this:

UPDATE
@server_name_param.dba_sandbox.dbo.SomeTable
SET SomeCol='data'
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.

Any ideas? Is there anyway I view the SQL statement that is being executed after the parameters are bound?

Answer 1

You cannot do this with parameters directly - you would have to use dynamic SQL, or send the server name as a parameter to an SP that does dynamic SQL:

DECLARE @template NVARCHAR(4000) 
DECLARE @Sql NVARCHAR(4000) 
DECLARE @SERVER_NAME VARCHAR(35) 

SET @template = 'UPDATE {@server_name_param}.dba_sandbox.dbo.SomeTable SET SomeCol=''data''' 
SET @sql = REPLACE(@template, '{@server_name_param}', @SERVER_NAME)

print @Sql 

exec sp_executesql @Sql -- OR EXEC ( @sql )

Answer 2

You'll have to do this, it can't be parameterised

....
SET @Sql = 'UPDATE ' + @server_name_param + '.dba_sandbox.dbo.SomeTable SET SomeCol=''data'''
....

Edit: There is another way which I used back in my pure DBA days

EXEC sp_setnetname 'AdhocServer', @SERVER_NAME
UPDATE AdhocServer.dba_sandbox.dbo.SomeTable SET SomeCol 'data'
EXEC sp_setnetname 'AdhocServer', 'MeaninglessValue'

sp_setnetname is there from SQL Server 2000 to 2008

Edit2. Permissions:

Try EXECUTE AS LOGIN = 'login_name' , where login_name is a superuser

I've not really used this (I use "AS USER" for testing), so not sure of the finer points...

Edit 3: for concurrency, consider using sp_getapplock and a stored procedure, or some other concurrency control mechanism.

Answer 3

I like gbn's trick. I didn't know that one and I'm gonna have to research that some more.

Since I didn't know that trick, I've had to use dynamic sql in similar situations in the past (like what Cade posted). When that happens I would normally query an information schema view to make sure the parameter value is a real database object before building the query. That way I'm sure it's not an injection attempt.