Duplicate column names are not permitted in SQL PowerShell

PowerShell Help
1

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
2

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/