PowerShell Function multiple SQL queries output as CSV

Question

I'm having problems getting PowerShell to run multiple SQL queries and export the results as CSV.

I'm trying to accomplish this using a Function but the problem occurs in the Process block when I expect two queries to run and output two CSV files.

I tried creating one function to run the query and a second function to create the CSV files but that didn't even run the SQL queries. I'm doing this without SQL being installed where this powershell script is executed from. -thanks!

Function Run-Query {
 param([string[]]$queries,[string[]]$sheetnames)
Begin{
 $SQLServer = 'ServerName'
 $Database = 'DataBase'
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
}#End Begin
Process{
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 $SqlCmd.CommandText = $queries
 $SqlCmd.Connection = $SqlConnection
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SqlAdapter.SelectCommand = $SqlCmd
 $DataSet = New-Object System.Data.DataSet
 $SqlAdapter.Fill($DataSet)
 $DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path "C:\Scripts\$sheetnames.csv"
}#End Process
End{
 $SqlConnection.Close()
}
}#End function run-query.
$queries = @()
 $queries += @'
Select * from something
'@
 $queries += @'
Select * from something2
'@
$sheetnames = @()
$sheetnames += 'Cert'
$sheetnames += 'Prod'
Run-Query -queries $queries

Bluecakes · Accepted Answer · 2015-03-04 05:06:16Z

6

I'm not sure if SQL processes multiple queries separately so while you might be passing two different queries the SQL server might be interpreting them as one query (Not 100% sure this is happening, just a guess really)

You've put your queries in an array so we can easily loop through the array, run each query by itself and put the results into a CSV.

Here's how i'd modify your code to start with:

Function Run-Query
{
    param([string[]]$queries,[string[]]$sheetnames)
    Begin
    {
        $SQLServer = 'ServerName'
        $Database = 'DataBase'
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
    }#End Begin
    Process
    {
        # Loop through each query
        For($i = 0; $i -lt $queries.count; $i++)
        {
            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

            # Use the current index ($i) to get the query
            $SqlCmd.CommandText = $queries[$i]

            $SqlCmd.Connection = $SqlConnection
            $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
            $SqlAdapter.SelectCommand = $SqlCmd
            $DataSet = New-Object System.Data.DataSet
            $SqlAdapter.Fill($DataSet)

            # Use the current index ($i) to get the sheetname for the CSV
            $DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path "C:\Scripts\$($sheetnames[$i]).csv"
        }
    }#End Process
    End
    {
        $SqlConnection.Close()
    }
}#End function run-query.

$queries = @()

$queries += @'
Select * from something
'@
$queries += @'
Select * from something2
'@

$sheetnames = @()
$sheetnames += 'Cert'
$sheetnames += 'Prod'

Run-Query -queries $queries -sheetnames $sheetnames

answered Mar 4, 2015 at 5:06

Bluecakes

2,17919 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4317867 Over a year ago

Too funny! That is how I originally did this as seen in my question here I'll work this up and report back! Thank you!

user4317867 Over a year ago

Please excuse my ignorance! In my original question, observe my Function call omitted the second param!!!! Adding that causes the script to loop perfect, thank you for guiding me back to sanity Bluecakes!

Collectives™ on Stack Overflow

PowerShell Function multiple SQL queries output as CSV

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related