Iris Classon
Iris Classon - In Love with Code

How do I query a SQL Server DB using PowerShell, and how do I filter, format and output to a file? (Stupid Question 251-255)

In the winter darkness the Swedes have become hard to identify (even with the pale blonde hair and strong accent), and we need a list so we can get the invites out for the next ‘Fika’ (Swedish tradition). It would be horrible if we got non-Swedes at the fika, after all nobody can appreciate this as much as we do. And of course, since we like our ‘Snapps’ and ‘Glögg’ they have to be old enough. All we have is a SQL Server instance, user credentials and PowerShell. How are we ever going to get a list of all the Swedes?

sqlserver-powershell-filter4

The Database:

sqlserver-powershell-filter1

We need to first connect to the server. Do we have our credentials? Yes we do. But in case we don’t and we are using Windows Authentication then just Integrated security might do.

WARNING! This girl, AKA me, is NOT a PowerShell expert, so there will be errors. I do however have the code on GitHub, feel free to fork, change, refactor or whatever, make sure you let me know if you find a better way, or if I have error here!

Declare the variables (so we can keep it tidy)

$dataSource = “.\SQLEXPRESS”
$user = “user”
$pwd = “1234”
$database = “Test”
$connectionString = “Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;”

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()

Alright. We set up our connection, and opened in. Houston we are in the building ( I know I can’t say that, but it’s my story).

Time to tell that lousy SQL Server what to do, let’s create some commands to our little spinning server slave.

$query = “SELECT * FROM Person”

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

We’ll get all the people, and worry about how to sort them out later.

Of course we want this nicely formatted, and so we’ll use a DataTable which gives us an in-memory table with the data, and we need to create it first then load it.

$table = new-object “System.Data.DataTable”
$table.Load($result)

Once we output the information we want to not use the column names, to boring. We want our own table, so we will create a variable called format that defines how we want our table to look like. We want the Id column to be named User Id, and we set a smaller width, and Name to be Identified Swede.

$format = @{Expression={$_.Id};Label=“User Id”;width=10},@{Expression={$_.Name};Label=“Identified Swede”; width=30}

We have all our people. Now we need to identify the Swedes that are born earlier than 1990. And how are identifying Swedes? Easy, we are so unimaginative (or traditional) that all our surnames end in sson.

$table | Where-Object {$_.Surname -like “*sson” -and $_.Born -lt 1990} | format-table $format

$table | Where-Object {$_.Surname -like “*sson” -and $_.Born -lt 1990} | format-table $format | Out-File C:\Users\Iris\Documents\swedes.txt

$connection.Close()

We take our table then we pass on the objects by piping them, we grab each object for filtering using Where-Object and within the curly braces (or binary hugs as I call them) and tell it what we want. It should be like something-then-sson (wildcard) and Born should be less than (-lt) 1990. Then we pass on the result to the format table command that is given our format.

And once we can see the output, we can confirm it is as we want, and then output to a text file.

The text file:

sqlserver-powershell-filter2

The Swedes can now go fika.

How easy wasn’t that?

Iris + PowerShell = true

Here is the full script for the copy and pasters, fika not included. I can’t get the formatting right, but the code is here: https://github.com/IrisClasson/PowerShell_General/blob/master/sql.ps1

[sourcecode language=“powershell”]
$dataSource = “.\SQLEXPRESS”
$user = “user”
$pwd = “1234”
$database = “Test”
$connectionString = “Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;”

$query = “SELECT * FROM Person”

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
#$connection.ConnectionString = “Server=$dataSource;Database=$database;Integrated Security=True;”
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)

$format = @{Expression={$_.Id};Label=“User Id”;width=10},@{Expression={$_.Name};Label=“Identified Swede”; width=30}

$table | Where-Object {$_.Surname -like “*sson” -and $_.Born -lt 1990} | format-table $format

$table | Where-Object {$_.Surname -like “*sson” -and $_.Born -lt 1990} | format-table $format | Out-File C:\Users\Iris\Documents\swedes.txt

$connection.Close()

[/sourcecode]

Comments

