0

I am working on a project where I have to use CDC, catalog collation SQL_Latin1_General_CP1_CI_AS and a CS collation for data. I get an collation conflict error from sp_cdc_create_populate_stored_procs when enabling cdc for any table. This is called from other procedure "sys.sp_cdc_enable_table_internal" and again this is call from my own sql-script "sys.sp_cdc_enable_table". To me this seems like a bug but would appreciate if there's any new ideas how to implement production level workaround on this matter.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Janih
  • 9
  • 3
  • Hi! Could you please show us the query you already tried, and the error code you had? – toshiro92 Feb 16 '20 at 18:38
  • Sure. msg: Msg 22832, Level 16, State 1, Procedure sys.sp_cdc_enable_table_internal, Line 673 [Batch Start Line 7]Could not update the metadata that indicates table [schema].[database] is enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_create_populate_stored_procs'. The error returned was 468: 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.'. Use the action and error to determine the cause of the failure and resubmit the request. Will send more tomorrow. – Janih Feb 16 '20 at 22:21

1 Answers1

0

This is a documented limitation of Contained Databases.

Limitations

Partially contained databases do not allow the following features.

-Partially contained databases cannot use replication, change data capture, or change tracking.

-Numbered procedures

-Schema-bound objects that depend on built-in functions with collation changes

-Binding change resulting from collation changes, including references to objects, columns, symbols, or types.

-Replication, change data capture, and change tracking.

Contained Databases - Limitations

If you need a case-insensitive catalog collation along with a case-sensitive collation for data, you can set the column collation directly on your text columns instead of using the database's default collation.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks David, I am not using contained databases. Unless using WITH CATALOG_COLLATION somehow makes it one. It's on prem SQL Server where contained mode is set to False. I was aware that contained databases have this limitation but I was not able to find anywhere actual documentation of the WITH CATALOG_COLLATION option. Any docs You would be able to send? – Janih Feb 16 '20 at 22:34
  • Contained Databases are the only way to get catalog collation on-prem. Azure SQL Database has the standard catalog collation for all databases. – David Browne - Microsoft Feb 16 '20 at 22:39
  • Ok, your view on contained database was news to me. I earlier specifically decided not use contained databases due to the lack of CDC. And now I found myself having started implementation with it. Would adding column level CS collation have performance hit in case I have relatively large system I'm dealing with? Or are the any other possible known risks / pitfalls using column level collation widely? Probably best implementation method is to use cursor for each varchar/char column.. – Janih Feb 16 '20 at 22:52
  • No need to use a cursor. The biggest risks are the mismatch between the instance collation and the column collation that can be inconvenient or surprising, and you'll need to pay attention to. – David Browne - Microsoft Feb 16 '20 at 22:55
  • Thanks David! Will give it a go and hopefully I'll get CDC working again. Would have really appreciated more thorough documentation regarding WITH CATALOG_COLLATION - still seems to be missing from https://learn.microsoft.com/ – Janih Feb 16 '20 at 23:06