SQL Compilation Error with built-in Snowflake connector for case-sensitive Database Names

[jmorgenstern]

We are connecting to Snowflake using built-in Snowflake connector with PowerBI Desktop 2019, version March 2022.

When we add Snwoflake as a new source using the built-in connector, connection is established successfuly. If databases with case sensitive names exist, PowerBI is not able to access these databases. It returns "ODBC: Error 42000 SQL compilation error...". 

SQL compilation error for case-sensitive databases

The same issue appears in existing reports.

However, the code generated by PowerBI is valid.

let
    Source = Snowflake.Databases("mysnowflakeaccount.snowflakecomputing.com","""103_WH_Stage_MY_WH"""),
    #"103_Stage_MY_DB_Database" = Source{[Name="103_Stage_MY_DB",Kind="Database"]}[Data],
    MY_SCHEMA_Schema = #"103_Stage_MY_DB_Database"{[Name="MY_SCHEMA",Kind="Schema"]}[Data],
    MY_TABLE_Table = MY_SCHEMA_Schema{[Name="MY_TABLE",Kind="Table"]}[Data]
in
    MY_TABLE_Table

This issue first occures with PowerBI March 2022 (version 2.103.661.0). Older versions do not seem to have this problem (February 2022 and December 2021 were tested).

Workarounds successfully tested:

• Use non-case-sesitive names for databases
• Use the official ODBC driver provided by Snowflake instead of the internal Snowflake connector

We could not find a final solution for the internal Snowflake connector. Are there any ideas, please?

[dszmolka]

TRACE level ODBC logs suggest that while earlier 'show objects in account' was used by Power BI Desktop (e.g. in February 2022 release for sure) to list objects:

2022-03-22T17:37:32.259 TRACE 1009 Simba:❄️:Statement::executeInternal: Entering executing query: show objects /* ODBC:TableMetadataSource */in account

thus it could not trip up any case-sensitiveness issue. Now in March 2022 release a different strategy seems to be used, which fails when database name is case-sensitive.

It's a bit strange that PowerBI however correctly issues the double-quoted version of the database name for getting the schemas, thus doesn't fail:

2022-03-22T16:53:36.926 TRACE 9016 Simba:❄️:Statement::executeInternal: Entering executing query: use 103_Stage_MY_DB
..
2022-03-22T16:53:37.029 ERROR 9016 Simba:❄️:Statement::executeInternal: Failed on executing query: use 103_Stage_MY_DB, queryid: <queryid>, code: -1, gsCode: 1003, SqlState: 42000, message: SQL compilation error:
syntax error line 1 at position 4 unexpected '103'.

..
2022-03-22T16:53:39.820 TRACE 9016 Simba:❄️:Statement::executeInternal: Entering executing query: show schemas in database "103_Stage_MY_DB"
..
2022-03-22T16:53:39.906 INFO 9016 Simba:❄️:Statement::executeInternal: Query with Query ID <queryid> ends and results parsed for: show schemas in database "103_Stage_MY_DB"

Also strangely the 'old' strategy of 'show objects in account' is used, when the 'ODBC' type data source (with the exact same 2.24.3 standalone version of Snowflake ODBC driver, which March 2022 release has) is used in place of the 'Snowflake' type one, thus again getting the objects is successful. (This might be less performant though when there are a lot of objects in the account)

Appreciate if someone could look into this issue, potentially affecting everyone who wants to use the current-latest version of Power BI Desktop with the built-in Snowflake driver.

[Anonymous]

Hi @jmorgenstern 

Based on the above information, if you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: https://powerbi.microsoft.com/en-us/support/

For how to create a support ticket, please refer to https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

Best Regards

Community Support Team _ Ailsa Tao

[dszmolka]

hi @Anonymous 

Thanks for picking this up so quickly !

Root cause of this issue can be seen in my above post. The above user contacted us for the same issue, but as you can see there's not much we can do on our end, since it's you guys who're sending a malformed query.

Appreciate if it would be possible to pass this on to the devs, because it is not an individual isolated issue.

It really affects all of your users who:
* use the latest Power BI Desktop (version 2.103.661.0, released on March 17)

* want to connect to Snowflake with the built-in ODBC driver

* have at least 1 Case_Sensitive database name in it

Thank you very much for your continued support on this !

Best regards,

David | Snowflake Support

[DavidReis0420]

We have been dealing with something very similar. Our issue is occurring on our Power BI Service capacities, not Power BI Desktop. Probably because we are still using the February 2022 version of Desktop.

Our issue started the morning of 3/15. We are using Direct Query connection to Ssnowflake. Every visual errored out with the following error: “DataSource.Error: ODBC:ERROR [02000] SQL compilation error: Object does not exist or operation cannot be performed.”  Using the Snowflake query history, we were able to trace the error to this query being issued by the Power BI Service:

use Accel_V3

Accel_V3 is the correct database name. However, Snowflake usually requires that object names be surrounded by double quotes.

We could see no instances of the “use [database]” command with or without double quotes ever being executed by Power BI prior to 3/15.

With the help of a Snowflake support engineer, we figured out we could patch the issue by renaming the database with all caps in Snowflake and updating the PBI Service connection parameters so the db name was also all caps.

Now, in the Snowflake query history, we see the following commands being executed for every visual

1. show objects /* ODBC:TableMetadataSource */in account
2. use ACCEL_V3
3. use

The third command is invalid and fails, but Power BI seemed to be able to recover this time and executed different metadata queries after the "use" statement failed. We see this sequence over and over in the Snowflake query history.

As dszmolka stated, the "show objects in account" can take a while to run depending on how many objects the account has permissions to. So we also pared down the access for the account to the bare minimum.

We did open a support ticket with Microsoft, but they haven't been much help. The first week, they kept suggesting the issue was somethign that requred Snowflake to fix. The latest from them is that the planned April release of Power BI Desktop should speed up metadata queries to Snowflake. But they haven't acknowledged a change was released for PBI Service that caused this, only that the Snowflake connector has always had performance issues that will be addressed in April.

[jmorgenstern]

Sorry for the delay.

In the meantime we opened a support ticket. The Microsft support states that the product team is working with the driver vendor on a solution. An ETA for a solution is not yet known. I'll keep you posted.

[stuartcoggins]

Just to add that this still appears to be an issue in the April 2022 release of Power BI Desktop. I hope it will be fixed in the forthcoming May 2022 release!

[stuartcoggins]

Hi again, still appears to be an issue for me in the May 2022 release.

Can anyone from Microsoft update on the potential timeline for this to be fixed?

Many thanks,

Stuart

[kfanalytics]

Hi folks,

The issue still exists for databases with case sensitive enabled in Snowflake.  We cannot remove case-sensitive constraint in Snowflake.

Only fix seems possible is from Power BI only. Do we have any tentative timeline?

[dszmolka]

This seems to be now fixed, according to the Release Notes of the June 2022 version.

[jmorgenstern]

Hi together,

this issue still exists. We cannot confirm a resolution in the June 2022 version. We'll reach out to them again to see if there is a timeline.