SQL Compilation Error with built-in Snowflake connector for case-sensitive Database Names
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
- show objects /* ODBC:TableMetadataSource */in account
- use ACCEL_V3
- 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.