Multithreaded SQLXmlBulkLoad Handle Leak...

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Dan Bass
    #1

    Multithreaded SQLXmlBulkLoad Handle Leak...

    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 bulkLoadComObje ct As SQLXMLBULKLOADL ib.SQLXMLBulkLo ad4 = Nothing
    Try

    bulkLoadComObje ct = New SQLXMLBULKLOADL ib.SQLXMLBulkLo ad4
    bulkLoadComObje ct.ConnectionSt ring = _connectionStri ng
    bulkLoadComObje ct.KeepIdentity = False

    bulkLoadComObje ct.Execute(sche maFilename, tempFilename)

    Finally

    ' clear up
    If (Not bulkLoadComObje ct Is Nothing) Then
    System.Runtime. InteropServices .Marshal.Releas eComObject(bulk LoadComObject)
    End If

    bulkLoadComObje ct = Nothing

    File.Delete(tem pFilename)
    End Try



    Tags: None
    • Dan Bass
      #2
      Re: Multithreaded SQLXmlBulkLoad Handle Leak...


      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_Inse rt", 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_prepared ocument @pointer OUTPUT, @xml

      INSERT INTO [Financial_Heade r]
      ([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.



      "Dan Bass" <(danjbass at gmail.com)wrote in message
      news:OQMGmlW7IH A.4988@TK2MSFTN GP04.phx.gbl...
      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 bulkLoadComObje ct As SQLXMLBULKLOADL ib.SQLXMLBulkLo ad4 =
      Nothing
      Try
      >
      bulkLoadComObje ct = New SQLXMLBULKLOADL ib.SQLXMLBulkLo ad4
      bulkLoadComObje ct.ConnectionSt ring = _connectionStri ng
      bulkLoadComObje ct.KeepIdentity = False
      >
      bulkLoadComObje ct.Execute(sche maFilename, tempFilename)
      >
      Finally
      >
      ' clear up
      If (Not bulkLoadComObje ct Is Nothing) Then
      >
      System.Runtime. InteropServices .Marshal.Releas eComObject(bulk LoadComObject)
      End If
      >
      bulkLoadComObje ct = Nothing
      >
      File.Delete(tem pFilename)
      End Try
      >
      >
      >

        Comment

        Previous template Next
        Working...