Sql-server – Setting a variable from a statement that starts with EXEC

linked-serversql servert-sql

I am trying to get the Server Name from a LinkedServer and assign it to a variable.

From this question (Get @@SERVERNAME from linked server), I found that either of these return the Server Name.

EXEC LinkedServer.[master].sys.sp_executesql N'SELECT @@VERSION;';

or

EXEC('SELECT @@VERSION;') AT LinkedServer;

Yet, when I try to assign that value to a parameter, SQL Server states it's incorrect:

SET @DbServerName = (EXEC TMR_DM_LS.[master].sys.sp_executesql N'SELECT @@SERVERNAME')

Msg 156, Level 15, State 1, Line XXXX Incorrect syntax near the keyword 'EXEC'.
Msg 102, Level 15, State 1, Line XXXX Incorrect syntax near ')'

What am I doing wrong?

Best Answer

Try something like the following:

DECLARE @DbServerName sysname;

EXEC [TMR_DM_LS].[master].sys.sp_executesql
   N'SELECT @DbServerName_tmp = @@SERVERNAME;',
   N'@DbServerName_tmp sysname OUTPUT',
   @DbServerName_tmp = @DbServerName OUTPUT;

SELECT @DbServerName AS [RemoteName];

HOWEVER, the above is how to return info in general. IF you are truly only wanting the server name, then I think you already have it on your local server. The Linked Server definition, depending on how it was configured, might very well have the name you are looking for:

DECLARE @DbServerName sysname;

SELECT @DbServerName = [data_source]
FROM   sys.servers
WHERE  [name] = N'TMR_DM_LS';

SELECT @DbServerName AS [RemoteName];

See if that works before doing the actual remote execution since it would be much more efficient to get the value locally.

UPDATE
O.P. replied that the desired value was indeed in the sys.servers system catalog view.

Related Question