Access SQL 2005 XML type data via ADO 2.8 or below

Posted on 2006-04-04
Last Modified: 2013-12-25
Can we work with ADO in VB6 to access sql 2005 xml type data?
Question by:SELSupport
    LVL 29

    Accepted Solution

    LVL 1

    Author Comment

    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.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Consider instead using the connection string from the article that leonstryker posted earlier.
    LVL 1

    Author Comment

    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 2.0 is fine, but our integration tool is based on VB6/ADO. Just want to confirm this with someone.)

    LVL 75

    Expert Comment

    by:Anthony Perkins
    If you actually tried with leonstryker's connection string, than I am afraid I have no idea.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now