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 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 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 @Sql 

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

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.

友情链接