Solved

Impossible to execute sqlxmlbulkload in a SQL SERVER 2005 CLR

Posted on 2008-10-29
1
1,044 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
ID: 22839768
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

26 Experts available now in Live!

Get 1:1 Help Now