带有SP_ExecuteSql的标准表名可以访问远程服务器 [英] Fully qualified table names with SP_ExecuteSql to access remote server

问题描述

尝试更新链接服务器上的表(SQL 2000/2005)，但我的服务器名称将无法提前知道.我正在尝试:

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

哪个返回此:

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

有什么想法吗?无论如何，我是否在绑定参数后查看正在执行的SQL语句?

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

推荐答案

您将必须执行此操作，无法对其进行参数化

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

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

在纯粹的DBA时代，我还使用了另一种方法

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'

从SQL Server 2000到2008的

sp_setnetname

sp_setnetname is there from SQL Server 2000 to 2008

编辑2. 权限:

尝试 EXECUTE AS LOGIN = 'login_name' ，其中login_name是超级用户

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

我还没有真正使用过它(我使用"AS USER"进行测试)，所以不确定要点...

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

对于并发，请考虑使用sp_getapplock和存储过程或其他一些并发控制机制.

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

这篇关于带有SP_ExecuteSql的标准表名可以访问远程服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！