Get the version and service pack details of all sql server instances

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  July 26, 2013 at 1:48 pm

  #280576

  Hi,

  I'm trying to get the version and service pack details from all the sql server instances. I have a table which contains the list of sql servers....i just need the output in this format

  example :

  InstancenameServicePackVersion

  DBATest 10.0.4000.0 SP2

  this table has the list of servers

  select instanceName from dbo.MonitoredSQLServers

  Below scrpit gives the above output

  select SERVERPROPERTY('servername') Instancename,SERVERPROPERTY('productversion') ServicePack, SERVERPROPERTY('productlevel') Version

  Please let me know how to get the details for all the servers, i tried using the cursor...but i'm not much into developing...so any help would be greatly appreciated.

  Thanks in advace

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  July 26, 2013 at 2:03 pm

  #1636319

  Robin35 (7/26/2013)

  ---

  Hi,

  I'm trying to get the version and service pack details from all the sql server instances. I have a table which contains the list of sql servers....i just need the output in this format

  example :

  InstancenameServicePackVersion

  DBATest 10.0.4000.0 SP2

  this table has the list of servers

  select instanceName from dbo.MonitoredSQLServers

  Below scrpit gives the above output

  select SERVERPROPERTY('servername') Instancename,SERVERPROPERTY('productversion') ServicePack, SERVERPROPERTY('productlevel') Version

  Please let me know how to get the details for all the servers, i tried using the cursor...but i'm not much into developing...so any help would be greatly appreciated.

  Thanks in advace

  Are all those SQL Server instances hosted by linked servers? meaning, can you "see" the other servers?

  If yes... run the query on each one of them from your local server.

  If no... log to each server and run the query.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  July 26, 2013 at 2:23 pm

  #1636326

  Thanks Paul for quick response...

  Good question...no linked server..since we have almost 150 servers in our environment...i would like to get a script that connects and retrieve the results...i think i have to go to powershell or command prompt script...

  If you have any link or script or a way to do please let me know

  Thanks

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  July 28, 2013 at 7:02 pm

  #1636511

  another way to do that is to use openrowset or openquery function.

  for more details you can follow these links

  http://sqlblog.com/blogs/buck_woody/archive/2010/03/16/using-linked-servers-openrowset-and-openquery.aspx

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140963