Direct database edits to the OperationsManager SQL database (and most other System Center SQL databases) are unsupported. And the fact of the matter is, the more time you spend in SQL Mgmt Studio, the more likely something bad will happen eventually. But there are some situations where SQL queries are easier than writing a script, or sometimes necessary.
With Powershell and and the .NET System.Data.SQLClient namespace (the.NET Framework Data Provider for SQL Server), we can execute a query against a database quite easily from a Powershell script.
Here’s a very basic sample in which we connect to the server hosting the OperationsManager database and run a query to retrieve data directly from the database. Could be used in any situation where you would otherwise run a SQL query…This can be run from a remote machine within the trust boundary of the SQL box and of course requires you are logged in with an account with rights on the target database.
$SQLServer = "opsmgr" #use Server\Instance for named SQL instances!
$SQLDBName = "OperationsManager"
$SqlQuery = "select * from consoletask"
$SQLConn = New-Object System.Data.SqlClient.SqlConnection
$SQLConn.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SQLConn
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DS = New-Object System.Data.DataSet
Right away, you’ll see the output is a bit unruly, as the entire contents of the table are returned. Certainly you can limit the result set with WHERE clauses, but it’s often useful to understand how to step through and manipulate query results.
In future posts, we’ll look at error checking, how to filter and format output, and other more advanced uses of .NET Data Provider for SQL Server via Powershell.
Twitter User? Follow us on Twitter at http://www.twitter.com/sysctrcentral