Xrhrft

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?

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.

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)

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