sp_xml_removedocument returns 1

[penny]

sp_xml_removedocument consistently returns 1, even when it appears to run
successfully. BOL states that the xml is loaded into SQL Server's memory
space.
Running a perf mon counter on SQL Server memory shows an increase in mem
usage
after running sp_xml_preparedocument and then a subsequent decrease after
sp_xml_removedocument, even with the 1 returned. Has anyone else noticed
this or
ever seen sp_xml_removedocument return 0?

[Dan Doris]

This is a known and a bug has been filed on it. Hope this helps at least
knowing you are not doing something wrong...

dan doris.

"penny" <pesi...@usa.net> wrote in message
news:#F2YsF6wAHA.1400@tkmsftngp05...> sp_xml_removedocument consistently

[Shaun]

I get the same error, --BUT-- after 600 or so documents I also recieve an
error saying
XML PARSING ERROR: There is not enough space left etc etc.. Use
sp_xml_removedocument to free file handles.

Anyone else get this?

I tried to set my server memory to 80MB on a 128MB machine.

"Dan Doris" <nos...@microsoft.com> wrote in message
news:ORGXvyIxAHA.1888@tkmsftngp03...

[Dan Doris]

*EVERY* call to prepare MUST be matched with a corresponding remove or you
will leak memory. If this is not the case you are running into let us know.

dan doris

"Shaun" <sh...@bugtank.com> wrote in message
news:##mzr83xAHA.2224@tkmsftngp02...

[penny]

If you fail to pair a call to sp_xml_preparedocument with a call to
sp_xml_removedocument, is there a way to go back later and get a list of
unremoved documents? Does stopping and restarting the server remove
documents that were not explicitly removed beforehand?

"Dan Doris" <nos...@microsoft.com> wrote in message

news:uFoDIC4xAHA.1840@tkmsftngp04...

[Dan Doris]

Yes, cycling the server should reclaim the memory. The calls should always
be paired up and the appropriate code within your error handlres.

Dan Doris.

"penny" <pesp...@usa.net> wrote in message
news:e2knGa5xAHA.2020@tkmsftngp03...

[Shaun]

This is most definatley not the case - we are pairing up every call to
prepare with a call to remove immediatley after procesing occurs.
This is the error i get.

Server: Msg 6624, Level 16, State 1, Procedure sp_xml_preparedocument, Line
98
XML document could not be created because server memory is low. Use
sp_xml_removedocument to release XML documents.

"Dan Doris" <nos...@microsoft.com> wrote in message

news:uFoDIC4xAHA.1840@tkmsftngp04...

[Dan Doris]

If you can get us a repro we definately would like to see it.

Thanks,

dan doris.

"Shaun" <sh...@bugtank.com> wrote in message

news:#ZrvUVEyAHA.1620@tkmsftngp05...

[Shaun]

Sure,

Some Background for you
-------------------------------------

The Application is a set of storedprocedures on SQL that consume XML
produced by Biztalk server. This is EID data converted to our XML format
for retail energy. The Stored procedures consume the XML, for three
different levels, a File envelope level, a transaction level, and the
transaction contect level. The file level handles opening the XML text
passed in (which can be up to 2 megs large) with sp_xml_processDocument and
passes the file handle all the way down to the supporting processing
procedures. The file handle returns back after the processing is done and
sp_xml_removedocument is executed, thus removing the document from memory.
--------------------------------------

** NOTE: sp_preparedocument setting the @@error to something but the
@retcode = sp_xml_preparedocument is 0. The Document handle passed back in
this instance works tho! @@Error is an Error Conversion thing.

I've charted 4 instances to try and narrow the remove docuemnt bug down.
---------------------------------------------------------------
1 - Running code that just checks the @RetCode from procprocessFiles and
kills the Doc at the end of processing.
2 - Check the ERROR code from procProcessFiles and instead of returning to
the calling procedure, make sure i call removedocuemtn on the @@ERROR'ed
@iDoc handle.

3,4 - And the same as above but having the client machine kill and restart
the connection to the DB every 100 files or so.

Hopefullly i can rat out a piece of my code or logic that's casuing this
problem - some bug i'm not seeing appear so that execution stops, the client
machine gets good codes but the reality of it is that the execution just
stopped and never called removedocument. TSQL even with robust handling can
throw these curveballs from time to time.

Currently we're having issues with starting the SQL service on my colleagues
desktop - just stopped wanting to start one day, no error no nothing, every
time you his the start button it wouldn't give any error it would just sit
there.

and uninstalling and reinstalling SQL resulted in it erroring on TempDB
creation.

will be back with more

"Dan Doris" <nos...@microsoft.com> wrote in message

news:#VIG#fHyAHA.236@tkmsftngp03...