asked on

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" />'

DECLARE @tab TABLE (
      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:

DECLARE @tab TABLE (
id int NULL,
varchar_col varchar(7000) NULL
)

DECLARE @tabSrc TABLE (
text_col ntext NULL
)

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

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

So, THERE SEEMS TO BE AN UNSOLVED BUG in SQL Server 2005

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)

Thanks!

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

sangraalus

ASKER

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?

sangraalus

ASKER

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

Thanks!

sangraalus

ASKER

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
-> SELECT with WHERE EXISTS and NOT EXISTS
-> 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!

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial