Execute SQL Stored Procedure and export results to CSV

This topic contains 3 replies, has 3 voices, and was last updated by  adam funck 10 months, 2 weeks ago.

  • Author
    Posts
  • #59625

    Nathan W
    Participant

    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]
  • #59628

    Ron
    Participant

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

  • #59635

    Nathan W
    Participant

    I should have known it would be that easy.

    Thanks!!!!

  • #59646

    adam funck
    Participant

    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

You must be logged in to reply to this topic.