Recently I needed to run Invoke-Sqlcmd that would use a connection string of a variety of parameters. Below is the script I used to accept whatever parameters would be passed in to the “Invoke-sqlcmd”. The input file contained the same text as the $query parameter.

 

$ServerInstance = ".\instance"
$database = "Northgale"
$uname = "user"
$pword = "password123"
$query = "'SELECT GETDATE() AS TimeOfQuery;'"
$inputFile = "C:\Users\Richard\Documents\inputfile.sql"

$connectionString = "-ServerInstance $ServerInstance -UserName $uname -Password $pword -Database $database -verbose"
$connectionString = $connectionString -split '(?=prg=PowerShell-)'
$InvokeSqlcmd = "Invoke-Sqlcmd -inputfile $inputFile $connectionstring"
write-host $InvokeSqlcmd -verbose
Powershell.exe -command "$invokeSqlCmd"