Post reply

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

  • April 12, 2019 at 1:42 pm

    #3156788

    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

  • April 16, 2019 at 10:08 am

    #3406005

    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.

  • April 16, 2019 at 3:04 pm

    #3410575

    PoSh is case sensitive.

  • April 17, 2019 at 11:33 pm

    #3435817

    Cary, Steve,

    Great tips! Thank you.

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

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