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.
I do not have experiences with SQL but if it is what I think it is this is a common behaviour of PowerShell. It returns a result just not as an array. When a query returns more than one element PowerShell creates an array implicitly. When there’s only one element PowerShell returns it as it is. So in all cases I remember the solution was to cast the result to an array.
Try this:
Hi Olaf,
Many thanks. Yes, this works with less code changes on my side. From my understanding the invoke-sqlcmd, like i am using it, returns a .net datatable/datarow object. I also found another way that worked as well. It was using the -AS Datatables option. As shown here. $results = Invoke-Sqlcmd -ServerInstance $MainSQLServerNameConnection -Database $Maindbname -Username $IN_SQLUserName -Password $IN_SQLPassword -Query $IN_SQLQuery -As DataTables -QueryTimeout 600 -AbortOnError
The -AS datatable approach requires me to do a bit more changes to make it work in my Excel processing - not many - but a bit more. Regardless, it worked as well. I will likley stay with your Array appraoch.
Thanks
MG
Is it realy that hard to format your code as code? It is that simple: Place your cursor on an empty line, click the preformatted text button ( </> ) and paste your code … that’s all. Please.
