Unanswered: dbcc checkdb errors against sql_variant
Recently, I've started to get DBCC Checkdb errors against a few databases - seems to be since applying sp3a.
The error (2537) affects certain tables that contain SQL_Variant fields. I just completed an exercise to run DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS) against a copy of the offending table - then comparing the original table and the repaired table to isolate the rows that throw up the consistency errors. What I found was that all the rows trapped by DBCC CHECKDB for error 2537 contain real numbers in the SQL_Variant field. All other rows with text or integer contents in the SQL_Variant field do not throw up errors.
I tried a MS hotfix for error 2537 but it didn't fix the problem. Any ideas?
The data gets into the table via a browser front-end application - ASP pages. I think the original idea of using the variant type for this app was the the users could define their own fields for their own purposes via the app front-end.
I think the problem started to surface after applying SP3a. I've been running DBCC Checkdb for a long time against these same tables without getting errors. So, to answer your question, yes it was right after the columns were created.
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 sql_variant)
INSERT INTO myTable99(Col2)
SELECT 1 UNION ALL
SELECT GetDate() UNION ALL
SELECT * FROM myTable99
DBCC CHECKTABLE ('myTable99')
DROP TABLE myTable99
[QUOTE][SIZE=1]Originally posted by Brett Kaiser
You using XML?
No XML in this case.
I ran your test script - no problems reported by DBCC CHECKDB. I also added in a row including a real number - also no problems.
We tried running DBCC checkdb with the allow data loss option to fix the problem and then re-inserted the data. This seemed ok but then then users found that this caused a problem to the app - so had to restore the db.