SA0060 : The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call

The topic describes the SA0060 analysis rule.

Message

The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call

Description

The rule checks for sp_xml_preparedocument procedure calls which are not followed by a call to the sp_xml_removedocument procedure.

The prepared and parsed document is stored in the internal cache of SQL Server. The MSXML parser (Msxmlsql.dll) uses one-eighth of the total memory available for SQL Server.

To avoid running out of memory, run sp_xml_removedocument to free up the memory.

How to fix

<para>Add a call to <codeInline>sp_xml_removedocument</codeInline> stored procedure to free the allocated by sp_xml_preparedocument memory.</para>

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
13 minutes per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1DECLARE @hdoc int,@hdoc1 int
 2DECLARE @doc varchar(1000)
 3SET @doc ='
 4<ROOT>
 5<Customer CustomerID="VINET" ContactName="Paul Henriot">
 6   <Order CustomerID="VINET" EmployeeID="5" 
 7           OrderDate="1996-07-04T00:00:00">
 8      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
 9      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
10   </Order>
11</Customer>
12<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
13   <Order CustomerID="LILAS" EmployeeID="3" 
14           OrderDate="1996-08-16T00:00:00">
15      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
16   </Order>
17</Customer>
18</ROOT>'
19--Create an internal representation of the XML document.
20EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>'
21EXEC sp_xml_preparedocument @hdoc1 OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>'
22
23EXEC sp_xml_removedocument @hdoc
24EXEC sp_xml_removedocument @hdoc
Analysis Results
  Message Line Column
1 SA0060 : The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call and the document referenced by the handle @hdoc1 is not released. 21 28
See Also

Other Resources

Analysis Rules