How to execute SQL Command using PowerShell

How to execute SQL Command using PowerShell

Execute SQL Command using PowerShell

If you are looking to automate some of your work on SQL using PowerShell then below commands or syntax will be helpful in executing commands using PowerShell.

Method 1:

Use SQLCMD.EXE, you need to install SQL management tools on your system, save the SQL query on the file system with .SQL extn and then call it using SQLCMD.exe command.

I have saved my query in C:\SQL\Query.sql file

$SQLServer = <SQL-Server>

$Username = <SQL-Server-Username>

$Password = <SQL-Server-Password>

Invoke-Command -ScriptBlock {cmd.exe /c "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -S $($SQLServer) -U $($Username) -P $($Password) -i C:\SQL\Query.sql} 

Method 2:

$SQLServer = <SQL-Server>

$Username = <SQL-Server-Username>

$Password = <SQL-Server-Password>

$Database = <SQL-Server-DB-Name>

#Write SQL Query here - Single line Multi Line. If Multiline, it is recommended to use .SQL file and use method 1

$SQLQuery = "Select collation_name from sys.databases where name='$($Database)';"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $($SQLServer); Database = $($Database); Integrated Security = False; User ID = $($Username); Password = $($Password);"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlUsers
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$SQLDataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($($SQLUsersDataSet))
$SQLDataSet.Tables[0]

 

 

About The Author

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *