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?

Cliveahhh...blindman?

Clive...how does the data get in to the table?

How long has the symptom been appearing? Was it right after the column(s) where created?

Was it after a bcp/dts/bulk insert/simple insert?|||Brett,

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.

Clive|||No sh-t

I'm wondering if it's a connection issue...

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 sql_variant)
GO

INSERT INTO myTable99(Col2)
SELECT 1 UNION ALL
SELECT GetDate() UNION ALL
SELECT 'MOO'
GO

SELECT * FROM myTable99
GO

DBCC CHECKTABLE ('myTable99')
GO

DROP TABLE myTable99
GO|||You using XML?

http://support.microsoft.com/?kbid=822757|||[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.

Clive|||Set up a trace and monitor the situation...

did you see anything in the error logs?