7

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'.

8

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
        ')
10

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();';
    
1

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.

  • This is exactly what I've done. SELECT name FROM master.sys.servers where is_linked=1 I then loop through and dynamically get everything I need. – dwjv Sep 16 '15 at 15:17
  • 2
    Well, let's not forget that the linked server could be defined by IP address, or a client network utility alias, or something that's masked by hosts file / DNS, etc. The way SQL Server routes to it is not necessarily the hostname. – Aaron Bertrand Sep 16 '15 at 16:47
-1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.