?
Solved

Impossible to execute sqlxmlbulkload in a SQL SERVER 2005 CLR

Posted on 2008-10-29
1
Medium Priority
?
1,067 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

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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