CODEWITHSUNDEEP
sql-serverpowershell
RaviLobo
RaviLobo

Reputation: 508

Invoke-SQLCMD unable to run multiple SQL Statements in PowerShell

I'm unable to run 2 SELECT statements from Invoke-sqlcmd.

$sql ="
select SERVERPROPERTY('ProductVersion') Version
select SERVERPROPERTY('ProductLevel') ServicePack, SERVERPROPERTY('edition') Edition"
    
$server = 'SomeServerName'    
Invoke-Sqlcmd -ServerInstance $server -Query $sql

Only the first TSQL statement returns the result.

Note: I can re-write the TSQL to make one statement. That is not the point. The original script has multiple TSQL commands. Also, I can do individual invoke-sqlcmd calls, for each statement. However, I'm trying to understand if this is a limitation of invoke-sqlcmd.

Is this the intended behavior?

Thank you so much.

Upvotes: 3

Views: 4002

Answers (2)

mclayton
mclayton

Reputation: 9975

You've already got an answer that works, but this is more an explanation of what's going on...

Basically, you've got a display / formatting issue rather than a problem with Invoke-SqlCommand.

In your example, your on-screen output probably looks like this:

PS> Invoke-Sqlcmd -ServerInstance $server -Query $sql

Version
-------
15.0.2070.41



PS> _

Note the 3 blank lines after the table - the first blank line is actually the results from the second sql command!

When PowerShell formats a table of items it uses the first item to decide what columns to show - the results from your first command contains just a "Version" column so that's all the table contains. The result from your second command doesn't have a "Version" column so PowerShell displays an empty value in that column, but it also doesn't add a "ServicePack" or "Edition" column to the table for you.

You can override this by specifying the column names with something like this:

PS> Invoke-Sqlcmd -ServerInstance $server -Query $sql | format-table Version, ServicePack, Edition

Version      ServicePack Edition
-------      ----------- -------
15.0.2070.41
             RTM         Developer Edition (64-bit)


PS> _

Note there's only 2 blank lines after the table now because the second result has de-cloaked.

If you output the two results separately inside a loop, PowerShell will show two separate tables and will decide what columns to show in each table separately, so you get this, per @Dan Guzman's answer:

Version    
-------    
15.0.4023.6



ServicePack Edition                   
----------- -------                   
RTM         Developer Edition (64-bit)

Upvotes: 7

Dan Guzman
Dan Guzman

Reputation: 46203

One method is to iterate over the results of Invoke-Sqlcmd to show the multiple result sets:

$results = Invoke-Sqlcmd -ServerInstance $server -Query $sql
for ($i = 0; $i -lt $results.Count; ++$i) {
    $results[$i] | Out-Host
}

Output:

Version    
-------    
15.0.4023.6



ServicePack Edition                   
----------- -------                   
RTM         Developer Edition (64-bit)

Upvotes: 5

Related Questions