Dynamic Parameter m Error with text value including -

[snowstar]

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!

[snowstar · Accepted solution]

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.

[Anonymous]

So it looks like the parameter is named CustomerLocation; any reason why you use ParamCustomerLocation instead of just CustomerLocation?

[snowstar · Accepted solution]

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.

[snowstar]

Hi watkinnc,

THe parameter is ParamCustomerLocation and it takes the data from a list query called CustomerLocation.

[Anonymous]

Can we see some of your code, plus the parameter code?

[snowstar · Accepted solution]

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.

[TrevorCook]

Hi there.

I know that this is late and i hope that you found a solution. 

I have not cofriemd 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/