Need to extract a query result to CSV from a list of servers

  • TrailRunner

    SSCommitted

    Points: 1540

    I have been trying the following PowerShell script to extract a query result from a list of SQL instances to a single CSV output file. It seems to be a common task, but I have been struggling with it. I got a file generated, but empty. So either I am not looping through the list correctly, or I am not constructing the dataset result correctly. Any hint you can provide will be greatly appreciated. Thank you.

    Powershell version below:

    $PSVersionTable.PSVersion

    Major  Minor  Build  Revision

    -----  -----  -----  --------

    5      1      14409  1018

    $dataset = New-Object System.Data.DataSet
    $serverlist= Get-Content -path D:\SQLserverlist.txt
    foreach($Server in $serverlist) {

    $connectionString = "Server=$Server;Database=tempdb;Integrated Security=True;"
    $SqlQuery = "Some select statements here..."

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

    $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)
    $SqlConnection.Close()
    }

    $OuputFile = "\\Networkpath\foldername\extract.csv"
    If (Test-Path $OuputFile ){
    Remove-Item $OuputFile
    }

    $DataSet.Tables[0] | select "servername","drive","freespace","totalsize","percentfree" | Export-Csv $OuputFile
  • Cary Hower-563110

    SSChasing Mays

    Points: 608

    The $DataSet that you are piping to Export-Csv is not in the same scope as the $DataSet that you filled. You might try removing the $DataSet within your loop and replacing the first statement of your script with $DataSet rather than $dataset. You may also have to define a primary key for your dataset.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714708

  • TrailRunner

    SSCommitted

    Points: 1540

    Cary, Steve,

    Great tips! Thank you.

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

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