Powershell script to query SQL table

I recently had cause to create a PS (Powershell) script in order to query a SQL table. This in itself was easy enough, the only thing that was bothering me was that once I had read the data from the table I needed to read the data from the extract and loop this through my script to continue another process.

The issue I had was exporting the data in such a way that I could import it easily into a PS script. After much searching I found some really helpful sites, but none which appeared to do exactly what I wanted, all were close though and so I put a few together to create one that performed exactly as I wanted. So I’m sharing it in case anyone has this problem again.

Please note I’m only a very average script writer, with limited knowledge in this area, however I can perform the basics, and do most of what I need to. You may always find better script writers out there (would be crazy not to mention Scripting Guy amongst others) and better methods of performing what I needed – if you do please share! Otherwise please feel free to use as follows:

#SQL connection details
$SQLServer = "server" #use Server\Instance for named SQL instances!
$SQLDBName = "dbname"
$SqlQuery = "SELECT * FROM table"
#location of extract file
$extractFile = @"
c:\scripts\csv\list.csv
"@
#create SQL connection and extract dataset to file
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
clear
$DataSet.Tables[0] | Export-Csv -Force -Delimiter "," $extractFile

Once I’d completed this the data was extracted in the columns I wanted from the SQL table (my select statement was actually fairly more complex to account for codes and required texts; 0s!) I could loop my data through a foreach command.

Just replace the SQL Server name, database, Select statement and file locations with yours and you will be ready to go.

Happy days.

Advertisements
This entry was posted in Script and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s