?
Solved

Is this a bug in ADOX or in SQL Server or in ADODB ??

Posted on 2003-11-26
7
Medium Priority
?
474 Views
Last Modified: 2013-11-27
Win2k, ADO 2.80, SQL Server 2000

This is the ASP code:
<%
Option Explicit
Response.Buffer = False


Dim oConn, sDSN, oCatalog, oKeys, oColumns, element, subelement

' crate ADODB Connection
sDSN = "PROVIDER=SQLOLEDB;DATA SOURCE=127.0.0.1;USER ID=sa;PASSWORD=;DATABASE=tivolirooster;"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open sDSN

' Create ADOX catalog
Set oCatalog = Server.CreateObject("ADOX.Catalog")
oCatalog.ActiveConnection = oConn

' get the keys of a table which has a primary key (on an identity field) - also some foreign keys are present.
Set oKeys = oCatalog.Tables.Item("performance").Keys

For each element In oKeys
    Response.write "<hr>" & element.Name & " (" & element.Type & ")<br>" & CHR(10)
    Set oColumns = element.Columns
    For each subelement in oColumns   '----- error on this line
        Response.write subelement.Name & "<br>" & CHR(10)
    Next
Next
%>

============================
Error:
Microsoft VBScript runtime error '800a0cb3'
Unknown runtime error
demobug.asp, line 23
============================
But, this is only an eror if oKeys.element.Type = 1 (primary key). For foreign keys it runs fine.

The same code runs completely without problems on Access.

questions:
- is this a bug in ADO?
- is there some way to avoid the bug (different connectionstring, upgrade - but i have the latest ADO installed)
0
Comment
Question by:sybe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9825772
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9825793
I assume that you have done your round of snooping around.. eh? :o)

Cheers!!
0
 
LVL 21

Accepted Solution

by:
ap_sajith earned 2000 total points
ID: 9825873
Go through this as well.. doesnt make much sense to me.. though..

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B294157

Cheers!!
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 21

Expert Comment

by:ap_sajith
ID: 9825956
hmmm.. didnt go through your entire post above... reasd through the kb article posted above..

>>CAUSE
To retrieve columns used by the primary key, ADOX uses the IDBSchemaRowset::GetRowset method with DBSCHEMA_KEY_COLUMN_USAGE, which is not supported by the SQL Server OLE DB provider (SQLOLEDB) provider and is supported only by the latest version of the Jet OLE DB Provider which is installed with the latest version of the Jet Service Pack. <<

>>STATUS
This behavior is by design. <<

why do microsoft alway piss us off with the above line... behaviour is by design...

Sorry Sybe, I guess SQL doesnt support the method....

Cheers!!
0
 
LVL 28

Author Comment

by:sybe
ID: 9830294
Your link to the ms site hits the nail exactly on the head. Thanks.
0
 
LVL 28

Author Comment

by:sybe
ID: 9830383
for anyone's information:

There is another way to get the columns in the Primary Key of a table.

All primary keys in a database are listed in a recordset using:
Set oRS = oConn.OpenSchema(28)

In the resulting recordset also fields for the name of the table and the name of the primary key are listed. Which is exactly what i needed.
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9830408
Thanks for the points and thanks for the info..

Cheers!!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 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