问题描述
尝试更新链接服务器上的表(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 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的标准表名可以访问远程服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！