3 tables query

I hable that has a ProOnwerID and ProFinOwner, when I create the following
view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
ProOwer shows up.. Any Idea why ?... is it because I have two ID for
Clients en same table ?
SELECT dbo.projects.*, dbo.employee.EmpName AS EmpName,
dbo.Clients.ClientName AS ProOwner, dbo.Clients.ClientName AS FinOwner
FROM dbo.projects LEFT OUTER JOIN
dbo.employee ON dbo.projects.ProPMID =
dbo.employee.EmpUserID LEFT OUTER JOIN
dbo.Clients ON dbo.projects.ProFinOwner =
dbo.Clients.ClientId AND dbo.projects.ProOwner = dbo.Clients.ClientId
thanksOn Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:

>I hable that has a ProOnwerID and ProFinOwner, when I create the following
>view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
>ProOwer shows up.. Any Idea why ?... is it because I have two ID for
>Clients en same table ?
Hi Carlos,
Your query tries to find ONE row in Clients that is equal to both the
ProFinOwner and the ProOwner. This will only succeed if ProOwner and
ProFinOwner are the same. If they are not, you'll get NULL (due to the
left join - with inner join, you'd not have gotten any rows at all).
I'm actually quite surprised that you did see the ProOwner when you
"deleted FinOwner" - but maybe I'm just misunderstanding what you actually
did.
Anyway, to show the name of the two owners, even if they are not the same,
you'll have to join in the client table twice:
SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
e.EmpName AS EmpName,
o.ClientName AS ProOwner,
po.ClientName AS FinOwner
FROM dbo.projects AS p
LEFT OUTER JOIN dbo.employee AS e
ON p.ProPMID = e.EmpUserID
LEFT OUTER JOIN dbo.Clients AS po
ON p.ProOwner = po.ClientId
LEFT OUTER JOIN dbo.Clients AS fo
ON p.ProFinOwner = fo.ClientId
If I were you, I'd also check if you really need all these joins to be
outer joins. Inner joins are usually faster.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo that did it !!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:j8ai01lfqn0j5c0l8tig70ne1tu1dqg576@.
4ax.com...
> On Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:
>
> Hi Carlos,
> Your query tries to find ONE row in Clients that is equal to both the
> ProFinOwner and the ProOwner. This will only succeed if ProOwner and
> ProFinOwner are the same. If they are not, you'll get NULL (due to the
> left join - with inner join, you'd not have gotten any rows at all).
> I'm actually quite surprised that you did see the ProOwner when you
> "deleted FinOwner" - but maybe I'm just misunderstanding what you actually
> did.
> Anyway, to show the name of the two owners, even if they are not the same,
> you'll have to join in the client table twice:
> SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
> e.EmpName AS EmpName,
> o.ClientName AS ProOwner,
> po.ClientName AS FinOwner
> FROM dbo.projects AS p
> LEFT OUTER JOIN dbo.employee AS e
> ON p.ProPMID = e.EmpUserID
> LEFT OUTER JOIN dbo.Clients AS po
> ON p.ProOwner = po.ClientId
> LEFT OUTER JOIN dbo.Clients AS fo
> ON p.ProFinOwner = fo.ClientId
> If I were you, I'd also check if you really need all these joins to be
> outer joins. Inner joins are usually faster.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

3 table joins - 3rd table join main not exist (return null columns) - please help!

Hello SQL Guru's,

This has had me stumped for about 8 hours already and I think I've gotten to a point where I'm trying the same things over and over again and they are just not working. Any help would be greatly appreciated!

My Table Structure:

Table 1) 'Modules'

ModuleID | ModuleName | isVisible
--
1 Test 1 True
2 Test 2 True
3 Test 3 False
4 Test 4 True

Table 2) 'ModuleUserTypes'

ID | ModuleID | UserType

1 1 1
2 1 2
3 2 1
4 3 1
5 4 1
6 4 2

Table 3) 'ModuleUserSettings'

ID | ModuleID | UserID | CustomTitle | BGColor
--
1 2 1 New Title2 Black
2 2 2 New Title2 White
3 3 1 New Title3 Orange
4 4 1 NewTitle4 Yellow

My Goal:
To be able to join the 3 tables 'Modules', 'ModuleUserTypes', and 'ModuleUserSettings' together and return 'ModuleID, ModuleName, CustomTitle, BGColor' for ALL Modules with UserType = 1 along with associated ModuleUserSettings IF the UserSetting exists, otherwise NULL for the columns.

My desired result set:

UserID = 1
UserType = 1
isVisible = True

