2005 will not allow me to set Primary Key again on same column

Sql Server 2005 table, a not null nvarchar column, in table design had made
column a Primary Key with "Set Primary Key" menu option.
At a later time, in table design, used "Remove Primary Key" menu option on
the column to remove having column as a Primary Key.
Now I need to make the column a Primary Key again. In table design right
click menu "Set Primary Key", I click it and then Save table to save the
change... I receive this message:
'mytablename (dbo)' table
- Unable to create index 'PK_mytablename'.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was
found for the object name 'dbo.mytablename' and the index name
'PK_mytablename'. The duplicate key value is (11).
Could not create constraint. See previous errors.
The statement has been terminated.
I am prevented from making the column a Primary Key. And I'm assuming this
is because I had previously made it a Primary Key. But - I did remove it
using the table design menu so shouldn't I be able to redo it?
I have looked all over the indexes and constraints and ran various querys
I've found from googliing and nothing turns up that confirms this error
message. Meaning I can't find any constraint or index or anything else on the
table and/or column.
I can't find anything that validates the message is correct.
How do I resolve this error and be able to successfully move past it and be
able to successfully set my column as a Primary Key?> How do I resolve this error and be able to successfully move past it and
> be
> able to successfully set my column as a Primary Key?
The duplicate key error message indicates that you have more than one row in
the table with the same proposed primary key value. You'll need to either
delete the duplicate rows or change existing data to avoid dups. A query
like the one below can help you identify the duplicate rows:
SELECT mytablename.*
FROM dbo.mytablename
JOIN (
SELECT my_key_column
FROM dbo.mytablename
GROUP BY my_key_column
HAVING COUNT(*) > 1
) AS dups ON
dups.my_key_column = mytablename.my_key_column
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:8EC0F736-1D7B-491B-83EA-6F1CD6B64035@.microsoft.com...
> Sql Server 2005 table, a not null nvarchar column, in table design had
> made
> column a Primary Key with "Set Primary Key" menu option.
> At a later time, in table design, used "Remove Primary Key" menu option on
> the column to remove having column as a Primary Key.
> Now I need to make the column a Primary Key again. In table design right
> click menu "Set Primary Key", I click it and then Save table to save the
> change... I receive this message:
> 'mytablename (dbo)' table
> - Unable to create index 'PK_mytablename'.
> The CREATE UNIQUE INDEX statement terminated because a duplicate key was
> found for the object name 'dbo.mytablename' and the index name
> 'PK_mytablename'. The duplicate key value is (11).
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I am prevented from making the column a Primary Key. And I'm assuming this
> is because I had previously made it a Primary Key. But - I did remove it
> using the table design menu so shouldn't I be able to redo it?
> I have looked all over the indexes and constraints and ran various querys
> I've found from googliing and nothing turns up that confirms this error
> message. Meaning I can't find any constraint or index or anything else on
> the
> table and/or column.
> I can't find anything that validates the message is correct.
> How do I resolve this error and be able to successfully move past it and
> be
> able to successfully set my column as a Primary Key?
>
>|||yeah it really helps when you know exactly what an error message is really
telling you ;)
thanx, you are very correct. I had no idea about the dups. So this has been
an unexpected big help. I got myself into something now :)
____
kudos to you btw. I haven't visited an MS NG in a long while and I was not
expecting anyone to post a reply anytime soon on a Saturday afternoon (CDST
anyway ;) so I really enjoy the surprise. Even more surprising to me is that
I was surprised, because having been an MVP myself back in the day I remember
the dedication and passion behind an MVP. So shame on me for so easily
forgetting the many Saturday afternoons I spent inside the confines of the
newgroups, and the dedication of those that have the MVP Award. Thank you for
your passion on this day :)
"Dan Guzman" wrote:
> > How do I resolve this error and be able to successfully move past it and
> > be
> > able to successfully set my column as a Primary Key?
> The duplicate key error message indicates that you have more than one row in
> the table with the same proposed primary key value. You'll need to either
> delete the duplicate rows or change existing data to avoid dups. A query
> like the one below can help you identify the duplicate rows:
> SELECT mytablename.*
> FROM dbo.mytablename
> JOIN (
> SELECT my_key_column
> FROM dbo.mytablename
> GROUP BY my_key_column
> HAVING COUNT(*) > 1
> ) AS dups ON
> dups.my_key_column = mytablename.my_key_column
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David" <David@.discussions.microsoft.com> wrote in message
> news:8EC0F736-1D7B-491B-83EA-6F1CD6B64035@.microsoft.com...
> > Sql Server 2005 table, a not null nvarchar column, in table design had
> > made
> > column a Primary Key with "Set Primary Key" menu option.
> > At a later time, in table design, used "Remove Primary Key" menu option on
> > the column to remove having column as a Primary Key.
> >
> > Now I need to make the column a Primary Key again. In table design right
> > click menu "Set Primary Key", I click it and then Save table to save the
> > change... I receive this message:
> >
> > 'mytablename (dbo)' table
> > - Unable to create index 'PK_mytablename'.
> > The CREATE UNIQUE INDEX statement terminated because a duplicate key was
> > found for the object name 'dbo.mytablename' and the index name
> > 'PK_mytablename'. The duplicate key value is (11).
> > Could not create constraint. See previous errors.
> > The statement has been terminated.
> >
> >
> > I am prevented from making the column a Primary Key. And I'm assuming this
> > is because I had previously made it a Primary Key. But - I did remove it
> > using the table design menu so shouldn't I be able to redo it?
> >
> > I have looked all over the indexes and constraints and ran various querys
> > I've found from googliing and nothing turns up that confirms this error
> > message. Meaning I can't find any constraint or index or anything else on
> > the
> > table and/or column.
> > I can't find anything that validates the message is correct.
> >
> > How do I resolve this error and be able to successfully move past it and
> > be
> > able to successfully set my column as a Primary Key?
> >
> >
> >
>|||Thanks for the kind words, David. I'm spending my Saturday working on a
PowerPoint for our next local SQL User Group meeting and stop by the news
groups now and then when I need a break ;-)
--
Dan Guzman
SQL Server MVP
"David" <David@.discussions.microsoft.com> wrote in message
news:84D3AF1A-2853-4C1B-907F-5DCCC692CF8F@.microsoft.com...
> yeah it really helps when you know exactly what an error message is really
> telling you ;)
> thanx, you are very correct. I had no idea about the dups. So this has
> been
> an unexpected big help. I got myself into something now :)
> ____
> kudos to you btw. I haven't visited an MS NG in a long while and I was not
> expecting anyone to post a reply anytime soon on a Saturday afternoon
> (CDST
> anyway ;) so I really enjoy the surprise. Even more surprising to me is
> that
> I was surprised, because having been an MVP myself back in the day I
> remember
> the dedication and passion behind an MVP. So shame on me for so easily
> forgetting the many Saturday afternoons I spent inside the confines of the
> newgroups, and the dedication of those that have the MVP Award. Thank you
> for
> your passion on this day :)
>
> "Dan Guzman" wrote:
>> > How do I resolve this error and be able to successfully move past it
>> > and
>> > be
>> > able to successfully set my column as a Primary Key?
>> The duplicate key error message indicates that you have more than one row
>> in
>> the table with the same proposed primary key value. You'll need to
>> either
>> delete the duplicate rows or change existing data to avoid dups. A query
>> like the one below can help you identify the duplicate rows:
>> SELECT mytablename.*
>> FROM dbo.mytablename
>> JOIN (
>> SELECT my_key_column
>> FROM dbo.mytablename
>> GROUP BY my_key_column
>> HAVING COUNT(*) > 1
>> ) AS dups ON
>> dups.my_key_column = mytablename.my_key_column
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:8EC0F736-1D7B-491B-83EA-6F1CD6B64035@.microsoft.com...
>> > Sql Server 2005 table, a not null nvarchar column, in table design had
>> > made
>> > column a Primary Key with "Set Primary Key" menu option.
>> > At a later time, in table design, used "Remove Primary Key" menu option
>> > on
>> > the column to remove having column as a Primary Key.
>> >
>> > Now I need to make the column a Primary Key again. In table design
>> > right
>> > click menu "Set Primary Key", I click it and then Save table to save
>> > the
>> > change... I receive this message:
>> >
>> > 'mytablename (dbo)' table
>> > - Unable to create index 'PK_mytablename'.
>> > The CREATE UNIQUE INDEX statement terminated because a duplicate key
>> > was
>> > found for the object name 'dbo.mytablename' and the index name
>> > 'PK_mytablename'. The duplicate key value is (11).
>> > Could not create constraint. See previous errors.
>> > The statement has been terminated.
>> >
>> >
>> > I am prevented from making the column a Primary Key. And I'm assuming
>> > this
>> > is because I had previously made it a Primary Key. But - I did remove
>> > it
>> > using the table design menu so shouldn't I be able to redo it?
>> >
>> > I have looked all over the indexes and constraints and ran various
>> > querys
>> > I've found from googliing and nothing turns up that confirms this error
>> > message. Meaning I can't find any constraint or index or anything else
>> > on
>> > the
>> > table and/or column.
>> > I can't find anything that validates the message is correct.
>> >
>> > How do I resolve this error and be able to successfully move past it
>> > and
>> > be
>> > able to successfully set my column as a Primary Key?
>> >
>> >
>> >

