Powershell only executing first SQL 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

[akau1902]

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

[#3]

Hello @akau1902,

As specified in documentation:

When this cmdlet is run, the first result set that the script returns is displayed as a formatted table.

If subsequent result sets contain different column lists than the first, those result sets are not displayed.

If subsequent result sets after the first set have the same column list, their rows are appended to the formatted table that contains the rows that were returned by the first result set.

Hope that helps.

[#4]

Hi Andy,

Thanks for reply and pointing to the documentation.

I can now see why it is behaving like this. I was however wondering why the same "Sqlcmd" command works the expected way when invoked via command prompt.

As a work around to above I now made a bat file and I am invoking the "sqlcmd" command within the bat file and it is giving me results for multiple SQL statements with different columns all appended in the one same text file, which is what I was looking for.

I just don't understand why it is that when same method is invoked via powershell, it is not able to execute in the same way..

Thanks,

Aiesh