sp_OACreate is a way to instantiate OLE / COM objects inside of SQL Server in the MemToLeave area of the server’s address space, or out of process in dllhost.exe. Whenever you instantiate a COM object using sp_OACreate method you need to destroy the objects created else it might run out of memory causing the application to fail because SQLMemToLeave is a small workspace i,e (384MB on SQL Server 2000). You might experience the following symptoms if you are using sp_OACreate to load large XML
When we try to read a large XML in SQL Server using extended stored procedures (sp_oaCreate) it shows incorrect values after certain nodes. The Script works fine if we reduce the number of elements in the source XML.
Add the following error handling code to find the exact error why we are failing.
IF @intReturn <>0
Declare @iMessageObjId int
Declare @vcErrSource Varchar(100)
Declare @vcErrDescription varchar (100)
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
print 'MSGID: ' + CAST(@iMessageObjId as Char)
print 'SRC: ' + @vcErrSource
print 'DESC: ' + @vcErrDescription
Print 'HR: ' + cast(@intReturn as char)
We were able to find the following error in the result window
OLE Automation Error Information
Source: ODSOLE Extended Procedure
@hr = 10077
This happens when you do not destroy the Objecttokens returned by sp_OAMethod
In order to resolve this we need to run sp_OADestroy on the objecttokens returned by sp_OAMethod.
SQL Server Books Online states that, sp_OADestroy is used on the objects created by sp_OACreate method. We also found that it is safe to pass the Objecttoken’s as the input parameters for the sp_OADestroy returned by sp_OAMethod.
(Where objecttoken is the OLE object that was created by using
Here is the sample script on how to read the XML file in SQL Server.
Use of Microsoft XML (MSMXL) inside a SQL Server stored procedure through the SQL Server OLE automation stored procedures (sp_OACreate, etc...), will result in memory leak inside of SQL Server which is discussed in this article (
Microsoft does not recommend that you use MSXML inside of a SQL Server stored procedure via the SQL Server ole automation stored procedures
Reading XML inside the SQL Server using extended stored procedures is the old 6.5 way of doing xml. If you are developing new projects then we need to avoid using sp_OACreate method. You could try using
SQL 2000 - sp_xml_preparedocument / sp_xml_removedocument to shred XML documents within SQL Server
SQL 2005 – new FOR XML with XPath query syntax support, XML data type
SQL 2008 – improved XML support since SQL 2005, Xquery syntax enhancements
Sample script to read xml using sp_xml_preparedocument
DECLARE @xmlDoc NVARCHAR(400)
DECLARE @handle INT
SET @xmlDoc = N'
<au_title>XML Developer''s Guide</au_title>
<au_description>An in-depth look at creating applications with XML.</au_description>