Replicated tables and Multiple Indexs

Aug 21, 2013 at 3:45 AM
Hi All

I was getting the

"Msg 50000, Level 16, State 3, Procedure sp_StandardCDC_enable_table, Line 364
Error on line 1: Column names in each table must be unique. Column name 'COLUMN' in table 'TABLE' is specified more than once."

error when trying to enable StandardCDC on a replicated table.

Essentially the code that extracts the source table metadata to create the tracking table was creating too many combinations and failing. The fix for a similar issue found in discussions did not work for me.

The re-worked the code is below: It should handle multiple indexes and replicated (subscribed)tables the previous code is commented out between the / / (sorry about the formatting)
            --Get column meta data details for creation of tracking table and trigger
            INSERT @SourceTableDetail (columnName, datatype, nullSpecification, ColumnID, columnRowNumber, isPrimaryKey)
                SELECT TQ.columName 
                    ,CASE
                        WHEN TQ.dataTypeName = 'datetimeoffset' THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.scale) + ')'
                        WHEN TQ.dataTypeName IN ('decimal', 'numeric') THEN TQ.dataTypeName + ' (' + CONVERT(varchar(10), TQ.[precision]) + ', ' + CONVERT(varchar(10), TQ.scale) + ')'
                        WHEN TQ.dataTypeName IN('nchar', 'nvarchar') AND TQ.max_length >= 2 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length/2) + ')'
                        WHEN TQ.dataTypeName = 'varchar' AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')'
                        WHEN TQ.dataTypeName IN('nvarchar', 'varchar', 'varbinary') AND TQ.max_length = -1 THEN TQ.dataTypeName + '(max)'
                        ELSE TQ.dataTypeName
                    END AS dataType
                    ,TQ.nullSpecification
                    ,TQ.column_id
                    ,ROW_NUMBER() OVER (ORDER BY TQ.column_id ASC)
                    ,TQ.isPrimaryKey
                FROM(
                /**
                SELECT c.name AS columName
                    ,t.name AS dataTypeName
                    ,c.max_length
                    ,c.[precision]
                    ,c.scale
                    ,CASE 
                        WHEN c.is_nullable = 0 THEN 'NOT NULL'
                        ELSE 'NULL'
                    END AS nullSpecification
                    ,c.column_id
                    ,CASE
                        WHEN c.column_id = ic.column_id THEN 1
                        ELSE 0
                    END AS isPrimaryKey                 
                FROM sys.columns AS c
                    INNER JOIN sys.objects AS o ON o.[object_id] = c.[object_id]
                    INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id
                    LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id] 
                    LEFT JOIN sys.index_columns AS ic ON ic.[object_id] = c.[object_id] 
                        AND ic.column_id = c.column_id                  
                        AND ic.index_id = i.index_id
                        AND i.is_primary_key = 1
                WHERE o.name = @SourceName
                    AND SCHEMA_NAME(o.[schema_id]) = @SourceSchema
                    AND t.name NOT IN('sysname', 'timestamp')
                    AND (c.name IN(SELECT cc.Element FROM utility.SplitArray(@CapturedColumnList, ',') AS cc) OR @CapturedColumnList IS NULL)
                    )AS TQ
                ORDER BY TQ.column_id; 
                **/
                SELECT  ac.name AS columName, 
                    ty.name AS datatypeName, 
                    ac.max_length,
                    ac.precision,
                    ac.scale, 
                    CASE WHEN ac.is_nullable = 0 
                        THEN 'NOT NULL' 
                        ELSE 'NULL'
                    END AS nullSpecification,
                    ac.column_id, 
                    CASE WHEN ac.[column_id] = IC.[column_id] 
                        THEN 1 
                        ELSE 0
                    END AS isPrimaryKey
                FROM    sys.tables AS t 
                    INNER JOIN sys.all_columns AS ac ON t.object_id = ac.object_id 
                    INNER JOIN sys.types AS ty ON ac.system_type_id = ty.system_type_id AND ac.user_type_id = ty.user_type_id 
                    LEFT OUTER JOIN sys.index_columns AS ic ON ic.object_id = ac.object_id
                WHERE     (t.is_ms_shipped = 0) 
                    AND (OBJECT_SCHEMA_NAME(t.object_id, DB_ID()) = @SourceSchema) 
                    AND (t.name = @SourceName)
                    AND (ac.name IN(SELECT cc.Element FROM utility.SplitArray(@CapturedColumnList, ',') AS cc) OR @CapturedColumnList IS NULL)
                    ) as TQ
                ORDER BY tq.column_id;