Invoke-Sqlcmd : Duplicate column names are not permitted in SQL PowerShell

Question

I've spitted Glenn Berry's Performance Query in 14 queries but one of them (number 9) is not working.

This is my PowerShell code:

#Provide SQLServerName
$SQLServer ="localhost"
#Provide Database Name 
$DatabaseName ="master"
#Prompt for user credentials 
$credential = Get-Credential 

$Query1= "-- 09 Index Sizes
-- Note: THIS IS SLOW as it reads index blocks. SAMPLED is not that high, but watch for prod I/O impact if using 'DETAILED'
SELECT DB_NAME() AS DatabaseName,
 Object_name(i.object_id) AS TableName
   ,i.index_id, name AS IndexName
   ,i.type_desc
   ,ips.page_count, ips.compressed_page_count
   ,CAST(ips.avg_fragmentation_in_percent as DECIMAL(5,1)) [fragmentation_pct]
   ,CAST(ips.avg_page_space_used_in_percent as DECIMAL(5,1)) [page_space_used_pct]
   ,ips.index_depth, ips.page_count ,ips.forwarded_record_count, ips.record_count
  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips -- or SAMPLED
    INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
where ips.page_count > 1
ORDER BY ips.record_count desc;"

Invoke-Sqlcmd  -Database $DatabaseName -ServerInstance $Server -Credential $credential -Query $Query1 | Format-Table

The error returned says:

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 C:\Users\FrancescoM\Desktop\CSV\Test.ps1:23 char:1
+ Invoke-Sqlcmd  -Database $DatabaseName -ServerInstance $Server -Crede ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : SyntaxError: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : DuplicateColumnNameErrorMessage,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Not sure what is the duplicate column because if I run the query called into the PowerShell script on SSMS I don't see any duplicate column:

I think this shouldn't be closed: I don't see any other post on StackOverflow talking about the error: `Invoke-Sqlcmd : Duplicate column names are not permitted in SQL PowerShell.` — Francesco Mantovani, Aug 07 '18 at 02:58

score 0 · Accepted Answer · answered Aug 07 '18 at 09:46

0

Arrived at home I formatted the query in a decent way and thanks to Notepad++, after clicking on each column I found out that ips.page_count was called twice.

So there was indeed a column called twice.

answered Aug 07 '18 at 09:46

Francesco Mantovani

10,216
13
73
113

score 0 · Answer 2 · answered Jul 06 '20 at 10:53

0

Use the following parameter for the Invoke-Sqlcmd command.

-OutputSqlErrors $False

It will suppress the error.

Documentation: https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

answered Jul 06 '20 at 10:53

avadhut007

332
3
16

score 0 · Answer 3 · answered Aug 24 '20 at 08:00

You get this error when you have duplicate column names in resultset of the query. invoke-sqlcmd : Duplicate column names are not permitted in SQL PowerShell

Suppose you used below query as to see result-set in your invoke-sqlcmd query-

select test, * from testtable

Now the test column will be duplicate in the result and invoke sqlcmd execution will fail.

To Resolve this use like this- select test as testColumn, * from testtable

Invoke-Sqlcmd : Duplicate column names are not permitted in SQL PowerShell

3 Answers3