Post reply

Get version details of multiple SQL server's

  • January 20, 2011 at 7:29 am

    #232510

    Hello All,

    I have a task of querying up some 400+ SQL server and getting the Version details of the SQL server installed on the machines. Doing this manually would kill me 🙂

    So I am thinking to Automate this procedure using PowerShell and the plan is

    1. To create a function that will take the input parameter as the server/instance name and then create a connection with the input named SQL Server and then run the query : select @@version

    2. Capture the output given out by the function into an array or else if possible into an excel file.

    3. Create a loop using for or foreach and repeat the above steps till all servers are queried and the output data is saved.

    I am pretty new to PowerShell so I need some ideas if this can be successful or else if I need to make any changes in the procedure. The script that I have got uptill now by reading out blogs, to connect to the server and get details is :

    **************************************************************************

    $SqlServer = $servername # $servername is the server/instance picked up from excel file

    $SqlCatalog = "Master"

    $SqlQuery = "select @@version"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd.CommandText = $SqlQuery

    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet

    $SqlAdapter.Fill($DataSet)

    $array=$dataset.tables[0]

    $SqlConnection.Close()

    ***************************************************************************

    Please feel free to provide any inputs that can be useful in terms of process flow or in script.

    Thanks in advance

  • January 25, 2011 at 1:41 pm

    #1278168

    In powershell you really don't exactly have to run the query against the server, since you have those properties available in different ways.

    For instance, this script will go find your sql servers (all on the network...even workstations which you may not care about at all) and then return the name and version of each of them.

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

    $smoObj = [Microsoft.SqlServer.Management.Smo.SmoApplication];

    # This gets the sql servers available

    $sql = $smoObj::EnumAvailableSqlServers($false)

    foreach($sqlserver in $sql)

    {

    $sqlserver.Name; $sqlserver.Version;

    }

    You could pipe these kinds of results out to whatever format you wanted to, whether it be to a text file, excel spreadsheet, load into a database etc...

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

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