ModuleID | ModuleName | CustomTitle | BGColor
--
1 Test1 NULL NULL
2 Test2 New Title2 White
4 Test4 New Title4 Yellow

I'm sure this type of query will be easy for someone out there, but rather hard for me!

Thanks for your efforts!

Execute the following query, to get your results :

select m.ModuleID,m.ModuleName,CustomTitle,BGColor

from Modules m left join ModuleUserTypes mut on m.ModuleID = mut.ModuleID

left join ModuleUserSettings mus on m.ModuleID = mus.ModuleID

where (UserID is null or UserID = 1)

and UserType=1

and IsVisible = 1

Assumption - IsVisible column is bit data type

otherwise - use -

and IsVisible = 'True'

Thanks

Naras.

|||you need to include your User table or you table that define the usertype of a user and you need to use left join

hope this helps

SELECT *
INTO #Modules
FROM (
SELECT 1 AS ModuleID
,'Test 1' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 2 AS ModuleID
,'Test 2' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 3 AS ModuleID
,'Test 3' AS ModuleName
, 'False' AS isVisible
UNION ALL
SELECT 4 AS ModuleID
,'Test 4' AS ModuleName
, 'True' AS isVisible
) Modules

SELECT *
INTO #ModuleUserTypes
FROM ( SELECT 1 AS [ID]
, 1 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 2 AS [ID]
, 1 AS ModuleID
, 2 AS UserType
UNION ALL
SELECT 3 AS [ID]
, 2 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 4 AS [ID]
, 3 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 5 AS [ID]
, 4 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 6 AS [ID]
, 4 AS ModuleID
, 2 AS UserType

) ModuleUserTypes

SELECT *
INTO #ModuleUserSettings
FROM (

SELECT 1 AS [ID]
, 2 AS ModuleID
, 1 AS UserID
, 'New Title2' AS CustomTitle
, 'White' AS BGColor
UNION ALL
SELECT 2 AS [ID]
, 2 AS ModuleID
, 2 AS UserID
, 'New Title2' AS CustomTitle
, 'Black' AS BGColor
UNION ALL
SELECT 3 AS [ID]
, 3 AS ModuleID
, 1 AS UserID
, 'New Title3' AS CustomTitle
, 'Orange' AS BGColor
UNION ALL
SELECT 4 AS [ID]
, 4 AS ModuleID
, 1 AS UserID
, 'New Title4' AS CustomTitle
, 'Yellow' AS BGColor
) ModuleUserSettings

SELECT *
INTO #Users
FROM (
SELECT 1 AS UserID
, 1 AS UserType
UNION ALL
SELECT 2 AS UserID
, 2 AS UserType

) Users

DECLARE @.UserType int
DECLARE @.UserID int
DECLARE @.isVisible varchar(5)

SET @.UserType = 1
SET @.UserID = 1
SET @.isVisible = 'True'

SELECT DISTINCT
m.ModuleID
, m.ModuleName
, mus.CustomTitle
, mus.BGColor
FROM #Modules m LEFT OUTER JOIN
#ModuleUserTypes mut ON m.ModuleID = mut.ModuleID LEFT OUTER JOIN
#ModuleUserSettings mus ON m.ModuleID = mus.ModuleID
AND mut.ModuleID = mus.ModuleID LEFT OUTER JOIN
#Users ut ON mut.UserType = ut.UserType
AND mus.UserID = ut.UserID
WHERE ISNULL(mut.UserType,@.UserType) = @.UserType
AND ISNULL(mus.UserID,@.UserID) = @.UserID
AND ISNULL(m.isVisible,@.isVisible) = @.isVisible

DROP TABLE #Modules
DROP TABLE #ModuleUserTypes
DROP TABLE #ModuleUserSettings
DROP TABLE #Users|||

select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolour

from Modules m

join moduleusertypes mut

on m.moduleid = mut.moduleid

and mut.usertype = 1

left join ModuleUserSettings mus

on mus.moduleid = mut.moduleid

and mus.userid = mut.usertype

where IsVisible = 1

Assuming userid in 'ModuleUserSettings' is equal to UserType in 'ModuleUserTypes'

Regards,

kwareol

|||Thanks Nara's for your reply. I tried a similar statement but it was not filtering correctly. It would work until I added the UserType=1 and isVisible=1 to the where clause.
|||Kwareol,

Your statement took me in the right direction!

All I needed to add was the UserID filter.

This is the final statement that works exactly as I needed:

select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolor

from Modules m

join moduleusertypes mut

on m.moduleid = mut.moduleid

