Get @@SERVERNAME from linked server

Question

This seems like a basic question but I can't find any answers out there - I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things:

select <linked server>.@@SERVERNAME;
select <linked server>.SERVERPROPERTY('ServerName');

... but no joy. Any ideas?

This is SQL 2008 R2 & 2014 (2008R2 is the linked server)

EDIT: Errors are:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '@@SERVERNAME'.

Answer 1

You can use OPENQUERY as explained in MSDN to get the information of Linked server:

i.e using OPENQUERY ( linked_server ,'query' )

with something like below will give you server name using sys.dm_exec_connections dmv

SELECT * FROM OPENQUERY (
         linkedservername, 
         '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
        ')

Answer 2

Some slightly shorter (and more natural, IMHO) approaches:

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

Or:

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

I prefer the sp_executesql route because:

1. It will still allow for strongly-typed parameters, instead of dealing with all kinds of string concatenation, escaping of single quotes, etc.

2. It is easy to specify a particular database in that path, instead of having to put database prefixes on all the objects referenced in the query. And yes, you can define the database dynamically:

   DECLARE @db SYSNAME, @exec NVARCHAR(1024);
   SET @db = N'tempdb';
   SET @exec = N'LinkedServer.' + QUOTENAME(@db) + N'.sys.sp_executesql';
   EXEC @exec N'SELECT @@VERSION, DB_NAME();';
   

   And the linked server name, too, if need be:

   DECLARE @srv SYSNAME, @db SYSNAME, @exec NVARCHAR(1024);
   SET @srv = N'LinkedServer';
   SET @db = N'tempdb';
   SET @exec = QUOTENAME(@srv) + N'.' + QUOTENAME(@db) + N'.sys.sp_executesql';
   EXEC @exec N'SELECT @@VERSION, DB_NAME();';
   

Answer 3

There are easier ways to get at the server names of your linked servers. You could query sys.sysservers or run EXEC sp_linkedservers to give you all the info you need on your local linked servers including the server name. Here's the link on sp_linkedservers if you want it. While this doesn't necessarily answer the question of how to SELECT @@SERVERNAME remotely, it gets you that information.

Answer 4

Easiest Way (SSMS)

You can't script it through openquery if you don't have permissions on the master database (nor should you).

From the object explorer

+Linked Objects

++Linked Servers

+++[Linked Server Name]

Left click the linked server name Script Linked Server As > Create To > New Query Editor Window

Look for @datasrc='linked server source here'

Voila!