Can any one help me with this SQLXML 3.0 SP3 Problem?

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.
--
Grant Smith
A+, Net+, MCP x 2Try a semicolon after the password.
Rick Sawtell
MCT, MCSD, MCDBA
"Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
message news:4Yudf.327077$084.13767@.attbi_s22...
> 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.
> --
> Grant Smith
> A+, Net+, MCP x 2|||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
"Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
message news:4Yudf.327077$084.13767@.attbi_s22...
> 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.
> --
> Grant Smith
> A+, Net+, MCP x 2|||John Bell wrote:
> 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 a
t
> http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
> John
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
Thanks 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.
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Rick Sawtell wrote:
> Try a semicolon after the password.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I added a semicolon as you directed and still got the same results.
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Rick Sawtell wrote:
> Try a semicolon after the password.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I tried this and got the same results.
Any more ideas?
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||John Bell wrote:
> 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 a
t
> http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
> John
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I 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?
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Hi
If the connection string
PROVIDER=SQLOLEDB. 1;SERVER=(local);DATABASE=database;UID=u
sername;PWD=passwo
rd;
does not work, then you may want to check conectivity in general
through query analyser.
John
Grant Smith - eNVENT Technologies wrote:
> Rick Sawtell wrote:
> I added a semicolon as you directed and still got the same results.
> --
> Grant Smith
> A+, Net+, MCP x 2
> Quality Production Liaison
> Hewlett Packard Company
> Database Administrator
> Renaissance Systems and Services, LLC