2005 product key

I just downloaded sql server 2005 dev edition from my msdn subscription,
however, in the listing of product keys, I dont see any keys for sql server.
Dont I need a product key for this when I install it?
moondaddy@.nospam.nospamNot for Dev edition, no.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OWi1L2X5FHA.1864@.TK2MSFTNGP12.phx.gbl...
>I just downloaded sql server 2005 dev edition from my msdn subscription,
>however, in the listing of product keys, I dont see any keys for sql
>server. Dont I need a product key for this when I install it?
> --
> moondaddy@.nospam.nospam
>|||Download the DVD edition (sorry). There was a problem with the initial CD
style distribution that incorrectly required product keys; I hope the
problem isn't that you started the download a w ago and only just
finished. The MSDN version of SQL Server 2005 should not require product
keys at all.
"moondaddy" <moondaddy@.nospam.nospam> wrote in message
news:OWi1L2X5FHA.1864@.TK2MSFTNGP12.phx.gbl...
>I just downloaded sql server 2005 dev edition from my msdn subscription,
>however, in the listing of product keys, I dont see any keys for sql
>server. Dont I need a product key for this when I install it?
> --
> moondaddy@.nospam.nospam
>|||moondaddy wrote:
> I just downloaded sql server 2005 dev edition from my msdn
> subscription, however, in the listing of product keys, I dont see any
> keys for sql server. Dont I need a product key for this when I
> install it?
It might have the key built into the download. Some products work that
way to ease installation. Start the install and see.
David Gugick
Quest Software
www.imceda.com
www.quest.com

