Simple Query using DECLARE with Azure Databricks and Native Query errors, bug?

[AxiomaticOne]

We are converting data source from Synapse to Databricks. So I'm just tranlating the SQL code and moving over. Came across a problem today where the following code runs just fine in Databricks using the same serverless compute but when I try to insert that into Power Query I get an error.

Does Power BI not support DECLARE?

Power Query M Code:

let
Source = Value.NativeQuery(Databricks.Catalogs(Server_Hostname, HTTP_Path, [Catalog=Catalog, Database=null, EnableAutomaticProxyDiscovery=null]){[Name=Catalog,Kind="Database"]}[Data],
"
DECLARE xx INT = 2;
SELECT xx;
", null, [EnableFolding=true])
in
Source

- Note: connection string is paramaterized and "SELECT 2;" will run.

Partial error message:

DataSource.Error: ODBC: ERROR [42601] [Microsoft][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: [PARSE_SYNTAX_ERROR] org.apache.spark.sql.catalyst.parser.ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'INT': missing ')'. SQLSTATE: 42601 (line 6, pos 15)

== SQL ==
select * 
from 
(
    

DECLARE xx INT DEFAULT 2;
---------------^^^


select
xx;

      
) as `_`
limit 0

Same query against same compute cluster in Databricks:

 

[Anonymous]

Hi @AxiomaticOne 

Please  check your connection mode. if your connection mode is direct query, it is not support the declare() in query. it will casue the error.

You can consider to change the connection mode from direct query to import, it can use the declare() and without error.

You can refer to the following similar thread.

Solved: Re: Why is DECLARE not supported (Error)? - Microsoft Fabric Community

Solved: Using SQL statement Source in DirectQuery causes "... - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AxiomaticOne]

Direct Query is not the problem I am using import.

• Additional info: This same query runs correctly if connected to a Msft SQL Server connection, but when the connection is changed to Azure Databricks the problem happens.
• Equivelant T-SQL query:

let
    Source = Sql.Database(Server,Database,[Query = "

DECLARE @xx INT = 2;
SELECT @xx;

"])
in
	 Source