Powershell only executes first sql statement in the sql file

[akau1902]

Hello Everyone,

I am trying execute two simple SELECT statements placed in a sql file and then trying to execute the sql file via powershell and dumping the results in a text file using the below code. However it only executes the first select statement.

$SQL = Get-Content -Path "C:\SQL Reports\Daily Checks.sql"

Invoke-Sqlcmd -Username "*****" -Password "*****" -ServerInstance "*****" -Database "*****" -Query $SQL | Out-File -FilePath "C:\SQL Reports\powershelloutput.txt"

It is just printing the results of the first sql in the output file and not the second one.

Can someone please guide what may be wrong or if the above method is not supposed to execute multiple sqls in a sql file.

The simple sql statements are like below

SELECT * FROM TABLE1

SELECT * FROM TABLE2

Thanks,

AK

[#2]

@akau1902, the CmdLet Invoke-SqlCmd supports the full feature set of the SqlCmd utility, see https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

So add a batch separator GO to your script =>

SELECT * FROM TABLE1
GO
SELECT * FROM TABLE2