PowerShell

How to Connect SQL with PowerShell

How to connect SQL with PowerShell. PowerShell has built in functionality to connect to SQL databases, which makes it very powerful. Here is how you do it.

Connect SQL with PowerShell

# SQL Server and database
$SQLServer = "localhost" #use Server\Instance for named SQL instances! 
$SQLDBName = "Database"

# SQL Query
$SqlQuery = "select * from table WHERE field = 'data'"

# Connection string 
$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

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

#Close the connection 
$SqlConnection.Close()

#Display result 
$Result.Tables[0]

 

Anders Rødland

Anders Rødland started his IT career in 2006. My main focus is MS Configuration Manager and client management, and I currently hold active 15 Microsoft certifications. Certified on Windows Server, Windows Client, SQL, Exchange and System Center Configuration Manager. Anders Rødland also holds an ITIL Foundation certification. This is my private blog and do not represent my employer. I use this to share information that I find useful. Sharing is caring.