Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    71

    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?

    Clive

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ahhh....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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Posts
    71
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No sh-t

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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Posts
    71
    [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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Set up a trace and monitor the situation...

    did you see anything in the error logs?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •