none
Getting remote SERVERPROPERTY through linked server RRS feed

  • Question

  • Is it possible to get the below query to work by using a variable instead of the linked server name hardcoded on the query?

    I need to run this query against more than one linked server and the linked server name needs to be dynamic, I'll pass the value to the query at runtime.

    I need to store the value in a temp table momentarily to perform a validation.

    Any suggestions?

    This works:

    --Get node for the remote SQL Instance

    CREATE

    TABLE #t2 (ServerName VARCHAR(30

    ))

    INSERT

    INTO #t2 (ServerName

    )

    SELECT

    RemoteNode FROM OPENQUERY([FTLQSQLCL02\INSTA], 'SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS RemoteNode')

     

    This doesn't work:

    --Get node for the remote SQL Instance

    CREATE

     

    TABLE #t2 (ServerName VARCHAR(30

    ))

    INSERT

     

    INTO #t2 (ServerName

    )

    SELECT

     

    RemoteNode FROM OPENQUERY(@LinkedServerName, 'SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS RemoteNode'

    )

     
    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Wednesday, November 16, 2011 5:22 AM

Answers

  • Try the following dynamic SQL script:

    DECLARE @SERVERNAME sysname = 'HPESTAR'
    DECLARE @sqlQuery nvarchar(max) =
          'SELECT * 
           FROM   OPENQUERY(' + QUOTENAME(CONVERT(sysname, @SERVERNAME))+ ',
                    ''SELECT SERVERPROPERTY(''''ComputerNamePhysicalNetBIOS'''')'')' 
    
    PRINT @sqlQuery 
    EXEC sp_executeSQL @sqlQuery
    


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Wednesday, November 16, 2011 6:38 PM
    Moderator
  • EXEC ('SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')') AT [MyLinkedServer]
    

     

     


     

    Meant to add this as well:

     

     

    SELECT * FROM sys.servers
    WHERE [is_linked] = 1

     

     

    DECLARE @q NVARCHAR(MAX)
    DECLARE c CURSOR LOCAL FAST_FORWARD
    FOR 
    --SELECT 'SELECT RemoteNode FROM OPENQUERY('+[name]+', ''SELECT SERVERPROPERTY(''''ComputerNamePhysicalNetBIOS'''') AS RemoteNode'')' FROM sys.servers WHERE [is_linked] = 1
    SELECT 'EXEC (''SELECT SERVERPROPERTY(''''ComputerNamePhysicalNetBIOS'''')'') AT ' + [name]  FROM sys.servers WHERE [is_linked] = 1
    		
    OPEN c;
    FETCH NEXT FROM c INTO @q;			
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	PRINT @q
    	EXEC sp_executesql @q
    	FETCH NEXT FROM c INTO @q;
    END			
    CLOSE c;
    DEALLOCATE c;

     


    Wednesday, November 16, 2011 8:54 AM

All replies

  • Wednesday, November 16, 2011 5:28 AM
    Moderator
  • EXEC ('SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')') AT [MyLinkedServer]
    

     

     


     

    Meant to add this as well:

     

     

    SELECT * FROM sys.servers
    WHERE [is_linked] = 1

     

     

    DECLARE @q NVARCHAR(MAX)
    DECLARE c CURSOR LOCAL FAST_FORWARD
    FOR 
    --SELECT 'SELECT RemoteNode FROM OPENQUERY('+[name]+', ''SELECT SERVERPROPERTY(''''ComputerNamePhysicalNetBIOS'''') AS RemoteNode'')' FROM sys.servers WHERE [is_linked] = 1
    SELECT 'EXEC (''SELECT SERVERPROPERTY(''''ComputerNamePhysicalNetBIOS'''')'') AT ' + [name]  FROM sys.servers WHERE [is_linked] = 1
    		
    OPEN c;
    FETCH NEXT FROM c INTO @q;			
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	PRINT @q
    	EXEC sp_executesql @q
    	FETCH NEXT FROM c INTO @q;
    END			
    CLOSE c;
    DEALLOCATE c;

     


    Wednesday, November 16, 2011 8:54 AM
  • SQLUSA,

    That's a really colorful web site :) Thanks for the help.

     

    Jon,

    Can I get the results of the below into a variable or a temp table?

    EXEC ('SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')') AT [MyLinkedServer]
    

    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Wednesday, November 16, 2011 3:39 PM
  • SQLUSA,

    That's a really colorful web site :) Thanks for the help.

     

    Jon,

    Can I get the results of the below into a variable or a temp table?

    EXEC ('SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')') AT [MyLinkedServer]
    

    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com


    use the syntax insert into ... exec, as shown below.  Note: MSDTC will likely need to be working properly for this to work.

    DECLARE @t TABLE(SrvName VARCHAR(255));
    
    INSERT INTO @t 
    EXEC ('SELECT SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')') AT [MyLinkedServerName]
    

     


    http://jahaines.blogspot.com/
    Wednesday, November 16, 2011 3:53 PM
    Moderator
  • I got MSDTC to work, your INSERT works.

    One last question.

    Would be possible to set my LinkedServerName to be a variable?

    DECLARE @s AS VARCHAR(128

    )

    SET

    @s =

    '[BRARJ1\INSTA]'

    CREATE

     

    TABLE t (servername VARCHAR(128

    ))

    INSERT

    INTO t

    EXEC

    ('SELECT CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS VARCHAR(128)) AS RemoteServer') AT @s


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Wednesday, November 16, 2011 4:25 PM
  • Try the following dynamic SQL script:

    DECLARE @SERVERNAME sysname = 'HPESTAR'
    DECLARE @sqlQuery nvarchar(max) =
          'SELECT * 
           FROM   OPENQUERY(' + QUOTENAME(CONVERT(sysname, @SERVERNAME))+ ',
                    ''SELECT SERVERPROPERTY(''''ComputerNamePhysicalNetBIOS'''')'')' 
    
    PRINT @sqlQuery 
    EXEC sp_executeSQL @sqlQuery
    


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Wednesday, November 16, 2011 6:38 PM
    Moderator