PowerShell: How to connect to a remote SQL database and retrieve a data set

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

Stefan Koell’s Sample Script 

In the comments below, Cloud and Data Center MVP Stefan Koell has posted a nice script of his own, which I have added here to provide nicer formatting.

 

Conclusion

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

7 thoughts on “PowerShell: How to connect to a remote SQL database and retrieve a data set

  1. Profile photo of Stefan KoellStefan Koell

    Pete, you rock, dude!

    I just wanted to fire up google to find an example like this. I am in the process to write a monitor based on a SQL query with the PowerShell module. I think you read my mind and spared me all the trouble stealing and compiling from some sample code.

    Perfect timing! Thanks

  2. Profile photo of Pete ZergerPete Zerger Post author

    Hey Stefan, have you done anything with the SQL queries in PowerShell yet? I have not spent a lot of time parsing multi-record datasets and was wondering what you’ve figured out to this point.

  3. Profile photo of Stefan KoellStefan Koell

    Hi Pete,

    your script was very helpful getting me started! Thanks again. Here’s a trimmed version what I do right now:

    $SQLServer = “hugo”

    $SQLDBName = “somedb”

    $SqlQuery = ‘EXEC [dbo].[usp_mystoredprocedure] ”’ + $parameter1 + ”’, ”’ + $parameter2 + ””

    $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()

    foreach ($row in $DataSet.Tables[0].Rows)

    {

    $Drive = $row[1].ToString().Trim()

    $Threshold = $row[2].ToString()

    $MountPoint = $row[3].ToString().Trim()

    }

    This thing is used for our custom DB disk space monitoring and our DBA’s are putting their thresholds for servers/cluster resources in a table and our monitoring script is fetching those values and checking the disk space (hence the $Drive, $Threshold, … stuff).

    cheers,

    Stefan

  4. Pingback: Database Administration Questions - SQL Server - SQL Server - Toad World

  5. Pingback: SharePoint, PowerShell e um pouco de SQL - Tarcisio Gambin

Leave a Reply