SQLKitten

For my first post I thought I would start things out with a bang and detail an issue that I recently encountered:

Location: “qstopsrt.cpp”:404
Expression: fFalse
SPID: 51
Process ID: 2908
Location: qxcntxt.cpp:1221
Expression: cref == 0
SPID: 51
Process ID: 2908
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

In case you were wondering that CHECKDB was a waste of time (in this situation). Had never seen something like this before, but who doesn’t like a good challenge?

If you have seen this error, and tried to research it, you know there is not much out there. Most of what I found had to do with issues with the msdb or master databases. Those did not apply to my situation.

Anyways, on to the code:

SELECT TOP 50
[personid] as [personid],
[shoeid] as [shoeid],
[shoe_name] as [shoe_name],
DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) as [days_since_purchase],
FROM vw_yabba WITH (NOLOCK)
where [Paid_in_full] = '1'
ORDER BY [days_since_purchase] desc

SELECT TOP 50
[personid] as [personid],
[shoeid] as [shoeid],
[shoe_name] as [shoe_name],
[shoe_type] as [shoe_type],
[shoe_size] as [shoe_size],
DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) as [days_since_purchase],
dateadd(day,datediff(day,0,delivey_date),0) as [delivery_date],
CASE WHEN [Paid_in_full] = 1 THEN 'YES' ELSE 'NO' END as [Paid],
[purchase_date] as [purchase_date]
FROM vw_yabba WITH (NOLOCK)
WHERE 0 = 0
AND ((((1=1))))
AND ( ( ( (
([shoe_type] = 'Stiletto')
AND ([Paid_in_full] = '1')
AND ( (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '23')
OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '24')
OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '25') )
AND ( ([shoe_rating] < '100') OR ([shoe_rating] IS NULL))) OR ( ([Paid_in_full] = '1') AND ( (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '23') OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '24') OR (DATEDIFF(day, CAST(purchase_date AS DATE), GETDATE()) = '25') ) AND ( ([shoe_rating] < '50') OR ([shoe_rating] IS NULL ) ) AND ( ([shoe_type] = 'mule') OR ([shoe_type] = 'athletic') OR ([shoe_type] = 'flat') ) ) ) ) ) ORDER BY [days_since_purchase] asc


Other useful background info:

• Both queries produce the same error.
• 3rd party application code - dynamically generated; cannot be changed.
• Happend on a production reporting server.
• View queries two tables with a union; tables are refreshed hourly.
• Not all data is pulled from the tables - some fields are defined in the view.

Researched the error, but like I said - didn't find much that was of any use. Picked someone else's brain on the issue - he said there was an additional thing that could be added to the TOP 50 - WITH TIES. I changed the first query to a TOP 1 WITH TIES and got five records back. Kept working with both queries, trying different things. Eventually found that if I left the Paid_in_Full field out of the where statement the query would complete.

Examined the view - Paid_in_Full was defined as '1' in the view for one table and '0' for the other. Surmised from this that the implied data type could be the cause of the problem.

Solution that resolved the issue - modified the process that populates the tables to include this field (meaning the datatype would now be explicit).

Funny how something so simple could cause an issue so obscure.