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]CREATE 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]CREATE 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]CREATE 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 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]CREATE 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!
>
|||> 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]CREATE 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!
>