Solved

Impossible to execute sqlxmlbulkload in a SQL SERVER 2005 CLR

Posted on 2008-10-29
1
1,059 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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