Oct 162013
 
 October 16, 2013  Posted by at 8:06 am Not So Stupid Questions, PowerShell  Add comments
sea-shells-on-the-sand-600x375

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]

  36 Responses to “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)”

  1. Great post Iris! I friggin love PowerShell 🙂

    • Me to! Which features do you like the most? And any cool things you use it for?

      • I used it to do provisioning to my vagrant environments alongside puppet. Import a database and run your script and there you go!

  2. 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.

  3. I second that, great post Iris.

  4. 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.

  5. For stuff like this, try LinqPad. Heck, I know PS comes out of box, but LinqPad would have saved you 90% of your time.

  6. 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.

  7. 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. 😉

  8. 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”

  9. Awesome! – did exactly what I wanted. Thanks

  10. Thanks Iris, this helped me to monitor sql updates over the weekend.

  11. EXcellent Work – many thanks

    • it didn’t work for me. I am using SQL 2012 standard version, powershell 4.0
      I kept getting logon failure then executeread()

      • 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.

  12. 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

      • It stopped working at $stable.load ($result), powershell 4.0 cannot find load cmdlet.

        • 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. ;-/

  13. Hi Classon,
    Your scripts helped me understand how to connect PS to SQL server. Thank you.

    Mashrur

  14. 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!

  15. 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

  16. 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

  17. 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?

  18. 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. ;-/

  19. Nice guide. Helped me setting up the connection to the sql and now is pulling info off 200 different servers.

  20. 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
    }

  21. 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,

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

What is 6 + 10 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)