Duplicate column names are not permitted in SQL PowerShell

Tagged: 

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Richard Diphoorn Richard Diphoorn 7 months, 4 weeks ago.

  • Author
    Posts
  • #37406
    Profile photo of Kir K
    Kir K
    Participant

    Folks, I have an issue with SQLcmd query which can be successfully executed in SQL, but fails with "Duplicate column names are not permitted in SQL PowerShell. To repeat a column, use a column alias for the duplicate column in the format Column_Name AS New_Name." when I use it as a parameter with -query:

    Invoke-Sqlcmd -ServerInstance "Server\Instance" -Query "USE DB
    select tf.id, tf.directory_id, tf.name, tm.name
    from [Tape.backup_sets] as tbs
    inner join
    [Tape.tape_medium_backup_sets] tmbs
    on tmbs.backup_set_id = tbs.id
    inner join
    [Tape.file_parts] tfp
    on tfp.media_sequence_number = 1
    inner join [Tape.file_versions] tfv
    on tfv.backup_set_id = tbs.id and tfp.file_version_id = tfv.id
    inner join [Tape.files] tf
    on tf.id = tfv.file_id
    inner join [Tape.tape_mediums] tm
    on tm.id = tmbs.tape_medium_id
    where tm.name = 'T90009L6'" (/code>

    Invoke-Sqlcmd : Duplicate column names are not permitted in SQL PowerShell. To repeat a column, use a column alias for the duplicate column in the format Column_Name AS New_Name.
    At line:5 char:1
    + Invoke-Sqlcmd -ServerInstance "$($a.SqlServerName)\$($b.SQLInstanceName)" -Query ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : SyntaxError: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : DuplicateColumnNameErrorMessage,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

  • #37427
    Profile photo of Richard Diphoorn
    Richard Diphoorn
    Participant

    As Invoke-SqlCmd is built for PowerShell, and therefore returns multiple objects, it can't handle the duplicate column names. Source: https://msdn.microsoft.com/en-us/library/cc281720.aspx
    Maybe you can look into SMO as a replacement for Invoke-SqlCmd: https://blog.netnerds.net/smo-recipes/

You must be logged in to reply to this topic.