• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

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!
0
sangraalus
Asked:
sangraalus
  • 3
  • 2
1 Solution
 
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
0
 
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?
0
 
sangraalusAuthor Commented:
angel, this username is also my Y M nick, could you please add me to your list?

Thanks!
0
 
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
-> 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!
0
 
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

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now