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 have passed 17 Microsoft certifications since then. My main expertise is on client management with Microsoft Endpoint Manager: Intune and Configuration Manager. I also do a lot of work on the security side with Microsoft Defender for Endpoint. In addition to my Microsoft certification, I also have an ITIL v3 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.