August 24, 2007 at 3:26 pm
MSSQL 2000
I am using the following procedure to create a simple cursor, and within that cursor to Create an XML Document, Load the XML, and then create a pointer to the Root (@root), and then Destroy the XML Document (this is scaled down from an original proc). Using the @counter I have found that after around 210 - 250 rounds, it crashes SQL and actually stops the service. If I take the @root OUTPUT out of the command, it runs through the entire cursor just fine.
Can anyone tell me what is wrong here? Can anyone duplicate this?
I get one of two errors:
ERROR1: [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionRead (recv()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
ERROR2: [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
TIA !!
--begin syntax
DECLARE @hr int,
@counter int ,
@xmlDOMObj int ,
@root int,
@load int
SET @counter = 0
WHILE @counter < 300
BEGIN
SET @counter = @counter + 1
--== CREATE DOCUMENT OBJECT
EXEC @hr = sp_OACreate 'MSXML2.DOMDocument.4.0', @xmlDOMObj OUTPUT
IF @hr <> 0 GOTO endScript
--== LOAD XML INTO DOCUMENT
EXEC @hr = sp_OAMethod @xmlDOMObj, 'loadXML', @load OUTPUT, '<ROOT/>'
IF @hr <> 0 GOTO endScript
IF @load = 0 GOTO endScript
--== DEFINE ROOT ELEMENT
EXEC @hr = sp_OAMethod @xmlDOMObj, 'documentElement', @root OUTPUT -- This Fails
--THIS WORKS: EXEC @hr = sp_OAMethod @xmlDOMObj, 'documentElement', NULL
IF @hr <> 0 GOTO endScript
--== DESTROY OBJECT
endScript:
EXEC @hr = sp_OADestroy @xmlDOMObj
PRINT @counter
PRINT @root
END
August 27, 2007 at 12:55 pm
I believe I have found the solution to this problem.
Upon reading the article below, I configured the sp_OACreate to execute 'out-of-process' by including the parameter of 4. When I executed, it ran through the whole loop without problems.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply