Microsoft KB Archive/278387

= FIX: INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION Column Reflects Incorrect Data =

Article ID: 278387

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q278387



BUG #: 58055 (SQLBUG_70)



SYMPTOMS
The ORDINAL_POSITION column in INFORMATION_SCHEMA.KEY_COLUMN_USAGE returns incorrect results. For example: create table t1 (c1 int not null, c2 int not null, c3 char(5), c4 int, c5 int, constraint pk_t1 PRIMARY KEY (c5,c4)) go create table t2 (tc1 int not null, c1 int not null, c2 int not null, c5 char(5), constraint fk_t2 FOREIGN KEY (c1,c2) references t1 (c5,c4)) go select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from information_schema.key_column_usage where TABLE_NAME in ('t1', 't2') The results are: CONSTRAINT_NAME   TABLE_NAME     COLUMN_NAME       ORDINAL_POSITION ---   --     ---       pk_t1           t1         c4            4 pk_t1          t1         c5            5 fk_t2          t2         c2            3 fk_t2          t2         c1            2 Note that the values for the ORDINAL_POSITION column are incorrect. It should return the relative position of the column in the constraint definition.

The correct results should be: CONSTRAINT_NAME   TABLE_NAME     COLUMN_NAME        ORDINAL_POSITION ---   --     ---        pk_t1            t1         c4            2 pk_t1           t1         c5            1 fk_t2           t2         c2            2 fk_t2           t2         c1            1



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Keywords: kbbug kbfix KB278387

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.