Parameterized Sql Statement: Expression.Error: This native database query isn't currently supported

[cl27274]

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!

[#2]

@cl27274 

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
   "]),
nextStep

but 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.

[#3]

@Sergei Baklan 

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

[#4 · Accepted solution]

@cl27274 

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 ... but in general they are not too far from each other. 

[#5]

@Sergei Baklan 

I've tried to do this, unfortunately, I'm getting a new error.

DataSource.Error: Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Details:
    DataSourceKind=SQL
    DataSourcePath= (some server name)
    Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
    ErrorCode=-2146232060
    Number=10054
    Class=20

 Normally, I connect to the SQL database through an ODBC connection. Would this change anything to the code?

I also tried using  Odbc.Query, and Odbc.Datasource as alternatives to Sql.Database, but it didn't help

[#6]

@Sergei Baklan 

I'll accept this as the best response and start a new thread about the new issue I'm having

[#7]

@cl27274 Thank you. Didn't find your new question so far.

[#8]

I have posted it and tagged you.

[#9]

@cl27274 

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.