Undocumented, unfixed BUG in SQL 2000. Internal SQL Server error.

Posted on 2006-04-08
Last Modified: 2012-08-13
Hi all!

Try running this and you will get the following error in SQL 2000.
Internal SQL Server error.

SQL 2005 doesn't have the problem anymore. So please run the examples below:

DECLARE @iDoc int
DECLARE @xml nvarchar(4000)
SET @xml = '<?xml version="1.0" ?><element atribut1="Ana are mere" atribut2="Si Dana" />'

      id int      NULL,
      textcol varchar(7000) NULL

EXEC sp_xml_preparedocument @iDoc output, @xml

      INSERT INTO @tab VALUES (1, '')

      UPDATE @tab SET textcol = cast([text] AS varchar(7000))
            FROM OpenXML (@iDoc, '/element')
            WHERE localname = '#text'

--      --This will work
--      INSERT INTO @tab
--            SELECT 1, cast([text] as varchar(7000))
--                  FROM OpenXML (@iDoc, '/element')
--            WHERE localname = '#text'
--      SELECT * FROM @tab

EXEC sp_xml_removedocument @iDoc

So, the problem of updating a varchar column from an edge table from the text column ives the error. You can build another example with 2 tables, the one above and the one which lookk like the one returned by the OpenXML. This will work either. Here is the code:

    id int    NULL,
    varchar_col varchar(7000) NULL

    text_col ntext NULL

INSERT INTO @tabSrc VALUES (N'This is of type text.')

UPDATE @tab SET varchar_col = cast(text_col AS varchar(7000))
FROM @tabSrc


When replying to this post do not come with comments like:
-> Q: There is a bugfix for the UPDATE, did you apply it?
     A: It is not the case for this example of UPDATE.
-> Q: Try to use temporary tables.
     A: My point is not the problem that i have, which I can solve with a DELETE and an INSERT, which works, but th fact that maybe, somehow this will get on the desktop of some MS engineer. :) "Dream on...."
-> Q: Who cares about SQL 2000 anymore, switch to 2005.
     A: ... (no comment)

Question by:sangraalus
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I could reproduce the problem on my server.
    My suggestion is (although you prohibited that) to use the workaround with a temporary table into which to insert the results of openxml (which works fine), and update the final table using a UPDATE on the join between the 2.

    listening & learning
    LVL 1

    Author Comment

    Thanks angel,

    I am also trying to avoid temp tables as much as possible, a DELETE-INSERT solution would also work here as well.

    I hope smbdy will see this post and they will fix the bug.

    But just as a matter of performance, which do you thinks is more affective: the temp table or the DELETE-INSERT using joins with OpenXML. Or, is a temp table more effective than opening the same XML many times?

    Any idea?
    LVL 1

    Author Comment

    angel, this username is also my Y M nick, could you please add me to your list?

    LVL 1

    Author Comment

    And here is another question angel, if you don't mind,

    which do you think is the best solution for

    Table A
    1     a
    2     b
    3     c
    4     d

    XML comes with:
    2     m
    4     n
    5     o
    6     p

    I want to update the existing rows and insert the new ones.

    There are many solution going through my mind:
    -> DELETE all that are in the intersection (INNER JOIN) and insert all in the XML
    -> UPDATE all that are intersection (INNER JOIN) and insert all the other from the XML (RIGHT OUTER JOIN)
    -> the same two solutions implementer with INTERSECT and EXCEPT
    -> WHERE with the id IN (SELECT...)
    -> etc.

    Any ideea on performance, ix the XML is large (hundreds or Ks of elements) and the table is very large (hundreds of thausands or Ms maybe)?

    Thanks again!
    LVL 142

    Accepted Solution

    >Or, is a temp table more effective than opening the same XML many times?
    it is more effective, as:
    1) you perform the OpenXML only once, which is the most cost-expensive operation
    2) instead of a temporary table, use the table variable (unless the xml is big/huge)
    3) the update will then be more effective than the delete/insert on the final table

    >I want to update the existing rows and insert the new ones.
    with the table variable (sourcetable) having the OpenXML output, make it 2 queries:

    -- update existing
    update destinationtable
      set value = s.value
    from destionationtable d
    join sourcetable s
     on =

    -- insert missing
    insert into destinationtable
      (id, value)
    select, s.value
    from sourcetable s
    left join destinationtable d
      on =
    where is null


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now