Syntax Error When Adding LINKED SERVER Dynamically

Transact-SQL
1

Hi experts, I'm overlooking something simple I think.

SET @SQL = N'exec ' + '[' + @LinkedServer + ']' + '.master.sys.sp_addlinkedserver @DBUTILLinkedServer, N'SQL Server'';

Get error Incorrect syntax near 'SQL'.

Any ideas? THanks

2

And of course, variables @LinkedServer and @DBUTILLinkedServer are populated with valid server names. I have tried the statement with N'SQL Server'; and N'SQL Server''; (two quotes) Still get the "Incorrect syntax near 'SQL' error

Any ideas, please? Thanks

3

Difficult to say since I don't have any way to test it. What value is in @LinkedServer?

And you'd have a variable in the statement, @DBUTILinkedServer, that would be undefined and have no value in the EXEC batch. You'd need to provide a value for that ... perhaps that's why the syntax error?

1 Like
4

ScottPletcher, I'm trying to post a reply but I'm getting an error on this site "Sorry, an error has occurred".

5

Rather than a "build-up-on-the-fly" approach, I think a "replace-in-template" approach is easier to create, to modify and to follow later. In this case, that would be something like this:

DECLARE @DBUTILLinkedServer varchar(8000)
DECLARE @LinkedServer varchar(8000)
DECLARE @SQL varchar(8000)

SELECT @LinkedServer = 'server1', @DBUTILLinkedServer = 'server2'

SET @SQL = N'EXEC [@LinkedServer].master.sys.sp_addlinkedserver @DBUTILLinkedServer, N''SQL Server''';

SET @SQL = REPLACE(REPLACE(@SQL, '@LinkedServer', @LinkedServer), '@DBUTILLinkedServer', '''' + @DBUTILLinkedServer + '''')

PRINT @SQL
--EXEC(@SQL)

1 Like
6

ScottPletcher - Maybe this will help someone else who needs to create the same Linked Server across all instances. I have a CMS node (Central ManagementServers) containing 97 prod SQL instances.
I ran this in the CMS node and it created the linked server as well as the associated Security login for the linked server. I'm sure the technique you described above would work as well but making use of the CMS trick seemed like a good idea. Thanks for all of your help.

EXEC sys.sp_addlinkedserver 'MyServer', N'SQL Server';
Command(s) completed successfully. (97 servers)

EXEC sp_addlinkedsrvlogin 'MyServer','false', NULL, 'MSDBAdmin', 'ThePassord'
Command(s) completed successfully. (97 servers)