MS SQL XML - SQLXML Bulk Load - Server cannot load DCOM

Author

SQLXML Bulk Load - Server cannot load DCOM

Paul Hunter

2005-11-01, 9:23 am

Hi,

I am having trouble importing an XML file using Bulk Load in the SQL
XML object. This was working fine on Sunday but unfortunately I now get
the "Server cannot load DCOM" error everytime I call .execute from a
SQLXMLBulkLoad3 object.

I am fairly sure I haven't installed any microsoft patches or upgrades
since Sunday and I don't think I have installed/un-installed any
software either.

It sounds like the kind of problem that would normally be resolved in
the dcomcnfg admin form but I am unable to locate anything remotely
SQLXML based in the available list.

I thought maybe it was throwing an error trying to do the import but
was giving me a very un-useful error message so I simplified down my
import to one table and 2 fields but I still get the error. I can
reproduce the problem with a VB executable and 1 SQL table. (see code
below)

I am running SQL2000 SP4 on Windows 2000 SP4.

Any help would be much appreciated as I have been unable to find any
answers online and I am thinking about a re-install. If I have left
out any relevant information then please let me know and I will be
happy to provide it.

Kind Regards

Paul Hunter

Below is the code I used to replicate the problem:

-- SQL TABLE --
CREATE TABLE dbo.order1
(
orderidentifier varchar(80),
country_id int
)

-- SCHEMA XSD --
<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Order" sql:relation="Order1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Country_ID" />
</xsd:sequence>
<xsd:attribute name="OrderIdentifier" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

-- VB CODE --
InputPath = "C:\XML\Files"
InputFileName = "test.xml"
SchemaFilesPath = "C:\XML\Schema"
SchemaFile = "test.xsd"
ConnectionString = "provider=SQLOLEDB.1;Integrated
Security=SSPI;Persis
t Security Info=False;Initial
Catalog=TRADES_EX;Da
ta Source=dell-9thdt0j"

Set oXML = New SQLXMLBulkLoad3
Set ofso = New FileSystemObject

oXML.ConnectionString = ConnectionString
oXML.ErrorLogFile = ofso. BuildPath(InputPath,
"ImportErrors.txt")
oXML.KeepIdentity = False

oXML.Execute ofso. BuildPath(SchemaFile
sPath, SchemaFile),
ofso. BuildPath(InputPath,
InputFileName)

Set oXML = Nothing
Set ofso = Nothing

Bertan ARI [MSFT]

2005-11-02, 3:23 am

Could you please post the full error description including the error source?

It would also be worthwile to check if you can succesfully create a
connection and execute a simple query on the same server. I am not familiar
with this error but it looks like it is pointing out an error in the system.
Bulkload does not generate such an error.

Thanks
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.

"Paul Hunter" <pjhunter@gmail.com> wrote in message
news:1130858104.586504.118220@g49g2000cwa.googlegroups.com...
> Hi,
>
> I am having trouble importing an XML file using Bulk Load in the SQL
> XML object. This was working fine on Sunday but unfortunately I now get
> the "Server cannot load DCOM" error everytime I call .execute from a
> SQLXMLBulkLoad3 object.
>
> I am fairly sure I haven't installed any microsoft patches or upgrades
> since Sunday and I don't think I have installed/un-installed any
> software either.
>
> It sounds like the kind of problem that would normally be resolved in
> the dcomcnfg admin form but I am unable to locate anything remotely
> SQLXML based in the available list.
>
> I thought maybe it was throwing an error trying to do the import but
> was giving me a very un-useful error message so I simplified down my
> import to one table and 2 fields but I still get the error. I can
> reproduce the problem with a VB executable and 1 SQL table. (see code
> below)
>
> I am running SQL2000 SP4 on Windows 2000 SP4.
>
> Any help would be much appreciated as I have been unable to find any
> answers online and I am thinking about a re-install. If I have left
> out any relevant information then please let me know and I will be
> happy to provide it.
>
> Kind Regards
>
> Paul Hunter
>
> Below is the code I used to replicate the problem:
>
> -- SQL TABLE --
> CREATE TABLE dbo.order1
> (
> orderidentifier varchar(80),
> country_id int
> )
>
> -- SCHEMA XSD --
> <?xml version="1.0" encoding="utf-8"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Order" sql:relation="Order1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Country_ID" />
> </xsd:sequence>
> <xsd:attribute name="OrderIdentifier" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> -- VB CODE --
> InputPath = "C:\XML\Files"
> InputFileName = "test.xml"
> SchemaFilesPath = "C:\XML\Schema"
> SchemaFile = "test.xsd"
> ConnectionString = "provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persis
t Security Info=False;Initial
> Catalog=TRADES_EX;Da
ta Source=dell-9thdt0j"
>
> Set oXML = New SQLXMLBulkLoad3
> Set ofso = New FileSystemObject
>
> oXML.ConnectionString = ConnectionString
> oXML.ErrorLogFile = ofso. BuildPath(InputPath,
"ImportErrors.txt")
> oXML.KeepIdentity = False
>
> oXML.Execute ofso. BuildPath(SchemaFile
sPath, SchemaFile),
> ofso. BuildPath(InputPath,
InputFileName)
>
> Set oXML = Nothing
> Set ofso = Nothing
>

Paul Hunter

2005-11-02, 3:23 am

Hi Bertan,

Thanks for responding.

The message I get back in visual basic is:

Run-time error '-2147467259' (80004005):
The server could not load DCOM.

The error source is: Microsoft OLE DB Provider for SQL Server

This is the information that is output in the error file from the
..execute command:
<?xml version="1.0"?>
<Error>
<Record>
<HResult>0x80004005</HResult>
<SQLState>42000</SQLState>
<NativeError></NativeError>
<ErrorState>2</ErrorState>
<Severity>16</Severity>
<Source>Microsoft OLE DB Provider for SQL Server</Source>
<Description><![CDATA[The server could not load DCOM.]]></Description>
</Record>
</Error>

I have used the connection string to create a connection and return
data to a recordset to verify that the connection string is valid.

Any thoughts?

Kind Regards

Paul Hunter

Paul Hunter

2005-11-02, 7:23 am

Hi,

After much frustration I created a virtual machine on my pc and set up
vb and tried out hte above logic connecting to the sql server on my
real machine. I had some trouble getting the users working but
eventually I got it importing fine.

This got me thinking about users and whether something was up with my
authentication. Back on my real machine I changed the connection
string to use a sql user I had just set up. Eveything worked fine.
Then on the off chance that some piece of magic had occured I changed
the connection string back to use SSPI (widows auth) it again worked
fine.

To say I am confused is an understatement, to say I am happy that its
fixed is also.

Thanks for your time with this anyway.

Kind Regards

Paul Hunter

Paul Hunter

2005-11-02, 7:23 am

A bit more information that makes no sense to me.

After working a bit more I got the same message again, nothing had
changed as I had done exactly the same on the previous debugging run.
Rebooting has no effect on this as I rebooted several times and still
got the message. Changing the connection string on my machine to use a
different user did not solve the problem either.

I then started up my virtual machine, ran my previous test program
(that used a different user) and it ran through okay. As soon as I
went back to my real machine I no longer got the message.

Colour me confused ;o)

I don't know if the above is helpful to you Bertan, but I am worried
about it happening on a live production installation. Thanks for
taking an interest.

Kind Regards

Paul Hunter

Sponsored Links