SQL Compilation Error with built-in Snowflake connector for case-sensitive Database Names
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.