sql-serverpowershellinvoke-sqlcmd

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


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 

enter image description here

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:

enter image description here


Solution

  • 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.

    enter image description here