Leave a comment below, or by email.
Mike Paterson
10/16/2013 6:30:13 PM
Great post Iris!  I friggin love PowerShell :-) 
Iris Classon
10/17/2013 2:24:57 AM
Reply to: Mike Paterson
Me to! Which features do you like the most? And any cool things you use it for? 
Mike Shepard
10/17/2013 7:03:06 AM
You might also like the adolib module which is installed as part of SQLPSX (SQL PowerShell Extensions).  It wraps the ado.net functionality in more powershell-y cmdlets. 
Brandon Dooley
10/17/2013 2:47:41 PM
I second that, great post Iris. 
john
10/21/2013 7:35:04 AM
Thanks Iris,  Can't believe it took me almost a day to figure out PS and SQL Server.  Your snippet was the key.  All is good now...Thanks much for keeping it simple, and workable.  Amazing how much non-working stuff there is out there.  

j. 
Bruno Brant
10/28/2013 5:31:20 AM
For stuff like this, try LinqPad. Heck, I know PS comes out of box, but LinqPad would have saved you 90% of your time. 
DavDaveeD
12/20/2013 2:52:00 PM
Cool.  Way simpler to re-use your post than the multitude of differing ways to do this on Microsoft's sites (most of which are complicated and over-engineered).  Thanks for keeping it simple and straight-forward. 
Paul
2/10/2014 8:03:36 PM
It would be much faster and efficient to write a better query and let the SQL sort/select the data rather than parsing a whole * table in powershell.  ;) 
Iris Classon
2/11/2014 12:29:12 PM
Reply to: Paul
That is true, but its just a demo :) 
Dominic Zukiewicz
2/18/2014 7:41:05 AM
You've also got the Invoke-SqlCmd to do this for you, and will return the results into a PowerShell variable:

$table = Invoke-SqlCmd -Database AdventureWorks2012 "select top 10 * from Orders" 
Mike Meyer
3/20/2014 7:25:20 PM
Awesome! - did exactly what I wanted.  Thanks 
Iris Classon
3/21/2014 12:47:42 PM
Reply to: Mike Meyer
Awesome :D Glad it helped, if you find errors or things that can be improved please share :) 
Harry
4/12/2014 5:34:56 PM
Thanks Iris, this helped me to monitor sql updates over the weekend. 
Iris Classon
4/16/2014 12:08:40 PM
Reply to: Harry
Awesome! I should fine the time to write more examples like that! 
Lewis
4/17/2014 12:42:58 PM
EXcellent Work - many thanks 
RobInNY
5/28/2014 9:38:59 PM
Thanks... going to use this as a basis for a script that SCOM will kick off on a schedule as a monitor for SQL. 
Iris Classon
6/3/2014 7:23:14 AM
Reply to: RobInNY
Nice! Post me a link if you post some examples, I'd be interested to see1 
Cesar Duran
7/18/2014 10:58:44 AM
Reply to: Paul
Agree with Iris, in my case I'm taking only the part of the where she is connecting and retrieving data from a table in SQL, the I will process the result on a logon script.. 
ginger
8/18/2014 2:23:30 PM
Reply to: Lewis
it didn't work for me. I am using SQL 2012 standard version, powershell 4.0
I kept getting logon failure then executeread() 
Mashrur
8/19/2014 1:00:00 PM
Hi Classon,
Your scripts helped me understand how to connect PS to SQL server. Thank you.

Mashrur 
Stuart
8/20/2014 7:48:53 AM
This is exactly what I needed for a problem I was having. The default Query Database activity in System Center Orchestrator uses ; as a delimiter between fields in query results, but of course Microsoft Endpoint Protection loads multiple path values for malware events into a single field separated by, you guessed it, a ;. Now I can just use PowerShell to control what's coming out of SQL...awesome!

Tack för att dela! 
Ginger
8/20/2014 11:44:58 AM
Reply to: Iris Classon
It stopped working at $stable.load ($result), powershell 4.0 cannot find load cmdlet. 
Iris Classon
8/24/2014 3:00:58 PM
Reply to: Mashrur
super! 
Iris Classon
8/24/2014 3:04:57 PM
Reply to: Stuart
Thank you for the kind words, very happy to hear that my little writings help :)) 
Ibrahim Ersoy
8/29/2014 12:33:56 AM
Hi Iris,

