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
            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.BulkLoad = True
            bulkXML.Execute(XMLSchemaFilePath, XMLFilePath)

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

I learned this from Gary here:
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!

Wednesday, August 11, 2010

Compact and Repair on Access 2010 64-bit

I recently had some issues using Compact and Repair while using Access 2010 (64-bit version). I couldn't get my database to compact and repair, and kept getting the following error message:

Could not use 'C:\temp\myDB.mdb'; file already in use.

Of course Access help provided nothing, so hopefully this will help you. Turns out that you need to have EXCLUSIVE ACCESS to the file, so it is guaranteed that no one else is using it, in order to compact and repair it. Here is how you do that:

  1. Open Access, but not a specific database.
  2. On the File tab, click Open.
  3. Navigate your file system to the database you want.
  4. Instead of clicking the Open button, click the dropdown on the button.
  5. Choose Open Exclusive.
  6. With your database now open, choose File, and then Compact and Repair.
This allows you to have Exclusive access to the database, so running Compact and Repair doesn't affect some other user of the database. Of course, other users will have to be logged off of the database for you to have exclusive access.

Hope this helps some user out there...