Post reply

Retrieving SERVERPROPERTY(''ProductVersion'') etc. from linked server (Dynamically!)

  • October 13, 2008 at 2:34 am

    #103601

    Hi fellow SQLServerCentral users

    I am currently developing a solution, where I am collecting different kinds of information from our SQL server park using a homebrewed "master" server. To establish the connection I am using linked servers, created on the master server.

    Retrieving for example SERVERPROPERTY(''ProductVersion'') from a linked server is no problem; I merely use:

    SELECT * FROM OPENQUERY(linked_servername, 'SELECT SERVERPROPERTY(''ProductVersion'') AS "Product Version"')

    What presents the problem is creating a dynamic version of this!

    For retrieving job info (if any jobs has failed) I am using something like this to make the servername dynamic:

    DECLARE @statement1 nvarchar(500)

    SET @statement1 = 'EXEC [' + @servername + '].DBNAME.dbo.usp_jobreport'

    EXECUTE sp_executesql @statement1

    I have tried creating a "SERVERPROPERTY" version of this, but it just won't work :unsure:

    Here is what I've come up with (Doesn't work!):

    SET @statement1 = 'SELECT * FROM OPENQUERY([' + @servername + '], 'SELECT SERVERPROPERTY(''ProductVersion'') AS "Product Version"')'

    A solution to the problem could be creating stored procedures on the remote servers, which could collect the desired information and insert it into a table, from where I could retrieve it from the master server. I would like to avoid this though.

    Does anyone have some input on this? Perhaps a rewrite of the above statement or a different approach?

    I would be very happy to get this problem solved 🙂

    Cheers, Lars Mikkelsen.

  • October 13, 2008 at 3:19 am

    #884026

    Try the following way:

    declare@sqlnvarchar (1000)

    set@sql= N'SELECT * FROM openquery (linked_server, ' + '''SELECT SERVERPROPERTY (''''ProductVersion'''') AS [Product Version]'')'

    print@sql

    execsp_executesql @sql

    go

    🙂

  • October 13, 2008 at 5:19 am

    #884072

    Thanks alot for the help - it works great 🙂

    I have a need to make an insert based of the value returned, AND make the servername (linked server) a dynamic value. Therefore I have rewritten the query like this:

    DECLARE @statement1 nvarchar(500)

    SET @statement1 = N'UPDATE info_version SET productedition = Convert(sysname, (SELECT * FROM openquery ([' + @servername + '], ' + '''SELECT SERVERPROPERTY (''''Edition'''') AS [Product Version]''))) WHERE server = [' + @servername + ']'

    EXEC sp_executesql @statement1

    But this returns:

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'myserver'.

    Without the WHERE clause it works:

    DECLARE @statement1 nvarchar(500)

    SET @statement1 = N'UPDATE info_version SET productedition = Convert(sysname, (SELECT * FROM openquery ([' + @servername + '], ' + '''SELECT SERVERPROPERTY (''''Edition'''') AS [Product Version]'')))'

    EXEC sp_executesql @statement1

    That isn't a viable solution though 🙂

    Does anyone have an idea as to how I can implement the WHERE clause?

    Cheers, Lars.

  • October 13, 2008 at 5:39 am

    #884088

    Try

    SET @statement1 = N'UPDATE info_version SET productedition = Convert(sysname, (SELECT * FROM openquery ([' + @servername + '], ' + '''SELECT SERVERPROPERTY (''''Edition'''') AS [Product Version]''))) WHERE server = ''' + @servername + ''''

  • October 13, 2008 at 5:53 am

    #884097

    Lars Bjørn (10/13/2008)

    Try

    SET @statement1 = N'UPDATE info_version SET productedition = Convert(sysname, (SELECT * FROM openquery ([' + @servername + '], ' + '''SELECT SERVERPROPERTY (''''Edition'''') AS [Product Version]''))) WHERE server = ''' + @servername + ''''

    Thanks - this works 🙂

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

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