2005 local server name help

you have to add the server with
exec sp_addserver 'newname','local'
the key word 'local' will set the serverid to 0.
-oj
"Mike" <mguissine@.hotmail.com> wrote in message
news:1151461463.763845.263690@.75g2000cwc.googlegroups.com...
>I have installed SQL Server 2005 and by mistake replace local server
> name with linked server.
> Ran sp_dropserver 'Localservername' and sp_addserver 'localservername'.
> After that I installed replication on same server and enable it as
> distributor and subscriber.
> It worked fime till server reboot
> After reboot, the replication stopped working. I find out that this is
> due to local server name not exists anymore (it's no record with id=0
> in sys.servers view).
> Now I have real big problem:
> 1. I can't drop linked and add a local server with same name since it
> used in replication.
> 2. I can't drop replication because it's trying to get server by name
> (apparently quering same view) and it returns NULL.
> Any help , please.
> Mike
>I have installed SQL Server 2005 and by mistake replace local server
name with linked server.
Ran sp_dropserver 'Localservername' and sp_addserver 'localservername'.
After that I installed replication on same server and enable it as
distributor and subscriber.
It worked fime till server reboot
After reboot, the replication stopped working. I find out that this is
due to local server name not exists anymore (it's no record with id=0
in sys.servers view).
Now I have real big problem:
1. I can't drop linked and add a local server with same name since it
used in replication.
2. I can't drop replication because it's trying to get server by name
(apparently quering same view) and it returns NULL.
Any help , please.
Mike|||you have to add the server with
exec sp_addserver 'newname','local'
the key word 'local' will set the serverid to 0.
-oj
"Mike" <mguissine@.hotmail.com> wrote in message
news:1151461463.763845.263690@.75g2000cwc.googlegroups.com...
>I have installed SQL Server 2005 and by mistake replace local server
> name with linked server.
> Ran sp_dropserver 'Localservername' and sp_addserver 'localservername'.
> After that I installed replication on same server and enable it as
> distributor and subscriber.
> It worked fime till server reboot
> After reboot, the replication stopped working. I find out that this is
> due to local server name not exists anymore (it's no record with id=0
> in sys.servers view).
> Now I have real big problem:
> 1. I can't drop linked and add a local server with same name since it
> used in replication.
> 2. I can't drop replication because it's trying to get server by name
> (apparently quering same view) and it returns NULL.
> Any help , please.
> Mike
>|||I maybe didn't explained things correctly..
I created linked server with 'LocalServerName' thas my local server
(id=0) does not exists anymore. Now I want to put it back. The right
steps to do it would be:
1. drop linked server. sp_dropserver 'name', 'droplogins'
2. create local server. sp_addserver 'name' , 'local'
The problem is that the server is used in replication, so I can't drop
it hense can't add new server with the same name. I can't do anything
with replication either...
The only way I think is to hack into system tables and change id to 0
for the linked server so it will become local. But it looks like in
SS2005 i don't have that option...
Mike
oj wrote:[vbcol=seagreen]
> you have to add the server with
> exec sp_addserver 'newname','local'
> the key word 'local' will set the serverid to 0.
> --
> -oj
>
> "Mike" <mguissine@.hotmail.com> wrote in message
> news:1151461463.763845.263690@.75g2000cwc.googlegroups.com...|||I maybe didn't explained things correctly..
I created linked server with 'LocalServerName' thas my local server
(id=0) does not exists anymore. Now I want to put it back. The right
steps to do it would be:
1. drop linked server. sp_dropserver 'name', 'droplogins'
2. create local server. sp_addserver 'name' , 'local'
The problem is that the server is used in replication, so I can't drop
it hense can't add new server with the same name. I can't do anything
with replication either...
The only way I think is to hack into system tables and change id to 0
for the linked server so it will become local. But it looks like in
SS2005 i don't have that option...
Mike
oj wrote:[vbcol=seagreen]
> you have to add the server with
> exec sp_addserver 'newname','local'
> the key word 'local' will set the serverid to 0.
> --
> -oj
>
> "Mike" <mguissine@.hotmail.com> wrote in message
> news:1151461463.763845.263690@.75g2000cwc.googlegroups.com...|||Ah. I see your delima. Dropping repl would be the only choice. Sorry.
-oj
"Mike" <mguissine@.hotmail.com> wrote in message
news:1151511040.077204.203160@.j72g2000cwa.googlegroups.com...
>I maybe didn't explained things correctly..
> I created linked server with 'LocalServerName' thas my local server
> (id=0) does not exists anymore. Now I want to put it back. The right
> steps to do it would be:
> 1. drop linked server. sp_dropserver 'name', 'droplogins'
> 2. create local server. sp_addserver 'name' , 'local'
> The problem is that the server is used in replication, so I can't drop
> it hense can't add new server with the same name. I can't do anything
> with replication either...
> The only way I think is to hack into system tables and change id to 0
> for the linked server so it will become local. But it looks like in
> SS2005 i don't have that option...
> Mike
>
> oj wrote:
>