June 20, 2019 at 1:08 pm
Hi,
I am running below 3 Inovke-SQLCmd satatements on a powershell window.
Invoke-Sqlcmd -Query "select servicename,startup_type_desc,status_desc from sys.dm_server_services;" -Server "." -IncludeSqlUserErrors
Invoke-Sqlcmd -Query "select count(1) as 'Total Online Count' from sys.databases where state_desc = 'ONLINE';" -Server "."
Invoke-Sqlcmd -Query "select count(1) as 'Not Online Count' from sys.databases where state_desc != 'ONLINE';" -Server "."
I am expecting and output from all 3, but only the first one gives me the data. Below is what I get as output.
PS SQLSERVER:\> Invoke-Sqlcmd "select servicename,startup_type_desc,status_desc from sys.dm_server_services;" -Server "." -IncludeSqlUserErrors
Invoke-Sqlcmd "select count(1) as 'Total Online Count' from sys.databases where state_desc = 'ONLINE';" -Server "."
Invoke-Sqlcmd -Query "select count(1) as 'Not Online Count' from sys.databases where state_desc != 'ONLINE';" -Server "."
servicename startup_type_desc status_desc
----------- ----------------- -----------
SQL Server (MSSQLSERVER) Automatic Running
SQL Server Agent (MSSQLSERVER) Automatic Running
SQL Full-text Filter Daemon Launcher (MSSQLSERVER) Manual Running
The commands give me output when running one at a time but not when all 3 are executed together. What am I doing wrong?
June 20, 2019 at 9:39 pm
Invoke-SQLCmd can have issues with multiple results and you end up with just the one. There are a few different ways to work around it with Invoke-SQLCmd. One of the easier ways is to set the results to a variable and then output those variables.
$results1 = Invoke-Sqlcmd -Query "select servicename,startup_type_desc,status_desc from sys.dm_server_services;" -Server "." -IncludeSqlUserErrors
$results2 = Invoke-Sqlcmd -Query "select count(1) as 'Total Online Count' from sys.databases where state_desc = 'ONLINE';" -Server "."
$results3 = Invoke-Sqlcmd -Query "select count(1) as 'Not Online Count' from sys.databases where state_desc != 'ONLINE';" -Server "."
Format-Table -inputobject $results1
Format-Table -inputobject $results2
Format-Table -inputobject $results3
Sue
June 21, 2019 at 5:42 pm
Although I am still unclear on why this would happen, that fixed the issue.
Thank You Sue
June 21, 2019 at 7:12 pm
I'm not that well versed at Powershell but it's my understanding that it's mostly because in Powershell it's returning objects so it's not like just running a few sqlcmd statements. If you run more than one, the columns need to be the same and then it appends the rows. Not like this example makes sense but if you run the last two of your commands and change the column names so they match, you'd get the two results together. So if you changed it like this, you'll see what I mean:
Invoke-Sqlcmd -Query "select count(1) as 'Total Online Count' from sys.databases where state_desc = 'ONLINE';" -Server "."
Invoke-Sqlcmd -Query "select count(1) as 'Total Online Count' from sys.databases where state_desc != 'ONLINE';" -Server "."
So multiple invoke-sqlcmd is kind of like a union query in a way. If you set the results of each to its own variable, then you just display those variables - like with the Results1, Results2, Results3. The behavior with multiple executions is explained in the docs but I remember hitting the issue and that explanation didn't make much sense initially. But the other executions would need to be able to append to the output object - System.Data.Datarow. Read the part in the documentation just before the examples which explains the multiple executions of Invoke-Sqlcmd:
As it says in the documentation: If subsequent result sets contain different column lists than the first, those result sets are not displayed.
Sue
June 23, 2019 at 12:31 pm
Thanks Sue. It all makes sense now. Appreciate your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply