Author Posts

April 8, 2016 at 3:44 am

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

April 8, 2016 at 6:28 am

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/