Solved

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

Posted on 2003-11-26
7
468 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
  • 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 500 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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