Forum Discussion

cl27274's avatar
cl27274
Copper Contributor
Feb 03, 2021
Solved

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 qu...
BI & Data Analysis
excel
office 365
power bi
cl27274's avatar
cl27274
Copper Contributor
Feb 03, 2021

SergeiBaklan 

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 04, 2021

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 Power BI Chris Webb's BI Blog (crossjoin.co.uk) but in general they are not too far from each other. 

  • cl27274's avatar
    cl27274
    Copper Contributor
    Feb 04, 2021

    SergeiBaklan 

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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 04, 2021

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

      • cl27274's avatar
        cl27274
        Copper Contributor
        Feb 04, 2021
        I have posted it and tagged you.
  • cl27274's avatar
    cl27274
    Copper Contributor
    Feb 04, 2021

    SergeiBaklan 

    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

Resources