Forum Discussion
Parameterized Sql Statement: Expression.Error: This native database query isn't currently supported
Hello,
I have a query in excel that loads without any issues. I have tried adding parameters to the query(SQL - statement), but this leads to this error: Expression.Error: This native database query isn't currently supported.
I tried looking at the M code and seeing if I could turn query folding off, as from looking around on different forums this is what came up as a solution, however I don't have that option. I was wondering if any would know if there any other potential solutions to my problem.
An extra detail: The query itself isn't a select * from some_table, its a call to a procedure that has inputs that I would like to parameterize in excel.
Let me know if you need more details, also if this is the right forum to post this on.
Thank you for the help!
That could be
... pParText = fParameter(ParameterTable,From), Source = Sql.Database(Server, Database, [Query = "CALL SALES_BY_CATEGORY_1234 ( '51', " & pParText & " '2020-12-31','',......)"]), nextStep, ...Another way is as here Chris Webb's BI Blog: Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI Chris Webb's BI Blog (crossjoin.co.uk) but in general they are not too far from each other.
One of ways is like
prevStep, Source = Sql.Database(Server, Database, [Query = " declare @pDate date = ' "& myParameter01asText & "'; WITH ...... SELECT something FROM somewhere WHERE ID >= " & myParameter02asText & " ORDER BY some ASC "]), nextStepbut with this yes, query won't be folded and it's better max logic to put into SQL query.
If don't use SQL query and do M-script in right order, query folding mechanism will work and actually sql query will be generated in background automatically.
Thank you for the quick reply, please note I'm not really familiar with M code and don't quite understand your solution. I will add some more detail about the issue I'm facing.
My SQL Query looks like this:
CALL SALES_BY_CATEGORY_1234 ( '51','2020-01-01','2020-12-31','','en','dd/MM/yyyy','2020-03-01','2020-12-01','Product','all','all','all','all','','all','','TPHX16','all','all','all','Regular','All','No','No','No','No','No','00208 ','All','202101','Detailed','all','all','all','Customer Salesman','all','Default')Also in terms of the parameterized SQL statement, I have created a table (2 columns, 1st column parameter "identifier" 2nd column, the value I want as input) which a user can input (type), the value which will be the input for the SQL statement. I then created a function with power query that would do this.
Example:
CALL SALES_BY_CATEGORY_1234 ( '51',fParameter(ParameterTable,From),'2020-12-31','',......)where ParameterTable is the name of the table where I have my input and From is the parameter "identifier", which lets the function know which value to place into the query.
You said that your solution was one of a few, is there something that can align more towards what I'm trying to achieve?
Thank you
That could be
... pParText = fParameter(ParameterTable,From), Source = Sql.Database(Server, Database, [Query = "CALL SALES_BY_CATEGORY_1234 ( '51', " & pParText & " '2020-12-31','',......)"]), nextStep, ...Another way is as here Chris Webb's BI Blog: Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI Chris Webb's BI Blog (crossjoin.co.uk) but in general they are not too far from each other.
I experienced the same error with a similar setup (Excel value.NativeQuery call to a parameterized SQL Server Stored Procedure). Note the execution of the NativeQuery call to SQL (to insert records into a table) was successful, but Excel reported an "Expression.Error: This native database query isn't currently supported." error. In my case, this was caused when SET NOCOUNT ON is present in the stored procedure. I cannot find any NativeQuery documentation to determine if this is expected behaviour.
