I promise to make this worth your time. (Oops, I better make this quick.)
| Alright so with PowerShell if you open it up and just run this code you’ll be able to query your database right from the start. Anyone who has worked with data systems for a while will notice right away the first line of this script kinda looks like a connection string for an application. I’m sure the fact that PowerShell was built on .Net has something to do with that. (But I’m not a developer so I don’t really know, it could be just a coincidence for all I know. |
$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=YourMachineName\YourInstanceName; InitialCatalog=master; Integrated Security=SSPI”)
$conn.Open()
$cmd1 = $conn.CreateCommand()
$cmd1.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd1.CommandText =“sp_databases”
$data = $cmd1.ExecuteReader()
$dt = new-object “System.Data.DataTable”
$dt.Load($data)
$dt | format-table #PipeLining is Awesome!
$conn.Close()
But this code is so much Nicer!
| If you’ve got SSMS 2008 installed on your machine all you need to do is fire up the ISE and add the SnapIn like I talked about in my last blog. Then you can use invoke-sqlcmd and pass it your query. As you see this is a lot easier and cleaner than all of the code above was. |
#Go ahead and add the SQL Snapins
add-pssnapin SqlServerCmdletSnapin100
#sp_databases | format-table
invoke-sqlcmd -query “sp_databases” -database master -serverinstance WIN7\Kilimanjaro | format-table
invoke-sqlcmd -query “sp_databases” -database master -serverinstance WIN7\Kilimanjaro | format-table
Conclusion:
Unless you really like writing 6 or 7 extra lines of code every time you query your database, load up the Snapin and use invoke-sqlcmd.
