如何写服务器名为变量的远程建立语句?

[disneyzhang]

declare @servername

set @servername='test'

Exec sp_addlinkedserver @srvproduct=N'',@server='srv_link',@provider='MSDASQL',
@provstr='driver={SQL SERVER};server=@servername;id=sa;pwd=12345'

现在的问题是如何将@servername名赋值给server参数呢?

[disneyzhang]

看过邹老师写的书,知道应该采用动态字符串形式,但是还是执行不了!

[leo_lesley]

exec ('Exec sp_addlinkedserver ,@server=''srv_link'',@provider=''MSDASQL'',@provstr=''driver={SQL SERVER};server='+@servername+';id=sa;pwd=12345''')



试试这个

[xiequanqin]

UP

[jetxia]

declare @servername varchar(50)

set @servername='test'
declare @tmp varchar(200)
set @tmp='driver={SQL SERVER};server='+@servername+';id=sa;pwd=12345'
Exec sp_addlinkedserver @srvproduct=N'',@server='srv_link',@provider='MSDASQL',
@provstr=@tmp

[disneyzhang]

不行的,提示错误
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

[disneyzhang]

测试过jetxia 写的语句, 出现下面的错误提示

Server: Msg 7399, Level 16, State 1, Line 67
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute]

[xiequanqin]

declare @servername varchar(60)

set @servername='test'
declare @STR varchar(255)
select @STR='driver={SQL SERVER};server='+@servername+';id=sa;pwd=12345'

Exec sp_addlinkedserver @srvproduct=N'',@server='srv_link',@provider='MSDASQL',
@provstr=@STR

[disneyzhang]

还是不行,是不是不服务器的名称不能作变量呢?

[disneyzhang]

UP!