Execute SQL Stored Procedure and export results to CSV

Hello everyone.
I have been tasked with creating a PS script that will connect to a MSSQL server, execute a stored procedure and export the results to a CSV file.

I have found the following code that should run the stored procedure but I always have a problem getting it outputted to the CVS file.
If anyone can help me out that would be great!!!


$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "sp_helpdb"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

$DataSet.Tables[0] | Export-CSV -notype path\file.csv

I’m guessing, I’m assuming that the last line outputs the table, I haven’t worked with MSSQL.

I should have known it would be that easy.


This was a massively hard concept for me to grasp when I first ran into it as well.
There are some complexities that the more technically eloquent can speak to; but a decent conceptual starting point is that your SQL request returns an ad-hoc array, so powershell doesn’t know what to do with it when you try to treat it as a table.

Below is some mildly sanitized production code.

 function invoke-SQLquery {
    param (
    $connection = new-object -TypeName System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $adapter = new-object -typename System.Data.SqlClient.SqlDataAdapter $command
    $dataset = new-object -TypeName System.Data.DataSet

## Create Arrays  
$myOut = @()
$queryResults = @()

$query = " EXEC [dbo].[spAlarmsSelect]    
         @BeginTimeStamp = '$timeReference'"

$connectionstring = 'redacted'

$queryResults = invoke-SQLquery $connectionstring $query
Write-Verbose "selecting alarms. $query"

    foreach($o in $queryResults)
        if ($o.alarmid)
        $myO = @()
        $myO = new-object system.object
        $myO | Add-Member -MemberType NoteProperty -name Line        -value (($o.alarmID -split "_")[0]) 
        $myO | Add-Member -MemberType NoteProperty -name Alarm       -value (($o.alarmID -split "_")[1])
        $myO | Add-Member -MemberType NoteProperty -name alarmdesc   -value $o.alarmDescription
        $myO | Add-Member -MemberType NoteProperty -name TimeStamp   -value $o.alarmeventdate
        $myO | Add-Member -MemberType NoteProperty -name TLMC        -value $o.TLMoldCount
        $myO | Add-Member -MemberType NoteProperty -name Pattern     -value $o.Pattern
        $myO | Add-Member -MemberType NoteProperty -name Status      -value $o.MoldStatus
        $myO | Add-Member -MemberType NoteProperty -name StopCause   -value $o.StopCause
        $myO | Add-Member -MemberType NoteProperty -name RunTime     -value ( "{0:N1}" -f $o.RunTime )
        $myO | Add-Member -MemberType NoteProperty -name OpSetTime   -value $o.OperatorSetPourTime
        $myO | Add-Member -MemberType NoteProperty -name MaxTemp     -value ( "{0:N0}" -f $o.MaxPourTemp )
        $myOut += $myO

Once you’ve done this $myOut can be sorted, converted to csv, etc.
My standard usage is to

convert-tohtml -as table
and then insert it into the body of an email.

Please note there are several ways of turning those array results into your own custom object, and there are some reasons to use splatting rather than add-member… but for myself personally I find this layout really easy and accurate to modify when I use it as a template.

Hope that helps