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

calling Stored Procedures from different DBs

Is it possible to have a stored procedure in database A while calling
it from database B and have it manipulate the tables in database B
(whatever the calling database happens to be)?

We have a large-scale app that uses many complex stored procedures,
and as of now, we're copying the SPs to every new database that is
created, and it will soon become a nightmare for propagating updates
and fixes. We'd like to keep a master set of the SPs in one DB and
"use" them from other DBs so that they only query data and manipulate
tables in the calling DB. I hope someone has some suggestions. Thanks.ZeBerg (adam@.alumni.northwestern.edu) writes:
> Is it possible to have a stored procedure in database A while calling
> it from database B and have it manipulate the tables in database B
> (whatever the calling database happens to be)?
> We have a large-scale app that uses many complex stored procedures,
> and as of now, we're copying the SPs to every new database that is
> created, and it will soon become a nightmare for propagating updates
> and fixes. We'd like to keep a master set of the SPs in one DB and
> "use" them from other DBs so that they only query data and manipulate
> tables in the calling DB. I hope someone has some suggestions. Thanks.

While this is possible, this will bring out of the frying pan, and into
the fire.

You would have to use dynamic SQL to manipulate the tables, and then
many of the advantages of stored procedures would go out the window.

OK, so there is the possibility of adding your own system procedures in
the master database, but this is unsupported, has security issues and
and I am not even sure whether it works with next version of SQL Server.

And neither of these schemes works the day you decide to scale out and
have databases one more than one server. Or the day you want to a test
version of the app running against a test database on the same server.

So what is left? Do as you do know, that is the way to go. What you
apparently need, is to invest time in how to deploy changes in a
controlled way. A foundation for this is to use a version-control
system. It is from the VCS you build update scripts to deploy the
changes. You might higher levels of sophistication, and there are
a number of third-party tools out there which aim specifically at
configuration management with SQL Server.

Since my shop in the same as yours - except that our multiple databases
are both in-house and remote customer sites - we have developed our
own routines, and the tool we use is actually available as freeware,
see http://www.abaris.se/abaperls/.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp