SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2113381%22%20slang%3D%22en-US%22%3EParameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20supported%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113381%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20query%20in%20excel%20that%20loads%20without%20any%20issues.%20I%20have%20tried%20adding%20parameters%20to%20the%20query(SQL%20-%20statement)%2C%20but%20this%20leads%20to%20this%20error%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20supported.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20looking%20at%20the%20M%20code%20and%20seeing%20if%20I%20could%20turn%20query%20folding%20off%2C%20as%20from%20looking%20around%20on%20different%20forums%20this%20is%20what%20came%20up%20as%20a%20solution%2C%20however%20I%20don't%20have%20that%20option.%20I%20was%20wondering%20if%20any%20would%20know%20if%20there%20any%20other%20potential%20solutions%20to%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20extra%20detail%3A%20The%20query%20itself%20isn't%20a%20select%20*%20from%20some_table%2C%20its%20a%20call%20to%20a%20procedure%20that%20has%20inputs%20that%20I%20would%20like%20to%20parameterize%20in%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20need%20more%20details%2C%20also%20if%20this%20is%20the%20right%20forum%20to%20post%20this%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2113381%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2750912%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2750912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953798%22%20target%3D%22_blank%22%3E%40cl27274%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20experienced%20the%20same%20error%20with%20a%20similar%20setup%20(Excel%20value.NativeQuery%20call%20to%20a%20parameterized%20SQL%20Server%20Stored%20Procedure).%20Note%20the%20execution%20of%20the%20NativeQuery%20call%20to%20SQL%20(to%20insert%20records%20into%20a%20table)%20was%20successful%2C%20but%20Excel%20reported%20an%20%22Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20supported.%22%20error.%20In%20my%20case%2C%20this%20was%20caused%20when%26nbsp%3BSET%20NOCOUNT%20ON%20is%20present%20in%20the%20stored%20procedure.%20I%20cannot%20find%20any%26nbsp%3BNativeQuery%20documentation%20to%20determine%20if%20this%20is%20expected%20behaviour.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113991%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113991%22%20slang%3D%22en-US%22%3EI%20have%20posted%20it%20and%20tagged%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113976%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953798%22%20target%3D%22_blank%22%3E%40cl27274%3C%2FA%3E%26nbsp%3BThank%20you.%20Didn't%20find%20your%20new%20question%20so%20far.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113944%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20accept%20this%20as%20the%20best%20response%20and%20start%20a%20new%20thread%20about%20the%20new%20issue%20I'm%20having%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113503%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113503%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20to%20do%20this%2C%20unfortunately%2C%20I'm%20getting%20a%20new%20error.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EDataSource.Error%3A%20Microsoft%20SQL%3A%20A%20connection%20was%20successfully%20established%20with%20the%20server%2C%20but%20then%20an%20error%20occurred%20during%20the%20pre-login%20handshake.%20(provider%3A%20TCP%20Provider%2C%20error%3A%200%20-%20An%20existing%20connection%20was%20forcibly%20closed%20by%20the%20remote%20host.)%0ADetails%3A%0A%20%20%20%20DataSourceKind%3DSQL%0A%20%20%20%20DataSourcePath%3D%20(some%20server%20name)%0A%20%20%20%20Message%3DA%20connection%20was%20successfully%20established%20with%20the%20server%2C%20but%20then%20an%20error%20occurred%20during%20the%20pre-login%20handshake.%20(provider%3A%20TCP%20Provider%2C%20error%3A%200%20-%20An%20existing%20connection%20was%20forcibly%20closed%20by%20the%20remote%20host.)%0A%20%20%20%20ErrorCode%3D-2146232060%0A%20%20%20%20Number%3D10054%0A%20%20%20%20Class%3D20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BNormally%2C%20I%20connect%20to%20the%20SQL%20database%20through%20an%20ODBC%20connection.%20Would%20this%20change%20anything%20to%20the%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20tried%20using%26nbsp%3B%20Odbc.Query%2C%20and%20Odbc.Datasource%20as%20alternatives%20to%20Sql.Database%2C%20but%20it%20didn't%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113431%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953798%22%20target%3D%22_blank%22%3E%40cl27274%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E...%0ApParText%20%3D%20fParameter(ParameterTable%2CFrom)%2C%0A%20%20%20%20Source%20%3D%20Sql.Database(Server%2C%20Database%2C%0A%20%20%20%20%5BQuery%20%3D%20%22CALL%20SALES_BY_CATEGORY_1234%20(%20'51'%2C%20%22%20%26amp%3B%20pParText%20%26amp%3B%20%22%20'2020-12-31'%2C''%2C......)%22%5D)%2C%0AnextStep%2C%0A...%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20way%20is%20as%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fblog.crossjoin.co.uk%2F2016%2F12%2F11%2Fpassing-parameters-to-sql-queries-with-value-nativequery-in-power-query-and-power-bi%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EChris%20Webb's%20BI%20Blog%3A%20Passing%20Parameters%20To%20SQL%20Queries%20With%20Value.NativeQuery()%20In%20Power%20Query%20And%20Power%20BI%20Chris%20Webb's%20BI%20Blog%20(crossjoin.co.uk)%3C%2FA%3E%26nbsp%3Bbut%20in%20general%20they%20are%20not%20too%20far%20from%20each%20other.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113427%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20quick%20reply%2C%20please%20note%20I'm%20not%20really%20familiar%20with%20M%20code%20and%20don't%20quite%20understand%20your%20solution.%20I%20will%20add%20some%20more%20detail%20about%20the%20issue%20I'm%20facing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20SQL%20Query%20looks%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ECALL%20SALES_BY_CATEGORY_1234%20(%20'51'%2C'2020-01-01'%2C'2020-12-31'%2C''%2C'en'%2C'dd%2FMM%2Fyyyy'%2C'2020-03-01'%2C'2020-12-01'%2C'Product'%2C'all'%2C'all'%2C'all'%2C'all'%2C''%2C'all'%2C''%2C'TPHX16'%2C'all'%2C'all'%2C'all'%2C'Regular'%2C'All'%2C'No'%2C'No'%2C'No'%2C'No'%2C'No'%2C'00208%20%20%20%20%20'%2C'All'%2C'202101'%2C'Detailed'%2C'all'%2C'all'%2C'all'%2C'Customer%20Salesman'%2C'all'%2C'Default')%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAlso%20in%20terms%20of%20the%20parameterized%20SQL%20statement%2C%20I%20have%20created%20a%20table%20(2%20columns%2C%201st%20column%20parameter%20%22identifier%22%202nd%20column%2C%20the%20value%20I%20want%20as%20input)%20which%20a%20user%20can%20input%20(type)%2C%20the%20value%20which%20will%20be%20the%20input%20for%20the%20SQL%20statement.%20I%20then%20created%20a%20function%20with%20power%20query%20that%20would%20do%20this.%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ECALL%20SALES_BY_CATEGORY_1234%20(%20'51'%2CfParameter(ParameterTable%2CFrom)%2C'2020-12-31'%2C''%2C......)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20ParameterTable%20is%20the%20name%20of%20the%20table%20where%20I%20have%20my%20input%20and%20From%20is%20the%20parameter%20%22identifier%22%2C%20which%20lets%20the%20function%20know%20which%20value%20to%20place%20into%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20said%20that%20your%20solution%20was%20one%20of%20a%20few%2C%20is%20there%20something%20that%20can%20align%20more%20towards%20what%20I'm%20trying%20to%20achieve%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113409%22%20slang%3D%22en-US%22%3ERe%3A%20Parameterized%20Sql%20Statement%3A%20Expression.Error%3A%20This%20native%20database%20query%20isn't%20currently%20suppor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113409%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953798%22%20target%3D%22_blank%22%3E%40cl27274%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20of%20ways%20is%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3EprevStep%2C%0A%20%20%20%20Source%20%3D%20Sql.Database(Server%2C%20Database%2C%0A%20%20%20%20%5BQuery%20%3D%20%22%0A%0A%20%20%20%20%20%20declare%20%0A%20%20%20%20%20%20%40pDate%20date%20%3D%20'%20%22%26amp%3B%20myParameter01asText%20%26amp%3B%20%22'%3B%0A%0A%20%20%20%20%20%20WITH%0A%20%20%20%20%20%20%20%20%20......%0A%20%20%20%20%20%20SELECT%20something%20%0A%20%20%20%20%20%20FROM%20somewhere%0A%20%20%20%20%20%20WHERE%20ID%20%26gt%3B%3D%20%22%20%26amp%3B%20myParameter02asText%20%26amp%3B%20%22%0A%20%20%20%20%20%20ORDER%20BY%20some%20ASC%0A%20%20%20%22%5D)%2C%0AnextStep%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20with%20this%20yes%2C%20query%20won't%20be%20folded%20and%20it's%20better%20max%20logic%20to%20put%20into%20SQL%20query.%3C%2FP%3E%0A%3CP%3EIf%20don't%20use%20SQL%20query%20and%20do%20M-script%20in%20right%20order%2C%20query%20folding%20mechanism%20will%20work%20and%20actually%20sql%20query%20will%20be%20generated%20in%20background%20automatically.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

 

 

 

 

 

 

 

 

8 Replies

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

@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

best response confirmed by cl27274 (Occasional Contributor)
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. 

@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

@Sergei Baklan 

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

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

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