Author Posts

December 7, 2016 at 1:44 pm

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!!!

Thanks

$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
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

December 7, 2016 at 1:56 pm

$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.

December 7, 2016 at 2:40 pm

I should have known it would be that easy.

Thanks!!!!

December 7, 2016 at 2:57 pm

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 {
    [cmdletbinding()]
    param (
        [String]$connectionString,
        [String]$query
        )
    $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
    $adapter.fill($dataset)
    $dataset.tables[0]
}

 
## 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