Solved

Impossible to execute sqlxmlbulkload in a SQL SERVER 2005 CLR

Posted on 2008-10-29
1
1,046 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

785 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