0

0

Someone sent me a couple of fairly complex examples of how to connect to a SQL database remotely and retrieve a dataset. Here's a simpler example of the same operation. Would be quite easy to incorporate this into a function in a larger script (read SCOM Health  Check Script) to execute a  number of queries and output data into a text file.

Sample Script

This script provides easy variables to add server name, db name and your SQL query

1. Save in notepad with a .ps1 extension
2. Change the server name and query to desired value
3. Save and run from a PowerShell prompt

$SQLServer = "MySQLServer" #use Server\Instance for named SQL instances! 
$SQLDBName = "MyDBName"
$SqlQuery = "select * from authors WHERE Name = 'John Simon'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
$SqlConnection.Close()

clear

$DataSet.Tables[0]

Conclusion

Seems like this could be the basis to get this OpsMgr health check script in motion we talked about awhile back???

Requirements for a SCOM 2007 Health Check Script?