Querying SQL Server using PowerShell
The great thing about PowerShell is direct access to objects. For my apps, database connectivity is happily handled by NHibernate; however, that doesn’t mean we can’t take advantage of good old System.Data.SqlClient for our PowerShell scripting.
CODE: The full source of this is available here on codepaste.net.
[string]$server = “.”,
[string]$instance = $(throw “a database name is required”),
$connection = new-object system.data.sqlclient.sqlconnection( `
“Data Source=$server;Initial Catalog=$instance;Integrated Security=SSPI;”);
$adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
$set = new-object system.data.dataset
$table = new-object system.data.datatable
$table = $set.Tables
Not too long or challenging—it’s mostly working to instantiate a quick SQL connection and pass in your query. I even considered plugging in a check on the $query parameter to ensure it began with SELECT to ensure I wouldn’t do accidental damage to a system. Maybe I’m just paranoid.😉
What this little snippet allows me to do is quickly add log4net checking into some of my server monitoring PowerShell scripts.
query sqlServer myDatabase “Select count(id), logger from logs group by logger” | format-table -autosize
Notice I didn’t include the format-table command in my main query script. Why? I wanted to keep the flexibility to select, group, and parse the information returned by my query. Unfortunately, it seems that the format commands break that if they’re ran before a manipulation keyword. Adding in “ft –a” isn’t difficult in a pinch.
Quick and easy…
- Customer calls up with a question about data—save time and do a quick query rather than waiting for Management Studio to wind up.
- Keep tabs on database statistics, jobs, etc.
- and more…