How to run a SQL command against a Microsoft SQL Server Database (MSSQL) from a PowerShell script.

cls 

$datasource = "server=server\instance;database=mydb;trusted_connection=true"

#if not using Integrated Security, 
#you might want to pass user/pass in variables or prompt for them 
#$connectionString = 'User Id=' + $username + ';Password=' + $password + ';' + $datasource
$connectionString = $datasource
write-host $connectionString 
$connection = New-Object System.Data.SQLClient.SQLConnection($connectionString)
$connection.open()

# -- reset so Biztalk ReceiveLocation Polling Process will pick them up 
$updateCommand = "update PollingTable set entryStatus = 'New' where ID in (101, 102)  "

Write-Host $updateCommand 

$Command = New-Object System.Data.SQLClient.SQLCommand 
$Command.Connection = $connection 
$Command.CommandText = $updateCommand 

$rowsAffected = $Command.ExecuteNonQuery() 
Write-Host ("Rows Affected by Update=$rowsAffected") 

$connection.close()  #not sure if this helps... 
                     #but in case you have more code after this... 

Filed under: Powershell