April 12, 2019 at 1:42 pm
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
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
PoSh is case sensitive.
April 17, 2019 at 11:33 pm
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