Fellow Powershellers, I have run into something that leaves me speechless and confused.
I am a DBA and have been writing SQL queries and sps for over 25 years. What I have come across is what seems to be how invoke-sqlcmd is handling the output.
I have a simple sp that returns rows. Works perfectly. Then I hit a condition where only ONE row is returned. invoke-sqlcmd would return NOTHING. So, I took the sql and did a Select TOP 2 … in the query and got results. So, when only one row is returned, invoke-sqlcmd is not populating the output object.
I put my query in the $IN_SQLQuery variable.
$results = Invoke-Sqlcmd -ServerInstance $MainSQLServerNameConnection -Database $Maindbname -Username $IN_SQLUserName -Password $IN_SQLPassword -Query $IN_SQLQuery -QueryTimeout 600 -AbortOnError
The counts from $results is NOTHING when only one row comes out of the sql. It does not matter whether it is from a sp or t-sql directly.
I really would like to get this to work - conveting over to ado is going to take more time than I want to give it.