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

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)

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
sangraalusAuthor Commented:
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?
sangraalusAuthor Commented:
angel, this username is also my Y M nick, could you please add me to your list?

sangraalusAuthor Commented:
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!
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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 s.id = d.id

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.