Quirky Invoke-Sqlcmd?

Published 25 January 11 12:48 PM | MartinBell

One thing I was going to mention in my last post on Invoke-Sqlcmd was the fact that you need to return unique column names. This is not mentioned in Books online and I’ve not seen it anywhere else. For example, if you run the following Powershell script:

# load snap-in if not in SQLPS
Add-PSSnapin SqlServerCmdletSnapin100

Invoke-Sqlcmd -server localhost -database Adventureworks -query "
    SELECT t.object_id AS tableid
        , t.name
        , c.object_id AS columnId
        , c.name
    FROM sys.tables t
    JOIN sys.columns c ON c.object_id = t.object_id
    WHERE t.name = 'Employee'
    " ;

You will get the following errors:

Invoke-Sqlcmd : The pipeline has been stopped.
At line:3 char:14
+ Invoke-Sqlcmd <<<<  -server localhost -database Adventureworks -query "
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Invoke-Sqlcmd : A column named 'name' already belongs to this DataTable.
At line:3 char:14
+ Invoke-Sqlcmd <<<<  -server localhost -database Adventureworks -query "
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], DuplicateNameException
    + FullyQualifiedErrorId : SqlServerError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

At first this may seem quite surprising, but if you remember that the cmdlet returns a datatable and datarow objects and each “column” is an attribute of the object, then it is sensible that you can’t have two attributes with the same name.

This can be an issue when running some of the system stored procedures e.g sp_who2 as described by Max Trinidad who creates his own version of the procedure to overcome it. Another solution to this would be to use a temporary table with uniquely named columns and INSERT…EXEC to get the output of the table. This could be an issue if you try to run your existing scripts or procedures using Invoke-Sqlcmd so make sure you test them first!!

Filed under:


No Comments

This Blog

SQL Blogs