We are a SaaS customer and we copied LIVE over Pilot less than 1 month ago. We have Query that has been running fine for years, but now users are not seeing all the records. The query runs correctly in Pilot, but in LIVE we get the following error:
Severity: Error, Text: Bad SQL statement.
Review the server event logs for details.
Query returned 10 row(s).
Query has no more records to return.
Query execution total time: 482.1792 ms.
For some reason we are getting some results (but not as many as we should). I am using parameters from BEFORE we copied LIVE to Pilot (so the data in that timeframe should be the same).
I am struggling to understand why the query works perfectly in Pilot but has a Bad SQL statement in LIVE. The queries are identical. I have even exported the working query in Pilot and Imported it into live (and get the error in LIVE with the copied Query).
Epicor gave me the server event logs (because we are SaaS). The message that stands out in the Server Log is âConversion failed when converting the nvarchar value âUserIDâ to data type int.â The UserID in the previous sentence is my UserID in Epicor. I cannot find anywhere in the query where we are using the BAQ Special Constant of UserID.
This Query was created by a predecessor and I am struggling to figure out what the issue is.
Can anyone point me in the right direction? What areas should I be checking?
One thing to try is to limit your results. There may be one specific row in Live thatâs causing all the fuss that isnât in Pilot. So maybe try to set some criteria to divide the results into smaller chunks to see where the issue is⌠the other way you can do it is to remove calculated fields by commenting things out⌠and then one by one add them back in and run it and see when it fails.
That will effectively give you a row (or rows) and a column to be looking at.
Usually when I see a BAQ return some results and then error out it means it is choking on one specific record. Typically this winds up being a divide by zero, or as @Olga is suggesting, some kind of conversion issue.
Like @dr_dan is saying the record most likely only exists in your live environment.
In Pilot I return 12 rows and Live I get 10 rows (for the date Oct 6) This date is well before we copied Live over Pilot. When I filter for another parameter (Salesperson), I get 1644 rows in Pilot and only 361 rows in Live (and we have almost 1 month of more data i LIVE than we do in Pilot). So I donât think this is a single record.
The joins are tricky ones. Especially if youâre joining a UD table or something that is nvarchar to a table that is keyed by an integer, say a QuoteHed or something. Then you get a convert statement in the join.
Totally agree with @Olga
Olga, I originally thought this too, which is why I added the calculated field:
convert(int,LastTask.Key1 ).
There is a relationship between Erp.QuoteHed and a subquery called LastTask.
The link is QuoteNum in QuoteHed equal to LastTask_Key1 in (my subquery called LastTask that is only Erp.Task table).
one thing you could try (may or may not work) is changing the table order so that it sees the character field first so it sets the type to character instead of int.
Ah, didnât know that. So itâs possible the plans are just different then. Would a option(recompile) possibly fix it?
The other (probably right thing) to do then would be to cast your type in the join. You can type in those boxes and force the int field to be character.
The data is different we have about 1 month more data in LIVE compared to Pilot. But when using parameters limiting the data to a time before the copy over, we still get the error and we are still missing lines in LIVE.
Parameter limiting does not mean SQL server wonât process your data. It means it wonât return data you donât want.
You need to sanitize your data. First, find out what give the error - remove join, then remove calc field, (one step at a time) and check when error dissappears
. Unless you add hint to enforce it