Solved

Impossible to execute sqlxmlbulkload in a SQL SERVER 2005 CLR

Posted on 2008-10-29
1
1,039 Views
Last Modified: 2013-11-07
Hi,
I would like to create a stored procedure in SQL SERVER 2005 by using .NET and CLR possibilities.
The goal of the stored procedure is to load XML data with SQLXMLBULKLOAD.
I succeed to create the stored procedure CLR in the database but when i execute it, the following error appears :

Msg 6522, Level 16, State 1, Procedure xmlbulk, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "xmlbulk":
System.InvalidCastException: Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).
System.InvalidCastException:
   at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ErrorLogFile(String pbstrFileName)
   at StoredProcedures.XMLBULK()

This message appeared when i added in my program the following line :
System.Threading.Thread.CurrentThread.SetApartmentState(Threading.ApartmentState.STA)

Indeed, without this line, i add an other kind of error :

Msg 6522, Level 16, State 1, Procedure xmlbulk, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "xmlbulk":
System.InvalidCastException: Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
System.InvalidCastException:
   at SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class.set_ErrorLogFile(String pbstrFileName)
   at StoredProcedures.XMLBULK()

I thought the sqlxmlbulload didn't work because he was using multithread in this program and the sqlxmlbulkload can't use a multithread that's why i tried to force the use of a simple thread.

I'm beginner in .Net and i don't understand very well the concept of thread.
I'm a SQL Developper and i'm sorry if i'm not clear.
I you have an idea, thank's a lot to help me.




Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Xml

Imports SQLXMLBULKLOADLib

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

 

 

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub  XMLBULK ()

        Dim objBL As New SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class

        'Dim reader As SqlDataReader

        'Dim MSG_XML As New XmlDocument

 

        'Using connection As New SqlConnection("context connection=true")

        'connection.Open()

        'Dim command As New SqlCommand("SELECT MSG_XML FROM B2B_P1..B2B_ORDER_REPLY_JPM WHERE T_ID=1", connection)

        'reader = command.ExecuteReader()

        'reader.Read()

        'MSG_XML.LoadXml(CStr(reader("MSG_XML")))

        'reader.Close()

        'End Using

 

        'Positionnement en simple thread 

        'System.Threading.Thread.CurrentThread.SetApartmentState(Threading.ApartmentState.STA)

        ' Execute bulk load. Read source XML data from the stream.

        objBL.ErrorLogFile = "C:\test_xmlbulk\logxmlbulk.txt"

        objBL.ConnectionString = "provider=SQLOLEDB.1;data source=ITWS0026;database=B2B_P2;uid=xxx;pwd=xxx"

        objBL.SchemaGen = False

        objBL.SGDropTables = False

        objBL.Transaction = False

        objBL.CheckConstraints = False

        objBL.BulkLoad = True

        objBL.KeepIdentity = False

        objBL.Execute("C:\test_xmlbulk\SchemaOrderList.xml", "C:\test_xmlbulk\export.txt")

        'While System.Runtime.InteropServices.Marshal.ReleaseComObject(objBL) <> 0

        'Threading.Thread.Sleep(50)

        'End While

        objBL = Nothing

        'MSG_XML = Nothing

    End Sub

End Class

Open in new window

0
Comment
Question by:JPM62
1 Comment
 

Accepted Solution

by:
JPM62 earned 0 total points
Comment Utility
I found the solution, it was just because 'System.Threading.Thread.CurrentThread.SetApartmentState(Threading.ApartmentState.STA) wasn't at the good place, it works like this :

    Public Shared Sub  XMLBULK ()
        'Positionnement en simple thread
        System.Threading.Thread.CurrentThread.SetApartmentState(Threading.ApartmentState.STA)
        Dim objBL As New SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class
        ...

So, did i win the 500 points?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now