and mut.usertype = 1

left join ModuleUserSettings mus

on mus.moduleid = mut.moduleid

and (mus.userid = 1 or mus.userid is null)

where IsVisible = 1

Thank you and everybody so much for your time and efforts!! It's much much appreciated!

(I marked this post as the final answer. I'm not exactly sure how this forums works and if users get some sort of point ranking for posting correct answers. If so, I will change it to Kwareol for him leading me in the direction I needed to go)

2ND POST: Is there a way to insert a record using another as a tem

Hi
If you specify the column list then you insert values into those columns.
Missing columns will be set to NULL (if it is allowed) or a default value.
To update existing data use the UPDATE statement, you will only change the
values for columns specified in the SET clause. More information on the
INSERT and UPDATE statements can be found in Books Online.
Using stored procedure may help to remove any issues with different versions
as you will ship the correct procedure with the changes to the schema.
You can look at the INFORMATION_SCHEMA.columns view to find out what columns
a table has, although a different approach would be to only provide a
solution for the latest version and check they are on that version otherwise
force an upgrade.
John
"SA Development" wrote:

> Hi,
> I am currently inserting a record using:
> insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
> But, the problem I have is that the table I am connecting to can either ha
ve
> 46 fields or 47 fields depending on its version. Is there a command that
> would allow me to use an existing record as a template and only specified
> the fields I want to change and then insert the new record?
> Something like:
> insert into TABLE(FIELD1) value ('%s') using template record where
> FIELD1='9999999' ?
> Thanks,
> SA Dev
>
>Thanks for the good ideas everyone, sorry my first post wasn't entirely
clear.
Lets say I have a table like this:
field1(key) field2 field3 field4 field5
9999 a b c d
What I was hoping to do is add a new record (field1=123 for example), but
use the 9999 as a template record so that its field values are inserted into
my new record EXCEPT for any that I override.
It sounds like the update command will let me update just the fields I want,
is there a way to tell SQL to copy record 9999 to record 123 and use all the
field values in record 9999 ?
Thanks,
Alan|||Hi David,

> INSERT INTO YourTable (col1, col2, col3, col4, col5)
> SELECT 123, col2, col3, col4, col5
> FROM YourTable
> WHERE col1 = 9999
Thank you -- that is exactly what I needed!
Thanks to everyone else who posted as well.
Have a great day,
Alan

2nd Login failed for user '(null)' ...

This is driving me NUTS!
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.
OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>

2nd Login failed for user '(null)' ...

This is driving me NUTS!
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>

2nd Login failed for user '(null)' ...

This is driving me NUTS!
Sometime on Modday, or the first part of the day Tuesday, I lost my ability
to connect to the servers from my workstation and have been getting the
message: "Login failed for user '(null)' ...".
I think (but am not sure) that this started with my changing the login
account in Windows Services for all the SQL Server entries from my domain
account to the Local account.
I have since completly uninstalled all SQL Servers and tools and then
reinstalled just the SQL Server 2000 client tools. Also, my local profile
was completly removed and I started with a fresh local account.
Now the really bizzare stuff. I (of course) get the error when logged into
my machine and I also get the error when logged into another machine.
However, when our Windows sysadmin logs into my machine, he can connect just
fine.
I am so totally lost, it isn't funny.OK, my version of the problem has been figured out.
The remote servers I'm dealing with are not part of the domain and we had a
new password policy (on the new QA cluster) that required two additional
characters be added. The Windows sysadmin had me change my domain password
to match - hence a password mismatch and the error.
Once I changed my password back to the old one, I was able to connect again.
Sigh ...
"Jay" <nospam@.nospam.org> wrote in message
news:unwuEGYIIHA.5360@.TK2MSFTNGP03.phx.gbl...
> This is driving me NUTS!
> Sometime on Modday, or the first part of the day Tuesday, I lost my
> ability to connect to the servers from my workstation and have been
> getting the message: "Login failed for user '(null)' ...".
> I think (but am not sure) that this started with my changing the login
> account in Windows Services for all the SQL Server entries from my domain
> account to the Local account.
> I have since completly uninstalled all SQL Servers and tools and then
> reinstalled just the SQL Server 2000 client tools. Also, my local profile
> was completly removed and I started with a fresh local account.
> Now the really bizzare stuff. I (of course) get the error when logged into
> my machine and I also get the error when logged into another machine.
> However, when our Windows sysadmin logs into my machine, he can connect
> just fine.
> I am so totally lost, it isn't funny.
>

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?
>> >
>> >
>> >