topic How to avoid SPARSE Columns for proper replication in Qlik Replicate

How to avoid SPARSE Columns for proper replication

Konec — Wed, 07 Jun 2023 10:45:10 GMT

Hello,

I still have an error: "Table 'XY' contains unsupported sparse columns and will therefore be ignored." but it has impact on whole table? It means in case that table which I am ingesting have those sparse column I am not able to processing deltas?

I try to disabled those sparse column on DB side (in Articles), I also try change settings in Qlick on that table - table settings > Transform > remove those columns from output.

But non of this helps, after full load is done and Change processing is running i still have the same error.

Any idea, please?

Re: How to avoid SPARSE Columns for proper replication

john_wang — Wed, 07 Jun 2023 14:41:27 GMT

Hello @Konec ,

Thanks for opening the article.

Not sure if you are meaning SQL Server source endpoint. For SQL Server source, the SPARSE Columns are not supported in "Microsoft SQL Server" source endpoint CDC mode, neither in "Microsoft SQL Server (MS-CDC)" endpoint under CDC mode. It's supported under Full Load mode only.

We have an open FR article, you may vote for this feature.

Hope this helps.

Regards,

John.

Re: How to avoid SPARSE Columns for proper replication

Konec — Wed, 07 Jun 2023 17:45:45 GMT

Oh sry, yes MSSQL. It means that i am not albe to do CDC on whole table, in case that there is only one sparse column? Other columns in the table are not sparse. I can’t exclude sparse column any way?

Re: How to avoid SPARSE Columns for proper replication

Alan_Wang — Wed, 07 Jun 2023 18:38:58 GMT

Hi @Konec

We're discussing this internally and sparse columns may work with MS-CDC although it is listed in the limitations. It's been tested and working by another user with May 2022 and Nov 2022 versions of Replicate.

MS-CDC can be set up in your SQL Server and enabled on the database and table. In the Replicate task SQL Server endpoint, the internal parameter isMSCDCMode should be checked to turn on MS-CDC mode. A task can't resume interchangeably between transaction logs and MS-CDC logs so it is recommended to test this with a new task and endpoint to see if it works before implementing on the prod environment.

CC: @john_wang

Re: How to avoid SPARSE Columns for proper replication

john_wang — Thu, 08 Jun 2023 01:13:38 GMT

Hello @Konec , copy @Alan_Wang ,

Thank you so much Alan for the clarification. And this is my concern as well, because from MS SQL Server docs, Change data capture supports sparse columns, so MS-CDC is the best bet. I'm sorry I did not verify the functionality prior to replying to the article.

Regards,

John.

Re: How to avoid SPARSE Columns for proper replication

Konec — Thu, 08 Jun 2023 07:35:47 GMT

Hi,

we have version 6.6.0.790 and it seems it´s not supported in that version? I am using CDC mode for other tables where it is working, but the other tables doesn´t have any sparse columns.

Re: How to avoid SPARSE Columns for proper replication

Alan_Wang — Thu, 08 Jun 2023 13:45:38 GMT

Hi @Konec

It's been tested with May and Nov 2022 versions of Replicate only. Other version behaviors may differ.

Re: How to avoid SPARSE Columns for proper replication

Konec — Thu, 08 Jun 2023 14:09:28 GMT

Well, if I have this error "Table 'XY' contains unsupported sparse columns and will therefore be ignored." i have probably different version then you mentioned. It means that I am not able to do MS-CDC on tables which have sparse columns and I can't ignore sparse columns in any way and do CDC on the rest of the table columns?

Only way is to drop those sparse columns directly on the table?

Re: How to avoid SPARSE Columns for proper replication

Alan_Wang — Thu, 08 Jun 2023 16:09:26 GMT

There isn't a way to ignore just sparse columns. Dropping those columns on the source would work. I suggest enabling MS-CDC on the table and trying to load them with a different task that is set to use MS-CDC mode. It may work even though you are getting errors.