SQL XML Bulk Load VB Web Service Error

[Andy Hunt]

Hi,

I am having a problem with SQLXML Bulk Load inside of a web service ( also
inside of a basic aspx page). I have a WebMethod in a web service that
makes a call to bulk load. This works perfectly on my local test machine (it
has VS 2005, SQL Server 2005 Ent, SQLXML 4.0 Libs and standard XP SP2 IIS)
also for reference the same code works perfectly on a basic one page aspx
file (with code behind). However when this web service or simple page are
moved over to the live server (Windows Server 2003 Standard, IIS,SQLXML 4.0
Libs) the bulk load falls over. Presenting this error

"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))."

The code is written in VB, and is detailed as follows. I have gone down the
route of exploring threading (which is suggested as an issue that may cause
this in C#) and locking the application to one thread. However this has not
helped. I have even tried installing a full version on SQL Server 2005 on
the web server, still not change. I am
at a loss as to why this is happening as it works flawlessly on my dev
machine. (All files and schemas are well formed as they work on the dev
machine)

Additionally I have changed permissions on IIS and am 99% sure it is not a
permissions issue.

Here is the code for reference; its basic but should do the job.

**WebService Snippet**
Dim FGuid As Guid = Guid.NewGuid

XML_OL.Save(Server.MapPath("App_Data") + "\" + FGuid.ToString + ".xml")

Dim ObjXML As SQLXMLBULKLOADLib.SQLXMLBulkLoad4 = New
SQLXMLBULKLOADLib.SQLXMLBulkLoad4

ObjXML.ConnectionString = SQLConBL

ObjXML.ErrorLogFile = Server.MapPath("App_Data") + "\error.log"

ObjXML.KeepIdentity = False

ObjXML.Execute(Server.MapPath("App_Data") + "\InsertDBContacts.XSD",
Server.MapPath("App_Data") + "\" + FGuid.ToString + ".xml")

File.Delete(Server.MapPath("App_Data") + "\" + FGuid.ToString + ".xml")
**Test One Page Snippet**
Dim ObjXML As SQLXMLBULKLOADLib.SQLXMLBulkLoad4 = New
SQLXMLBULKLOADLib.SQLXMLBulkLoad4

ObjXML.ConnectionString = "Provider=SQLOLEDB;server=xxx;database=xxx;use r
id=xxx;password=xxxx;Trusted_Connection=False;"

ObjXML.ErrorLogFile = "error.log"

ObjXML.KeepIdentity = False

ObjXML.Execute("C:\InsertDBContacts.XSD", "c:\1ib.xml")
Any ideas?
Regards,
Andy.

[igorelik]

Hi,

I am having exactly same problem as you are. Did you have any luck figuring it out?

Would greatly appreciate it.

Thank you,

Ilya

[igorelik]

Try using Server.CreateObject("SQLXMLBulkLoad.SQLXMLBulkload .4.0")

It worked for me.