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
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'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