Get remote sql server properties

  • Hi all,

    I am trying to fetch, some SQL SERVER PROPERTIES on remote network machines (I have their names in a table).

    I was trying do as below:-

    (1)

    SELECT 'SQL Server '

    + CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + ' - '

    + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '

    + CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('

    + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

    And then do a join of the above on the PCs Name (from) my local table.

    But, as you can see, I need a “FROM CLAUSE” to complete the join.

    Does anyone know from what system table the server properties are coming from?

    Else, could you please suggest an alternative for getting remote servers information (having these server names locally)?

    Thanks in advance!

    (2)

    --:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

    JOIN DwnLdbleSoftware_Tab ON MachineName = DwnLdbleSoftware_Tab.[PcName]

    --:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

  • because SERVERPROPERTY() is a function, even if you had " FROM RemoteServer.master.dbo.sysobjects" as the from clause, the function will return the info from the machine you are connected to...not the remote server.

    In this situation, I've always gone to the tables that might contain the data on the remote server, and not try to use server functions....

    ie SELECT * FROM RemoteServer.sys.servers

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Lowell,

    I probably didn't explain me good enough, however, could you please give more input on how this (read remote sql server property)can be accomplished? If you know. Many thanks.

  • SELECT a.* FROM OPENROWSET('SQLOLEDB',

    'DRIVER={SQL Server};SERVER=.\yukon1;Trusted_Connection=yes;',

    'select @@servername') AS a

    MJ

  • This work just fine, as long as you have RPC turned on for your linked server:

    exec ServerName.master.dbo.sp_executesql N'Select SERVERPROPERTY(''MachineName'')'

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thank you RBarry and MJ for your efforts, the problem is that I have these servers in a corperate network, therefore, in different locations round the globe(350 machines), therefore, configuring RPC or Linked server for everyone of them, apart from the security risk, could be tidious.

    It might be useful letting you know why am doing this:-

    FOR LICENSING REASONS, I NEED TO KNOW WHAT VERSIONS(developer,enterprise ecc.) OF SQL SERVERS ARE ON THESE MACHINES

    Any futher help will be useful. Many thanks.

  • In my organization, we come across such situation very frequently, we have some 500+ servers running across globe. We mix DOS batch script with sql query.

    1. Batch will open connection using osql or sqlcmd and run the query

    osql -E -S servername -Q"set nocount on;your query" -n -w999 >> output.txt

    2. out put will be forwarded to one single file ( in DOS >> is create/append , > is create and redirect)

    3. using DOS findstr command we'll truncate all useless line like hyphens and blank lines

    If you want this i can provide you some code.

  • Please! I really will appreciate if you can provide me the code. Thank you in advance.

  • Ok..

    I have 3 files

    1. abc.cmd - This is the batch file we will call from DOS prompt

    [font="Courier New"]for /f %%a in (srvlist.txt) do osql -E -S %%a -i scr.txt -n -w999 >> out.txt

    findstr /V "\-\-\-" out.txt > temp.txt

    findstr "[a-z]" temp.txt > out.txt

    del temp.txt

    notepad out.txt[/font]

    2. srvlist.txt - This file list of all servers to connect, for example, i am using following servers (running on my PC)

    [font="Courier New"]SAC\SQL9_SRV1

    SAC\SQL8_SRV1

    SAC\SQL8_SRV2[/font]

    3. scr.txt - this is sql code i have to run

    set nocount on

    SELECT 'SQL Server '

    + CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + ' - '

    + CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '

    + CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('

    + CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

    Save these files, and from command prompt go to location where these files are

    [font="Courier New"]cd /d d:\folder_name[/font]

    then type

    [font="Courier New"]abc.bat[/font]

    It will run -

    1. for all servers in list one by one (for /f %%a in (srvlist.txt) do osql -E -S %%a -i scr.txt -n -w999 >> out.txt)

    2. Now it will truncate extra hyphens (findstr /V "\-\-\-" out.txt > temp.txt)

    3. Then all blank lines (findstr "[a-z]" temp.txt > out.txt)

    4. Then popup the result in notepad (notepad out.txt)

    Please remember -

    1. There might be some servers which you are unable to connect, for those you will see some error messages in output file, deal such servers manually.

    2. If you are OK with some hyphens or spaces, comment those command lines (:: is used to comment in DOS)

    Try this with few servers and let me know if it works.

  • I'll let you know as soon as possible. Many thanks.

  • greg

    If you are on SQL 2005, look into PowerShell as well, it is great for this purpose. Google for some examples to grab server versions

    If not, above batch script is quite typical

    I suggest using SQLCMD instead of OSQL though

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • greg eze (4/2/2009)


    Thank you RBarry and MJ for your efforts, the problem is that I have these servers in a corperate network, therefore, in different locations round the globe(350 machines), therefore, configuring RPC or Linked server for everyone of them, apart from the security risk, could be tidious.

    You can script this also. And I am not aware of any particular security risk, given that every single client SQL user in your corporate network can already use RPC to your servers.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • To make use of RPC, I will definitely have to turn off the fire walls of those machines,we have them turned on by default. That's where the security issue comes in.

    I shall try the script Boolean_z sent to me when am done with what am doing right now, and let you'all know as soon as possible. However any further input will be welcomed.

    Many thanks to everyone that has contributed so far.

  • Jerry Hung (4/2/2009)


    greg

    If you are on SQL 2005, look into PowerShell as well, it is great for this purpose. Google for some examples to grab server versions

    I think, Powershell is introduced in SQL 2k8, its not available in 2k5.

  • Hi Boolean_z,

    I tryed the script, it worked for my locally installed sql server, and, linked servers on the local machine. But I got the error below for the remote servers. I don't know if you can add more to this?

    However, this is the best solution I have so far, come's to the worst, I'll just execute the script on different machines involved, scripting the results to a shared folder in the network.

    I really appreciate the effort. Many thanks.

    [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].

    [SQL Native Client]Login timeout expired

    [SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

    [SQL Native Client]SQL Network Interfaces: Connection string is not valid [87].

    [SQL Native Client]Login timeout expired

Viewing 15 posts - 1 through 15 (of 20 total)

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