Setting a variable from a statement that starts with EXEC

Question

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?

Answer 1

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.

Answer 2

You can't do it like that because you are returning a result set (via SELECT) and not a scalar value. If you wanted to put the result into a variable you could do something like this:

DECLARE @version SYSNAME;
DECLARE @params NVARCHAR(MAX);
    SET @params = '@server_name SYSNAME OUTPUT';
EXEC LinkedServer.[master].sys.sp_executesql
     N'SELECT @version = @@VERSION;',
     @params,
     @version = @version OUTPUT;
SELECT @version;

Using the OUTPUT parameter (sp_executesql)

Answer 3

A simple script showing how to return the server name using openquery

the only difference is that here I put the returning values into variables

--first query
--to find out the available linked servers
select * 
from sys.servers
where is_data_access_enabled = 1
  and is_linked = 1

--ckecking it out
SELECT * FROM OPENQUERY (
         [CTDB12], -- the name here must be one returned from the first query
         'SELECT
             @@SERVERNAME AS TargetServerName,
             SUSER_SNAME() AS ConnectedWith,
             DB_NAME() AS DefaultDB,
             client_net_address AS IPAddress
          FROM
              sys.dm_exec_connections
          WHERE
              session_id = @@SPID
        ')

-- you need to have proper permissions
--OLE DB provider "SQLNCLI11" for linked server "CTDB12" returned message "Deferred prepare could not be completed.".
--Msg 297, Level 16, State 1, Line 8
--The user does not have permission to perform this action.


--lets try another server
SELECT * FROM OPENQUERY (
         [QG-V-SQLRPL1-PR], -- the name here must be one returned from the first query
         'SELECT
             @@SERVERNAME AS TargetServerName,
             SUSER_SNAME() AS ConnectedWith,
             DB_NAME() AS DefaultDB,
             client_net_address AS IPAddress
          FROM
              sys.dm_exec_connections
          WHERE
              session_id = @@SPID
        ')

--now lets declare variables to keep the info and show them
declare @DbServerName sysname
declare @theuser sysname
declare @IPAddress sysname

SELECT @DbServerName = TargetServerName,
       @theuser = ConnectedWith,
       @IPAddress = IPAddress
FROM OPENQUERY (
         [QG-V-SQLRPL1-PR],  -- the name here must be one returned from the first query
         'SELECT
             @@SERVERNAME AS TargetServerName,
             SUSER_SNAME() AS ConnectedWith,
             DB_NAME() AS DefaultDB,
             client_net_address AS IPAddress
          FROM
              sys.dm_exec_connections
          WHERE
              session_id = @@SPID
        ')


print @DbServerName 
print @theuser 
print @IPAddress