Re: ADO OpenSchema( adSchemaPrimaryKeys , ...)

Pete d'Oronzio [pdmagic] wrote:
Hi all,

I've got an app that uses ADO.OpenSchema() extensively during a
process that identifies DB schema changes between versions of my
application. It generates an SQL script to update the current
database structure to be compatible with the latest version.

A recent change that needed to be made involved a Primary Key /
Contstraint change. This produced an error that can be boiled down
to this:

Create a table with some columns / drop a couple of columns /
reCreate those columns / create a Primary Key Constraint. Result:
ADO reports incorrect columns when using OpenSchema(
adSchemaPrimaryKeys );

Note that this same process, when run against Oracle, works fine.

Thanks,

-Pete


Client system: Windows XP SP2
Server system: Windows 2003 + MS SQL Server 2000

Here's code to reproduce:

-- Create "Report" table
CREATE TABLE TestReport (
UserGroupID INT,
UserID INT,
ReportID INT NOT NULL
);

-- Drop columns from "Report" table
ALTER TABLE TestReport DROP COLUMN UserGroupID;
ALTER TABLE TestReport DROP COLUMN UserID;

-- Add columns to "Report" table
ALTER TABLE TestReport ADD
UserGroupID INT DEFAULT -1 NOT NULL,
UserID INT DEFAULT -1 NOT NULL,

Syntax error here: remove the last comma (or replace it with a semicolon).


-- Add primary key to "Report" table
ALTER TABLE TestReport ADD CONSTRAINT PKTestConstraint PRIMARY
KEY(UserGroupID,UserID,ReportID);

Good job documenting this. I've been able to reproduce this in SQL 2005
using both the SQLOLEDB and SQLNCLI providers. This code:

dim cn,rs, fso,ts,s
set cn=createobject("adodb.connection")
on error resume next
cn.open "provider=SQLNCLI;data source=commandcentral;" & _
"integrated security=sspi;initial catalog=test"
if err<>0 then
msgbox err.number & ": " & err.description,,"Connection Failure")
end if
set rs=cn.openschema(28,array(empty,empty,"TestReport"))
if err<>0 then
msgbox err.number & ": " & err.description,,"OpenSchema Error"
else
if not rs.eof then s=rs.getstring
rs.close
set fso=createobject("scripting.filesystemobject")
set ts=fso.opentextfile("schema.txt",2,true)
ts.write s
ts.close
end if
cn.close

Produces this result:
test dbo TestReport ReportID 3 PKTestConstraint


I don't have an answer for you but I will run it by my fellow MVPs and see
if they can shed some light.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.