Getting SQL Server version from multiple servers.

• alex.clostio

  Grasshopper

  Points: 16

  More actions

  May 10, 2011 at 7:07 am

  #237122

  I have written a script that will loop to get the SQL Server version on multiple servers/instances but it will not output all to a csv file. I am pretty sure it is the foreach command. My script is below. Any help would be appreciated.

  foreach ($svr in Get-Content "C:\DBAScripts\SQLServers.txt")

  {

  $con = "server=$svr;database=master;Integrated Security=sspi"

  $cmd = "select serverproperty('servername') as Name,serverproperty('productversion') as Version,serverproperty('productlevel') ServicePack,serverproperty('edition') as Edition"

  $da = New-Object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = New-Object System.Data.DataTable

  $da.fill($dt)

  $dt | Export-csv C:\DBAScripts\Test.csv

  }

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  May 10, 2011 at 11:11 am

  #1322516

  Try this:

  $dt | select-object Name,Version,ServicePack,Edition | Export-csv C:\DBAScripts\Test.csv -NoTypeInformation

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• alex.clostio

  Grasshopper

  Points: 16

  More actions

  May 10, 2011 at 1:56 pm

  #1322627

  I had my question answered so I thought I would post it here for everyone else.

  # Must call the name of the file as below

  # C:\ PS > .\GetSQLServerVersions3.ps1 | Export-Csv -NoTypeInformation C:\DBAScripts\Test2.csv

  foreach ($svr in Get-Content "C:\DBAScripts\SQLServers.txt")

  {

  $con = "server=$svr;database=master;Integrated Security=sspi"

  $cmd = "select serverproperty('servername') as Name,serverproperty('productversion') as Version,serverproperty('productlevel') ServicePack,serverproperty('edition') as Edition"

  $da = New-Object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

  $dt = New-Object System.Data.DataTable

  $da.fill($dt) | out-null

  Write-Output $dt

  }

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  May 10, 2011 at 2:09 pm

  #1322632

  Yep, that works as well.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato