I'm having a problem with SQLXML. I have written a small VB.NET
application to manipulate xml files and import them into an SQL Server
database.

Every time I run the application, the import fails. The error log
contains the following xml:

<code>

<?xml version="1.0"?>
<Result State="FAILED">
    <Error>
        <HResult>0x80004005I32</HResult>
        <Description><![CDATA[Error connecting to the data
source.]]></Description>
        <Source>XML BulkLoad for SQL Server</Source>
        <Type>FATAL</Type>
    </Error>
</Result State>

</code>

I'm at a complete loss as to what's going on here. Any input would be
greatly appreciated. I've included most of my code for reference. If
anything else is needed, please let me know.

The code that is supposed to be connecting to the database and executing
the bulk transfer is as follows:

<code>

Private Function importToSQL(ByVal importXML As String)

(where importXML = C:\SQL EXCHANGE\IDS\IN\filename.xml)

    Dim noErrors As Boolean

    Dim connectionString As String = "PROVIDER=SQLOLEDB; Server=(local);
database=database; user id=username; password=password"
    Dim errorLog As String = importXML & ".errlog"
    Dim dataSchema As String = "C:\SQL EXCHANGE\IDS\IN\IDS XML Importer\IDS
XML Importer.xsd"

    Dim bulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3

    Try

        bulkLoad.KeepIdentity = False
        bulkLoad.KeepNulls = True
        bulkLoad.ErrorLogFile = errorLog
        bulkLoad.ConnectionString = connectionString
        bulkLoad.Execute(dataSchema, importXML)

    Catch ex As Exception

        noErrors = False

    End Try

End Function

</code>

Lastly, a snippet of the .xsd file that I'm using to show that its
structure:

<code>

<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
    <xsd:element name="File">
        <xsd:complexType>
            <xsd:choice maxOccurs="unbounded">
                <xsd:element name="CLAIM" sql:relation="IMPORT_IHS_DENTAL_CLAIMS">
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element name="CLAIM_NUM" type="xsd:string"
sql:field="CLAIM_NUM" />
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                            <xsd:element name="_240_TREATMENT_ZIP" type="xsd:string"
sql:field="_240_TREATMENT_ZIP" />
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:choice>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

</code>

Thanks in advance.

Try a semicolon after the password.

Rick Sawtell
MCT, MCSD, MCDBA


message news:4Yudf.327077$084.13767@attbi_s22...
Show quoteHide quote

Rick Sawtell wrote:
I added a semicolon as you directed and still got the same results.

Hi

If the connection string

PROVIDER=SQLOLEDB.1;SERVER=(local);DATABASE=database;UID=username;PWD=password;

does not work, then you may want to check conectivity in general
through query analyser.

John

Grant Smith - eNVENT Technologies wrote:
Show quoteHide quote

Rick Sawtell wrote:
I tried this and got the same results.

Any more ideas?

Hi

The error implies that  you have not connected correctly to your database.
Make sure that your connection string is correct. Check out the examples at
http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp

John

message news:4Yudf.327077$084.13767@attbi_s22...
Show quoteHide quote

John Bell wrote:
Show quoteHide quoteThanks for the input John, but I changed my connection string to match
PerfecXML (which is what I started with, i might add) save for making
UID and PWD appropriate for my server, and I still got the same results.

John Bell wrote:
Show quoteHide quoteI changed my connection string to exactly what PerfectXML had as an example (by the way, I started with this connection string originally) save for
the username and password values and still got the same results.

Anything else you can think of?

Stored Procedure Problem (Bug?)
SP succeeds but returns error
using UDF in 'IN' clause
The uninstall of sql server 2005 beta and visual studio 2005 is a mess
Consecutive values