Post reply

Select global var from another server

  • November 18, 2009 at 8:39 am

    #217758

    How do you select a global var from another server, ala a fully qualified name?

    SELECT server.master.dbo.@@version

    The following DOES work (but is totally lame):

    SELECT Top 1 @@MicrosoftVersion FROM server.master.dbo.any_table

    ---
    Dlongnecker

  • November 18, 2009 at 8:43 am

    #1080961

    Try with OPENQUERY:

    SELECT *

    FROM OPENQUERY(server,'SELECT @@version')

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • November 18, 2009 at 8:44 am

    #1080964

    dlongnecker-802303 (11/18/2009)

    SELECT Top 1 @@MicrosoftVersion FROM server.master.dbo.any_table

    This select @@MicrosoftVersion from the LOCAL server, not the remote one.

    -- Gianluca Sartori

  • November 18, 2009 at 8:53 am

    #1080970

    Good catch! And this works, thanks.

    ---
    Dlongnecker

  • November 18, 2009 at 9:12 am

    #1080981

    Hrm... I using this in a sproc to gather data from other servers, but it doesn't like using a variable as the server name:

    SELECT @version=version FROM OPENQUERY(@SERVER_NAME,'SELECT @@MicrosoftVersion / 0x01000000 As ''Version''')

    Think something akin to this is possible? Preferably without dynamic SQL or something because that's nasty.

    ---
    Dlongnecker

  • November 18, 2009 at 9:29 am

    #1080995

    dlongnecker-802303 (11/18/2009)

    Hrm... I using this in a sproc to gather data from other servers, but it doesn't like using a variable as the server name:

    SELECT @version=version FROM OPENQUERY(@SERVER_NAME,'SELECT @@MicrosoftVersion / 0x01000000 As ''Version''')

    Think something akin to this is possible? Preferably without dynamic SQL or something because that's nasty.

    Unfortunately the server name cannot be a variable. If you want it dynamic you will have to build the SQL string and run it with EXEC or sp_executesql. I'm sorry.

    -- Gianluca Sartori

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply