Fully Qualified Table Names With Sp_executesql To Access Remote Server

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 NVARC

Solution 1:

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.

Solution 2:

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 @Sqlexec sp_executesql @Sql-- OR EXEC ( @sql )

Solution 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.