Microsoft KB Archive/279078

= PRB: Access Violation Occurs with the Use of OpenXML in a User-Defined Function =

Article ID: 279078

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q279078



SYMPTOMS
SQL Server 2000 reports an Access Violation if a user-defined function (UDF) contains a call to the OpenXML function. The following error message is reported:

ODBC: Msg 0, Level 19, State 1

SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005

EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.



CAUSE
This error message is the result of a current limitation in SQL Server 2000 that prevents the intermediate rowset from OpenXML from being streamed as input to another SQL command.



RESOLUTION
To work around the problem, you can insert the intermediate rowset from the OpenXML call into a temporary table.

This resolution is illustrated in more detail in the &quot;More Information&quot; section of this article.



Steps to Reproduce Behavior
  Create a user-defined function, as follows: CREATE FUNCTION dbo.udfTestOPENXML (@iDocID INT, @sXPath NVARCHAR(100)) RETURNS TABLE AS RETURN SELECT * FROM OPENXML (@iDocID, @sXPath) WITH (     Value1  VARCHAR(10),      Value2  VARCHAR(10)    ) GO NOTE: The following steps 2 through 4 must be executed in a single batch rather than individually.

  Declare an XML document variable and a text variable to hold the XML text: DECLARE @hXMLInsertDoc int, @hXMLInsertDocText varchar(1000)

SET @hXMLInsertDocText ='This is a test '   Prepare the XML document from the text: EXEC sp_xml_preparedocument @hXMLInsertDoc OUTPUT, @hXMLInsertDocText   Execute the user-defined function with the prepared document: SELECT * FROM dbo.udfTestOPENXML(@hXMLInsertDoc,'/Root/test') NOTE: This is where the error occurs. </li></ol>

Workaround
You must define the user-defined function to insert the intermediate rowset into a temporary table, as follows: CREATE FUNCTION dbo.udfTestOPENXML (@iDocID INT, @sXPath VARCHAR(8000)) RETURNS @tbl TABLE (          Value1 VARCHAR(10),           Value2 VARCHAR(10)           ) AS BEGIN INSERT @tbl SELECT * FROM OPENXML (@iDocID, @sXPath) WITH (      Value1  VARCHAR(10),       Value2  VARCHAR(10)     )

RETURN END

Additional query words: streaming result set XML pipe command input output

Keywords: kbprb KB279078

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.