Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a native query with a parameterised where caluse like this...
WHERE CALDATE > @datefrom AND CLDATE < @dateto
I try to query it
let
Source = OleDb.DataSource("provider=IBMDASQL.DataSource.1;data source=serverconnection),
Database = Source{[Name="SERVER",Kind="Database"]}[Data],
calendar = Value.NativeQuery(Database,Query1, [datefrom="20230701",dateto="20240701"])
in
calendar
This results in a datasource error, Column or global variable @DATEFROM not found.
I guess the parameters are not being picked up by Value.NativeQuery.
If I remove the parameters from the where clause, or replace with literal values, it runs fine.
Is this not supported for OLEDB?
The issue you’re encountering might be due to the way OLE DB handles parameters in native queries. In some cases, OLE DB might not support parameterized queries in the same way as other data sources1.
Here are a few potential solutions:
Use Literal Values: If possible, consider replacing the parameters with literal values directly in the query. This might not be feasible if the parameters change frequently or are user inputs1.
Use Power Query Parameters: You can create parameters in Power Query and use them in your native que...2. However, this might not work if you need to pass the parameters dynamically at runtime1.
Check Query Syntax: Ensure that the syntax of your query is correct and that the parameters are prop...1.
Consider Other Data Sources: If OLE DB continues to cause issues, you might want to consider using a...1.
Remember, these are just a few potential solutions. The exact solution might vary depending on the specifics of your setup1.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.