#61 : How to connect SQL Server with Powershell?

Powershell can be used to run queries against SQL Server. In fact, what C# and other .NET Framework based languages can do, you can do all of them wit h Powershell. You can connect SQL Server and perform all operations which you do with Powershell.

Here is a simple code for this :

	#--------------------------------------------------------------------------------------------------------------
	#-- Script to run query and get recordset
	#--------------------------------------------------------------------------------------------------------------
	function Run-Query( [string]$InstanceName , [string]$DatabaseName, [string]$Query)
	{
	$SQLInstance = $InstanceName
	$DBName = $DatabaseName
	$SQLQry = $Query
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLInstance; Database = $DBName; Integrated Security = True"
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SQLQry
	$SqlCmd.Connection = $SqlConnection
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd
	$DataSet = New-Object System.Data.DataSet
	$SqlAdapter.Fill($DataSet)
	$SqlConnection.Close()
	#--Show data in table --#
	$DataSet.Tables[0]
	}
	#--Check the running processes --#
	Run-Query "SQLTEST" "master" "select top 1 * from master..sysprocesses"
	#--Find the blocked SPID --#
	Run-Query "SQLTEST" "master" "select * from master..sysprocesses where blocked > 1 "
	#--------------------------------------------------------------------------------------------------------------

view raw connect-sql.ps1 hosted with ❤ by GitHub

Hope you like this article! It's too late night (2:30 AM) and I have to go to bed.
Enjoy!