3
Vote

sp_standardCDC_enable_table Error when table has more than one indexes

description

Error on line 1: Column names in each table must be unique.When the table that the sp_standardCDC_enable_table is running for, has more than one indexes (including pk's clustered index), it multiplies the columns with the indexes. I think the problem is at the last two left outer joins. The condition 'AND i.is_primary_key = 1' has to be at the sys.indexes join instead of sys.index_columns.I changed this and worked as expected.LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id] AND i.is_primary_key = 1 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

file attachments

comments