Hello!
Here’s a quick script that lets you query an SQL Database, store the results in an array and export to CSV.
The script uses the .NET SQLClient class for connecting to the database and stores the results in a DataTable object. The connection string uses SQL authentication for the user account.
For more info on the custom object used in this script see this post.
Here’s the script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
$query = "Select * From Table" # Declare query $RetArray = @() # Declare array for holding results $conn = New-Object System.Data.SqlClient.SqlConnection # Declare SQLConnection object $conn.ConnectionString = "Data Source=Server\Instance;Initial Catalog=Database;User ID=SQLUsername;Password=SQLPassword;" # Specify the connection string for the SQL Connection $conn.open() # Open the connection $cmd = $conn.CreateCommand() # Create an SqlCommand object associated with the SqlConnection. $cmd.CommandText = $query # Set the CommandText (aka query) $result = $cmd.ExecuteReader() # Execute the query and store in an SQLDataReader object $table = new-object System.Data.DataTable # Create a DataTable $table.Load($result) # Load the results from the DataReader into the DataTable $conn.Close() # Close the connection to the Database ForEach($Item in $Table) { $object = New-Object -TypeName System.Object $object | Add-Member -type NoteProperty -name Column1 -Value $Item.Column1 $object | Add-Member -type NoteProperty -name Column2 -Value $Item.Column2 $RetArray += $object } $RetArray | Export-csv C:\SQLResults.csv -NoClobber -NoTypeInformation -Force |