Can't create Primary Key??

Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!Assuming your PK is compreised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!|||Assuming your PK is comprised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!|||Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I hav
e
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> --
> Rob
> IT guy!
>|||> I try running the query but it times out.
Where did you try running it? I suggest usng Query Analyzer, not Enterprise
Manager. You might also try
...
FROM MyTable WITH (NOLOCK)
...
http://www.aspfaq.com/
(Reverse address to reply.)|||Rob,
Try
select
Col1
, Col2
, count (*)
from
MyTable
where Col1 = 'type 6c, len 9'
group by
Col1
, Col2
having
count (*) > 1
This assumes that Col1 is the initial column of PK_master, the primary
key you are trying to set, since this value was provided in the error
message.
Steve Kass
Drew University
Rob wrote:
[vbcol=seagreen]
>Tom,
>I try running the query but it times out. The table is quite large.
>"Tom Moreau" wrote:
>
>|||You probably have no indexes on the table yet. Run it through QA and let it
finish.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1960D77C-C7C8-40C7-B927-6FC1360C26DC@.microsoft.com...
Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I
have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create[/vbcol
]
constraint.[vbcol=seagreen]
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> --
> Rob
> IT guy!
>