Syntax error or access violation

Tuesday 29 July 2014 @ 9:53 pm

Today I was dealing with this error message. It was generated by a report that uses a SQL command with a parameter. The parameter was a numeric and when we replaced the parameter with a specific number, the error went away. Putting the parameter in the same spot generated the error.  So as part of troubleshooting I started taking out pieces of SQL until there was very little left of the query, and the error remained.  So we were surprised to find that moving the same SQL to a new report worked fine, even with the parameter.  The customer was ready to recreate the report from scratch.

Then she made a comment that she had once deleted and recreated a parameter to fix a similar problem.  Unfortunately that hadn’t worked for her today.  But this rang a bell, so I asked her if she had used the same parameter name when recreating the parameter.  She had, so I deleted the parameter and created a new parameter with a different name. I used the new parameter in place of the old one and the SQL ran correctly with no error message.  It appears that this parameter name, which she had used successfully in several other reports, had somehow gone flaky inside this report.  She tried to rename the working parameter back to its original name and the same error popped up.  So the problem wasn’t with the SQL Syntax or with the value but something specific to that parameter name in that one report.

So if you run into a similar problem with a SQL Command parameter and nothing else seems to fix it, here is one more thing you can try.









Leave a Reply

Recrystallize Pro