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?
The Database:
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:
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
Me to! Which features do you like the most? And any cool things you use it for?
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.
I second that, great post Iris.
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.
For stuff like this, try LinqPad. Heck, I know PS comes out of box, but LinqPad would have saved you 90% of your time.
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.
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. ;)
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"
Awesome! - did exactly what I wanted. Thanks
Awesome :D Glad it helped, if you find errors or things that can be improved please share :)
Thanks Iris, this helped me to monitor sql updates over the weekend.
EXcellent Work - many thanks
Thanks... going to use this as a basis for a script that SCOM will kick off on a schedule as a monitor for SQL.
Nice! Post me a link if you post some examples, I'd be interested to see1
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..
it didn't work for me. I am using SQL 2012 standard version, powershell 4.0 I kept getting logon failure then executeread()
Hi Classon, Your scripts helped me understand how to connect PS to SQL server. Thank you. Mashrur
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!
It stopped working at $stable.load ($result), powershell 4.0 cannot find load cmdlet.
Thank you for the kind words, very happy to hear that my little writings help :))
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 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
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?
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.
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. ;-/
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. ;-/
Nice guide. Helped me setting up the connection to the sql and now is pulling info off 200 different servers.
Best advice! I tried out LINQPad and that it was a breeze. Powerful yet simple.
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
} 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, 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