First time I notice something strange with “Invoke-SQLCmd”, I was when executing the system Stored-Procedure “SP_Who2” and got the following error message:
| PS C:\Users\Max> $sqlWho = Invoke-Sqlcmd “SP_Who2” Invoke-Sqlcmd : The pipeline has been stopped. At line:1 char:24 + $sqlWho = Invoke-Sqlcmd <<<< “SP_Who2” + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand Invoke-Sqlcmd : A column named ‘SPID’ already belongs to this DataTable. PS C:\Users\Max> |
I was trying to create a collection of object with the results from my “SP_Who2” and got the error stating “.. A column named ‘SPID’ already belongs to this DataTable..”. So, I end up scripting out the SP_Who2, finding there are two columns with the same name ‘SPID’ and PowerShell didn’t like it. I made the change to rename one of the columns to be SPID2 and save the T-SQL script to my PowerShell script file.
And, the next time the ran my “Invoke-SQLCmd”, I had no problems and got my results so I could manipulate my .NET objects.
So, this is to make you aware that you will experience this minor issues when executing some of the SQL system stored-procedures. And, for those who wonder… is this a BUG?? I really don’t think so!! Because, the issue is in some of the system stored-procedures. Should I submit this issue to the SQL Team to fix all stored-procedures generating columns with the same name? This could be a major and unnecessary task. Anyway, you were served!!
Happy PowerShelling!!