• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Access SQL 2005 XML type data via ADO 2.8 or below

Can we work with ADO in VB6 to access sql 2005 xml type data?
  • 2
  • 2
1 Solution
SELSupportAuthor Commented:
Actully I have a query below:

select ref.value('@id', 'varchar(20)') p0,ref.value('@comp', 'varchar(20)') p1, ref.value('@barc3', 'varchar(20)') p2
from Table_xml cross apply xmlcol.nodes('TraceabilityData/charge') as Tab(ref)
where id=6 order by p0

I can run it through QA (sql analyzer),but when I run it in VB6 with ADO 2.7, with a connect string:

cnn.Open "Provider=SQLNCLI;Trusted_Connection=yes;Database=" & DataSource & ";Server=" & Servername, cnn, adOpenStatic

An error occured, said " run-time error '-2147217900(80040e14)' ".

Instead if it's a simple select statement like this:

select * from Table_xml where id=6

no problem to get all xml formatted data.

Please advise me. Thanks.
Anthony PerkinsCommented:
Consider instead using the connection string from the article that leonstryker posted earlier.
SELSupportAuthor Commented:
I tried, just got the same error. Here is the details:

Run-time error '-2147217900(80040e14)':

SELECT failed beacuse the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexs on computed columns and/or query notifications and/or xml data type methods.

I was wondering if ADO 2.8 or below supports xml data methods in sql 2005? (saw somewhere mentioned ADO.net 2.0 is fine, but our integration tool is based on VB6/ADO. Just want to confirm this with someone.)

Anthony PerkinsCommented:
If you actually tried with leonstryker's connection string, than I am afraid I have no idea.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now