使用SP_ExecuteSql完全限定的表名访问远程服务器。

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:

试图更新链接服务器上的表(SQL 2000/2005)，但是我的服务器名将不会被提前知道。我在这个:

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?

什么好主意吗?我是否看到了在参数被绑定后正在执行的SQL语句?

3 个解决方案

#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

编辑:还有一种方法是我在纯DBA时代使用的

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

sp_setnetname从SQL Server 2000到2008

Edit2. Permissions:

Edit2。权限:

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

尝试执行为LOGIN = 'login_name'，其中login_name是超级用户

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.

编辑3:对于并发性，可以考虑使用sp_getapplock和一个存储过程，或者一些其他并发控制机制。

#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:

您不能直接使用参数实现这一点——您必须使用动态SQL，或者将服务器名作为参数发送给执行动态SQL的SP:

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 )

#3

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

我喜欢gbn的技巧。我不知道这个，我还要再研究一下。

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.

因为我不知道这个技巧，所以过去我不得不在类似的情况下使用动态sql(就像Cade发布的那样)。当发生这种情况时，我通常会在构建查询之前查询一个information schema视图，以确保参数值是一个真实的数据库对象。那样的话，我肯定这不是注射。

#1