Multithreaded SQLXmlBulkLoad Handle Leak...

[Dan Bass]

Using:
.Net (happens to be VB but same with C#) 2.0
SQLXML4
SQL Server 2005

I've got a multithreaded .Net application that uses the SQLXmlBulkLoad call
and I'm not convinced the COM object is being released properly. The code is
also called often and I'm not sure if I should be creating the object each
time, or reusing it. I've tried reusing it (one object for all threads with
a lock or one object per thread), but because of the multithreaded nature of
the app, can't get this to work. Each thread is STA...

The Handle count keeps increasing in the application, and eventually it
crashes and I've narrowed it down to the SQL XML Bulk Load bit.

Any help would be appreciated.


The code for this is here:

' load up the SQLXML's bulkload object
Dim bulkLoadComObject As SQLXMLBULKLOADLib.SQLXMLBulkLoad4 = Nothing
Try

bulkLoadComObject = New SQLXMLBULKLOADLib.SQLXMLBulkLoad4
bulkLoadComObject.ConnectionString = _connectionString
bulkLoadComObject.KeepIdentity = False

bulkLoadComObject.Execute(schemaFilename, tempFilename)

Finally

' clear up
If (Not bulkLoadComObject Is Nothing) Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(bulkLoadComObject)
End If

bulkLoadComObject = Nothing

File.Delete(tempFilename)
End Try

 

[Dan Bass]

It appears as though this method of loading XML into a relatiocal schema is
not being pushed by Microsoft.
Why else exclude the bulk load from the SQLXml .Net library?
Why else have no reliable way of doing this basic operation with no leaks?

I've decided to move the XML shredding to OPENXML in my Stored Procedures,
dynamically building the stored procedure name from my .Net code based on
the root element of the XML I'm parsing. I.E. If the root is "Financial",
then I add "_Insert" to give me a SP name of "Financial_Insert", which I
create with one xml parameter.


for xml:
<Financial>
<Item>
<Field1>10/10/2008 00:00:00</Field1>
<Field2>Some Text</Field2>
<Field3>7</Field3>
</Item>
<Item>
<Field1>11/10/2008 00:00:00</Field1>
<Field2>Some Text2</Field2>
<Field3>8</Field3>
</Item>
<Item>
<Field1>12/10/2008 00:00:00</Field1>
<Field2>Some Text3</Field2>
<Field3>9</Field3>
</Item>
</Financial>


SP would contain:
declare @pointer int

-- @xml is the SP parameter
execute sp_xml_preparedocument @pointer OUTPUT, @xml

INSERT INTO [Financial_Header]
([Field1]
,[Field2]
,[Field3])

SELECT [Field1]
,[Field2]
,[Field3]

-- ,on openxml 2 = get the node value
-- 1 = get the attribute value
FROM OPENXML ( @pointer, '/Financial/Item', 2 )

WITH
(
[Field1] [datetime],
[Field2] [nvarchar](50),
[Field3] [int],
)



Hopefully this helps someone.