-1

In the past couple of days we have experienced issues with 3 different applications that have been running fine for several years. Errors #1 and #2 were caused by reserved words in SQL queries. I am fairly certain that is also the cause of Error #3, but I cannot be sure as I don't have the source code. The databases for the three applications are all on the same instance of SQL Server. We are running SQL Server 2008 with no updates, and the last Windows update was in 2016. What could have caused these errors to suddenly start occurring?

Error details:

  1. We have a Web Forms site that has been used daily (and not updated) for the past 6 years. Two days ago it suddenly started throwing a SQL timeout exception on one of its pages. In SSMS I tested the raw queries being used and they all ran in under 2 seconds. However I noticed that two of them used the reserved words "Status" and "Date" without square brackets. I added square brackets around the reserved words and re-uploaded the site, and that fixed the error.
  2. Today users reported an issue with a report in an old MS Access Data Project. I tested the report myself and got a query timeout. The data source for the report is a table-valued function, which when I executed directly in SSMS completed in a matter of seconds. I examined the function body and saw that it used the reserved words "Priority" and "Timestamp". I added square brackets, altered the function, and voila, the report now opens with no errors.
  3. We have an .asmx web service that has been used daily for the past 6 years. One of the methods is supposed to query a table and return a list of user logins and passwords. Yesterday it started returning an empty response object. I don't have the source code for the web service so I can't see the text of the query. However I can see that the table has columns named with the reserved words "User_ID" and "Password", and based on the pattern for errors #1 and #2 I would bet that that's the problem.

These queries have been running for years without problems. Why would they suddenly start causing problems? Is there perhaps a configuration in SQL Server that has been changed?

  • 1
    Did you run a trace to check errors? – Racer SQL Jul 21 '17 at 15:16
  • 1
    None of these are reserved keywords. The "fix" was probably a side effect of the service restart. If they were reserved, you would be getting errors in these 6 years. – ypercubeᵀᴹ Jul 21 '17 at 15:18
  • 2
    Did someone change database compatibility levels recently? – Erik Darling Jul 21 '17 at 15:26
  • 1
    Also please explain: did you get some error when you run the queries (with the allegedly reserved keywords in SSMS)? What was the error message? – ypercubeᵀᴹ Jul 21 '17 at 15:30
  • 3
    If you alter the text of a query that is timing out and suddenly it works fine that may well indicate parameter sniffing. A new plan will be cached. – Martin Smith Jul 21 '17 at 16:55
1

You're not getting an error from the queries themselves - you're just seeing them take a long time to run. So you're not facing a problem where it has a problem with "date"'a being a reserved word.

What you're more likely to be seeing is a series of bad plans, possibly caused by some statistics getting updated at a bad time or something similar. The fact that you can tweak the queries a little and see performance improve makes me think that you could solve this by identifying and clearing out the bad plans, because you've been able to get good plans very easily. Query select * from sys.dm_exec_plan_cache c cross apply sys.dm_exec_sql_text(c.sql_handle) t or something like that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.