Using sqlcmd always save time :) 
An example of usage: 

$table = Invoke-Sqlcmd -ServerInstance [servername] -U [username] -Password [password] -Database [database] "[your query here]"

Exporting to a file:
Using Clixml: $table | Export-Clixml "{path}\data.xml"
Using Out-File: $table | Out-File "{path}\data.xml"

Cheers 
Ibrahim Ersoy
8/29/2014 12:37:54 AM
Reply to: Dominic Zukiewicz
exactly.this is the most common way to do it. 
Derek
8/29/2014 1:12:46 PM
Thanks Iris. This helped me out tremendously. I like how you broke it down and explained each step. I took a look around your page and some videos. I admire how much you've accomplished in a short period of time. Keep it up. Thanks for the pointers here.  : )

Derek 
Tim
9/8/2014 6:31:36 PM
I think everyone said Iris's script had worked  fine on a server or with SQL express installed on your PC. Not on a PC with SQL SMO and SQL client connectivity SDK Installed.

Iris: Just wonder what version of SQL express you used? 
Sweatshirt
9/19/2014 8:23:01 AM
Reply to: ginger
Make sure that you alter the credentials in the code she gives you to match the servers that you're connecting to on your end. 
Ron
6/1/2015 7:38:44 PM
Reply to: Ginger
I ran into the same problem. Iris has provided this line:

$table = new-object “System.Data.DataTable”

I changed it to:

$table = new-object System.Data.DataTable "MyNewTable"

And I received the expected results. I'm a PowerShell newb, so it took be better par of an hour to figure that out. ;-/ 
Ron
6/1/2015 7:39:42 PM
I ran into a problem with the table loading result. I resolved it by changing the line below. Thank you Iris for the snippet, works great after I changed it to below.

$table = new-object “System.Data.DataTable”

I changed it to:

$table = new-object System.Data.DataTable "MyNewTable"

And I received the expected results. I'm a PowerShell newb, so it took be better par of an hour to figure that out. ;-/ 
The Onsite Viking
6/15/2015 7:02:24 AM
Nice guide. Helped me setting up the connection to the sql and now is pulling info off 200 different servers. 
John Dale
11/9/2015 4:18:55 PM
Reply to: Bruno Brant
Best advice! I tried out LINQPad and that it was a breeze. Powerful yet simple. 
Vic
7/8/2016 1:09:50 AM
Thank's a lot for script!
It's a pity I couldn't join "Fika" :)

I my case I needed to do some more manipulation with table records, so I added "foreach" statement.

$table | foreach {
    write-host "Name value is :" $_.Name
    write-host "Born value is :" $_.Born
} 
Will Howard
11/27/2016 7:52:33 PM
Thanks Iris,
This has just been an absolute life-saver.

The only problem I had is that whatever version of powershell I'm using, or specific set I'm using, I got an error with the line:
$table = new-object “System.Data.DataTable”
I had to change it to:
$table = new-object System.Data.DataTable
Or
$table = new-object "System.Data.DataTable"


New-Object : Cannot find type [â?oSystem.Data.DataTableâ??]: make sure the asse
mbly containing this type is loaded.
At C:\Temp\Testing\ADF.PS1:17 char:20
+ $table = new-object <<<<  â?oSystem.Data.DataTableâ??
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentExcepti
   on
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewOb
   jectCommand

You cannot call a method on a null-valued expression.
At C:\Temp\Testing\ADF.PS1:18 char:12
+ $table.Load <<<< ($result)
    + CategoryInfo          : InvalidOperation: (Load:String) [], RuntimeExcep
   tion
    + FullyQualifiedErrorId : InvokeMethodOnNull

Tripped me up because everything else about your tutorial was so friendly to such a powershell newbie as me.

Best Regards, 
Malan van Eck
4/6/2016 1:12:27 AM
Reply to: Iris Classon
I used it to do provisioning to my vagrant environments alongside puppet. Import a database and run your script and there you go! 


Last modified on 2013-10-16

comments powered by Disqus