Forum Discussion
Dynamic Parameter m Error with text value including -
Hello!
My Dynamic Parameter m is giving an error when the text value from a list of plain parameter has a hyphen (-) in it.
Like 312-34
Not sure why, I defined as text.
I get this error:
DataSource.Error: Microsoft SQL: Statement(s) could not be prepared.
Incorrect syntax near '-'.
Details:
DataSourceKind=SQL
DataSourcePath=mtlsql01;Reco_DEF
Message=Statement(s) could not be prepared.
Incorrect syntax near '-'.
ErrorCode=-2146232060
Number=8180
Class=16
Can anyone help?
I spent the whole day trying to figure it out.
Thanks!
Thanks for your help! (Not sure if I replied at the right spot)
This is the Query Code:
= Sql.Database("MTLSQL01", "Reco_DEF", [Query="SELECT *#(lf)FROM OPENQUERY ([MTLSQL01],#(lf)'EXEC Reco_DEF.dbo.DEF_Customer_Detail_NoDuplication_pbi " & ParamCustomer & ",
" & ParamCustomerLocation & " , " & Division & " ') " ])This is the parameter code:
"2705-10" meta [IsParameterQuery=true, ExpressionIdentifier=CustomerLocation, Type="Text", IsParameterQueryRequired=true]
If I put the parameter in quotes in current value: "2705-10" It works.
"""2705-10""" meta [IsParameterQuery=true, ExpressionIdentifier=CustomerLocation, Type="Text", IsParameterQueryRequired=true]
Thaks in advance.
I fixed the issue.
After running profiler, I copied and pasted statement that was sent and ran it in sql.
It failed.
I added brackets to my parameter in sql and it worked.
I then added brackets in m:
[" & ParamCustomerLocation & "]
and it worked!
Maybe I need to do this because I am using OPENQUERY?
In any case thanks for your help watkinnc.
6 Replies
Thanks for your help! (Not sure if I replied at the right spot)
This is the Query Code:
= Sql.Database("MTLSQL01", "Reco_DEF", [Query="SELECT *#(lf)FROM OPENQUERY ([MTLSQL01],#(lf)'EXEC Reco_DEF.dbo.DEF_Customer_Detail_NoDuplication_pbi " & ParamCustomer & ",
" & ParamCustomerLocation & " , " & Division & " ') " ])This is the parameter code:
"2705-10" meta [IsParameterQuery=true, ExpressionIdentifier=CustomerLocation, Type="Text", IsParameterQueryRequired=true]
If I put the parameter in quotes in current value: "2705-10" It works.
"""2705-10""" meta [IsParameterQuery=true, ExpressionIdentifier=CustomerLocation, Type="Text", IsParameterQueryRequired=true]
Thaks in advance.
Hi there.
I know that this is late and i hope that you found a solution.
I have not confirmed this but I bet that you need to use escapre characters aroudn the hyphen. Here is a great article: https://blog.crossjoin.co.uk/2018/03/05/character-escape-sequences-in-m/
Hi watkinnc,
THe parameter is ParamCustomerLocation and it takes the data from a list query called CustomerLocation.
I fixed the issue.
After running profiler, I copied and pasted statement that was sent and ran it in sql.
It failed.
I added brackets to my parameter in sql and it worked.
I then added brackets in m:
[" & ParamCustomerLocation & "]
and it worked!
Maybe I need to do this because I am using OPENQUERY?
In any case thanks for your help watkinnc.
