Invoke-SQLCmd minor issue running some SQL Stored-Procedures…

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.
At line:1 char:24
+ $sqlWho = Invoke-Sqlcmd <<<<  “SP_Who2”
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], DuplicateNameException
    + FullyQualifiedErrorId : SqlServerError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

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!!

%d bloggers like this: