Suggest an Idea

[SandilyaDongar]

Hi,

Source: SQL Server, Target: Snowflake on Azure

We're trying to remove case-sensitivity issue on our Target and ignore double-quotes while creating objects from Replicate. This requires session parameter 'QUOTED_IDENTIFIERS_IGNORE_CASE' needs to be set to 'true' within Snowflake while creating objects to ignore double-quotes for case-insensitivity.

But when we've changed the logs to Verbose in Replicate and loaded the data. Seems Replicate is defaulting the parameter 'QUOTED_IDENTIFIERS_IGNORE_CASE' to 'False'. Is there anyway to override this to 'True' on the fly? If so, that might resolve our issue.

Log where Replicate is forcing as false:

'00000796: 2020-11-10T12:41:18:57578 [TARGET_LOAD   ]V: Execute immediate: 'alter session set QUOTED_IDENTIFIERS_IGNORE_CASE=FALSE' (ar_odbc_stmt.c:4811)'

Thanks,

Sandilya Dongar.

[Meghann_MacDonald]

Hi there, we will put this through Ideation Review. Please keep in mind that this is not a place to submit bugs, and we will not be able to solve individual errors or problems, just consider implementing new ideas and features.

Meghann

[EyalSilner]

Hi Sandilya,

There is currently no way to override the QUOTED_IDENTIFIERS_IGNORE_CASE to 'True' on the fly.

This is by Qlik Replicate design.

Are you having case-sensitivity issues due to Qlik Replicate?

Could you please help us understand what is your main reason for ignoring the case-sensitivity issue on your target, other than solving the problem you are facing?

Would your preferences have been different if you had not had to deal with these issues?

Thanks,

Eyal.

[SandilyaDongar]

Hi Eyal,

Our source is SQL server which has camel-case objects (all permutations possible) and our target is Snowflake which is case-sensitive by default unless all the objects are defined in uppercase or creating objects without double-quotes. So we are trying to convert all objects to uppercase through Replicate using global transformations (we are also using datatype conversions on the columns in global transformations as well).

But since, Replicate is unable to handle multiple-global transformations on same columns/tables (both datatype & uppercase conversions), we are forced to make Snowflake case-insensitive instead of converting to uppercase by using 'QUOTED_IDENTIFIERS_IGNORE_CASE ' param at the session level. But Replicate is even setting that param to false and there is no way for us to update it to true to make Snowflake case-insensitive. 

We did also checked with Snowflake support and they replied that since Replicate is explicitly setting ‘quoted_ignore_identifiers_case’ param to ‘false’ at session-level which overrides the settings done at the database level in snowflake, they suggested to check with Replicate only and there is no workaround even from the ODBC driver run time level.

So we are kind of stuck in a deadlock with below things which can't be changed on the fly when using Replicate:

• Inserting double-quotes for every object creation behind the scenes (which can't be removed)
• Setting the session paramerter 'quoted_identifiers_ignore_case' to 'false' (which can't be updated to 'true')
• Not allowing multiple-global transformations to apply on the same columns/tables (so unable to use both datatype & uppercase conversions for same columns)

If Replicate can provide a workaround for any of the above 3 things, we might be able to resolve our issue with case-sensitivity or uppercase conversion.

Thank you,

Sandilya D.

[EyalSilner]

Hi Sandilya,

Thank you for the detailed response. We can now better understand the problem/ deadlock you face. 

We work to find the best solution for you and others, and prevent the deadlock potential. 

We are considering some solutions at the moment, and we will get back to you as soon as possible with a finally chosen approach that will solve your issue one way or another.

Regards,

Eyal.

[SandilyaDongar]

Hi Eyal,

Thanks a lot for your response. Please keep me posted on the status of solution for this problem.

Thank you,

Sandilya D.

[spachunuri]

We are also struck in the same situation where we are trying to fix the issue.

[Shelley_Brennan]

In order to address this issue, we will be releasing two improvements in the 2021.5 May release (technical preview will be available in April):

First, we will expose an internal parameter to control QUOTED_IDENTIFIERS_IGNORE_CASE parameter.  Once this been released, you may work with Support for instructions on how to implement.

Second, we will also be enhancing global transformations to allow for multiple transformations on the same columns/tables.

[SandilyaDongar]

Thats great! This should resolve the issue we are currently facing.

Thanks a lot Shelley.

[Shelley_Brennan]

With the May 2021 release, you'll now be able to control QUOTED_IDENTIFIERS_IGNORE_CASE for Snowflake targets.  Thanks!

[SandilyaDongar]

Thank you. Was able to test it out and was able to control the parameter from Replicate.