Feb 03 2021 02:51 PM
Feb 03 2021 02:51 PM
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!
Feb 03 2021 03:25 PM
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.
Feb 03 2021 03:58 PM
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.
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?
Feb 03 2021 04:12 PMSolution
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.
Feb 03 2021 05:43 PM
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
Feb 04 2021 05:42 AM
I'll accept this as the best response and start a new thread about the new issue I'm having
Feb 04 2021 06:22 AM
@cl27274 Thank you. Didn't find your new question so far.