Thursday, October 28, 2010

Calling SQLXMLBulkLoad from aspx page using VB.NET

If you are using SQLXMLBulkLoad in VB.Net from an aspx page consider the following:

I was having trouble getting SQLXMLBulkLoad to load my xml file when called from a .aspx page. What I had to do was add the AspCompat="true" to the header (.aspx @ Page directive) on the aspx page.

For example, here is my header:

<%@ Page AspCompat="true" Language="vb" AutoEventWireup="false" Codebehind="MyPage.aspx.vb" Inherits="AST.Web.Directory.MyPage" %>

Adding AspCompat="true" makes the aspx page run in a Single Thread Apartment mode, so then the SQLXMLBulkLoad COM object will work correctly.

Here is an example of my code calling the SQLXMLBulkLoad object in VB.net:
          
            Dim bulkXML As SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class = New SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class
            connection = "provider=SQLOLEDB;data source=" & Server & ";database=" & Database & ";integrated security=SSPI"

            bulkXML.ConnectionString = connection
            bulkXML.ErrorLogFile = ErrorFilePath
            bulkXML.SGDropTables = False
            bulkXML.SchemaGen = True
            bulkXML.BulkLoad = False
            bulkXML.Execute(XMLSchemaFilePath)
            bulkXML.BulkLoad = True
            bulkXML.Execute(XMLSchemaFilePath, XMLFilePath)

Once I added the AspCompat="true" statement, everything worked!

I learned this from Gary here: 
http://sqlxml.org/faqs.aspx?faq=90
For anyone trying to run this in an ASP.NET page, ensure that 
ASPCompat="true" is added to the .aspx @Page directive. The lines 
declaring a new thread are not necessary.
       
Hope this saves someone some time!