Cant drop constraint...?

Hello, I have hit the wall here...can't make sense of this one.

I have a script that creates a PRIMARY KEY constraint called PK_tblDRG
CODE:

ALTER TABLE [dbo].[tblDRG]
ALTER COLUMN [record_id] Uniqueidentifier NOT NULL
Go
ALTER TABLE [dbo].[tblDRG]
WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED
(
[record_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]

All is fine with that. I run this to verify:

EXEC sp_pkeys @.table_name = 'tblDRG'
,@.table_owner = 'dbo' ,@.table_qualifier = 'Relational_05Q3'

which returns this:

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
Relational_05Q3 dbo tblDRG record_id 1 PK_tblDRG

Now I want to drop the constriant if it exists:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I get this in return:

The command(s) completed successfully.

So, lets double check:

EXEC sp_pkeys @.table_name = 'tblDRG'
,@.table_owner = 'dbo' ,@.table_qualifier = 'Relational_05Q3'

AND I STILL GET THIS:

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
Relational_05Q3 dbo tblDRG record_id 1 PK_tblDRG

Hmmmmm. Looks like the IF statement didn't do it's job. Ok fine. I'll just kill it myself:

ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I GET THIS?!?!?!?!

Server: Msg 3728, Level 16, State 1, Line 1
'PK__tblDRG' is not a constraint.

What am I not getting here? Is it me...I can take If I am a bone head here.

Any help would be appreciated. Thanks!Nevermind...I am a moron.
PK__tblDRG doesn't exists because it's PK_tblDRG!!!

Sorry!

Cant Delete Row

I ran into a scenerio this morning

I made a table with no relations or primary id

It is an error log table basically

I went to delete a row and I got an error of

Cant delete row because it does not make them unigue or it alters multiple rows(3)

I didnt want this by no means, what did i not sdet right and how do you delete the rows without deleting the table and starting out new again?

Davids Learning

hi,

you'll often see this kind of problems on a table without a primary key... try defining one..

regards

|||

It wouldnt let me do that, so I deleted the table and started again(no big loss), but I did make a primary this time!

Thanks

Davids Learning

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
Ragnar
One method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>
|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I have a table in an SQL Server 2000 table that has no primary key, in
>> fact no indexes at all. (I didn't design this (:-)) )
>> I found that there is a duplicate row in the table but when I try to
>> delete it I get an error message:
>> "Key column information insufficient or incomplete. Too many rows were
>> affected by this update."
>> I had tried adding a primary key index but of course I couldn't since
>> there is a duplicate row.
>> I also tried just opening the table in EM and editing the data in the
>> column I want as primary key for one of the duplicate rows so I could set
>> the primary key, but I got an error message that the transaction can't
>> start in firehose mode.
>> Is there a simple way I can get out of this catch22 box? The DB supports
>> a busy Web site, so I was hoping to avoid shutting down the site.
>> Any help would be appreciated.
>> Ragnar
>

can't delete a row from primary table - is there an SP for this?

Hello,
I copied a database from our live server to a test server where I could
study the database, experiment and so on. I cannot delete/remove a row from
the primary table. The table had several relationships, dozens of triggers
,
a constraint, and was under replication on the live server.
I have removed all the relationships, triggers, constraints from the table
on the test server, and I do not have replication running on the test server
and there is no replication running on the database in the test server.
I did copy the contents of the primary table to a temp table and was able to
delete the row from the temp table. But I can't delete from the primary
table. Is there a property somewhere in the database that I need to look at
?
How come I can't delete from this table but I was able to delete from the
temp table? Is there an SP I can run to fix this or check what is going on?
Thanks,
RichCan you post the DELETE statement and the error message?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:ED97EF6C-6223-457C-A668-7BE409CAE593@.microsoft.com...
> Hello,
> I copied a database from our live server to a test server where I could
> study the database, experiment and so on. I cannot delete/remove a row fr
om
> the primary table. The table had several relationships, dozens of trigge
rs,
> a constraint, and was under replication on the live server.
> I have removed all the relationships, triggers, constraints from the table
> on the test server, and I do not have replication running on the test serv
er
> and there is no replication running on the database in the test server.
> I did copy the contents of the primary table to a temp table and was able
to
> delete the row from the temp table. But I can't delete from the primary
> table. Is there a property somewhere in the database that I need to look
at?
> How come I can't delete from this table but I was able to delete from the
> temp table? Is there an SP I can run to fix this or check what is going o
n?
> Thanks,
> Rich|||Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE,
DELETE
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:ED97EF6C-6223-457C-A668-7BE409CAE593@.microsoft.com...
> Hello,
> I copied a database from our live server to a test server where I could
> study the database, experiment and so on. I cannot delete/remove a row
> from
> the primary table. The table had several relationships, dozens of
> triggers,
> a constraint, and was under replication on the live server.
> I have removed all the relationships, triggers, constraints from the table
> on the test server, and I do not have replication running on the test
> server
> and there is no replication running on the database in the test server.
> I did copy the contents of the primary table to a temp table and was able
> to
> delete the row from the temp table. But I can't delete from the primary
> table. Is there a property somewhere in the database that I need to look
> at?
> How come I can't delete from this table but I was able to delete from the
> temp table? Is there an SP I can run to fix this or check what is going
> on?
> Thanks,
> Rich|||Jeff,
As of 7.0 you can have several triggers of the same type on a table. And as
of 2000, you can define
which to fire first and which to fire last.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jeff Dillon" <jeffdillon@.hotmail.com> wrote in message
news:OqFSPqreGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Dozens of triggers? A table can generally only have 3 - INSERT, UPDATE, DE
LETE
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:ED97EF6C-6223-457C-A668-7BE409CAE593@.microsoft.com...
>

can't delete a row from primary table - is there an SP for thi

Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
updates and there was a trigger to rollback deletes. But I commented out al
l
of the triggers - all of them. So there are basically no triggers except fo
r
t_sometinging on PrimaryTbl
For Insert...
As
/* */
Return
I did that to all of the triggers so that nothing would fire. Right now I
am recreating the table with all the triggers, indexes, relationships,
constraints and so on and see if that table works (minus of course, the For
Delte As Rollback...)
"Tibor Karaszi" wrote:

> Jeff,
> As of 7.0 you can have several triggers of the same type on a table. And a
s of 2000, you can define
> which to fire first and which to fire last.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jeff Dillon" <jeffdillon@.hotmail.com> wrote in message
> news:OqFSPqreGHA.5040@.TK2MSFTNGP03.phx.gbl...
>
>A much easier way to disable triggers, that doesn't involve having to change
code, is to use ALTER TABLE ... DISABLE TRIGGER
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
> all
> of the triggers - all of them. So there are basically no triggers except
> for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the
> For
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||recreating the table seemed to do the trick. I was able to delete the row
from the re-created table.
Recreating the live table will be a bigger hassel because I will have to
stop replication first.
"Rich" wrote:
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
all
> of the triggers - all of them. So there are basically no triggers except
for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the Fo
r
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||yes, I have used that before, but I just needed to make sure there was
nothing in the triggers that could possibly run.
Well, as fate would have it, after I recreated my table and was able to
delete the desired row, I then readded the row and also re-added all the
triggers, relationships, etc. Now I can't delete the row again in the new
table. So I guess maybe there was nothing wrong with the original table.
So tommorrow I have to play the boring game of recreating the table, add the
row, delete the row, and keep adding triggers one at a time and relationship
s
until I can't delete the row and thus isolate the offending procedure,
relationship. I am already having indigestion thinking about it.
"Kalen Delaney" wrote:

> A much easier way to disable triggers, that doesn't involve having to chan
ge
> code, is to use ALTER TABLE ... DISABLE TRIGGER
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
>
>|||Well, I figured out what the problem was in deleteing rows on my table. It
was the Instead Of Delete trigger. Even though I had commented out the body
and only had
Create Trigger...
Instead Of Delete
As
--
--
Return
This would not allow deletes on the table. When I removed the trigger
entirely, I was able to delete rows.
"Rich" wrote:
> yes, I have used that before, but I just needed to make sure there was
> nothing in the triggers that could possibly run.
> Well, as fate would have it, after I recreated my table and was able to
> delete the desired row, I then readded the row and also re-added all the
> triggers, relationships, etc. Now I can't delete the row again in the new
> table. So I guess maybe there was nothing wrong with the original table.
> So tommorrow I have to play the boring game of recreating the table, add t
he
> row, delete the row, and keep adding triggers one at a time and relationsh
ips
> until I can't delete the row and thus isolate the offending procedure,
> relationship. I am already having indigestion thinking about it.
> "Kalen Delaney" wrote:
>|||I think that is because the code in the trigger runs in place of your delete
statement (on the rows affected by it). Since there was no code, and no
action to perform, nothing was done with these rows.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:92D437D9-CFDB-45F4-8991-90C49BBFD1E7@.microsoft.com...
> Well, I figured out what the problem was in deleteing rows on my table.
It
> was the Instead Of Delete trigger. Even though I had commented out the
body
> and only had
> Create Trigger...
> Instead Of Delete
> As
> --
> --
> Return
> This would not allow deletes on the table. When I removed the trigger
> entirely, I was able to delete rows.
> "Rich" wrote:
>
new
table.
the
relationships
change
some
commented out
except
now I
relationships,
the
table. And
UPDATE,
I
delete/remove a
of
the
test
was
need to
from
is

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GO
UPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>
|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
----
--
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create table definition with primary key and SMO.

I'm trying this code, but it doesn't work. Can you help me?

I show you the error and code...I don't understand what is the adverted "Primary element" in error messaje and I'm really astonished because one time the code works whitout index definition, but any way, it doesn't work now.

Thanks,

Asereware

Context Info:

Visual C# Express Edition.

SQL Server 2005 Express & SQL Server 2000. The behavior is the same in both.

Error message:

<ERROR>-
Fuente:Vivenda.Testings
Descripción simple:
Error de Crear para Tabla 'dbo.AsereTablaSMO1'.

Detalle:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Error de Crear para
Tabla 'dbo.AsereTablaSMO1'. > Microsoft.SqlServer.Management.Smo.FailedOper
ationException: No se puede crear Table '[dbo].[AsereTablaSMO1]' si aún no se ha
creado el elemento primario.
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringColle
ction& createQuery, ScriptingOptions& so)
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
Fin del seguimiento de la pila de la excepción interna
en Vivenda.Testings.Program.TestSQLServerSMO() en Program.cs:línea 640
en Vivenda.Testings.Program.Main(String[] args) en Program.cs:línea 50

Source Code:

private static void TestSQLServerSMO()

{

SqlConnection cnn = null;

Microsoft.SqlServer.Management.Common.ServerConnection cnn1 = null;

try

{

cnn = new SqlConnection("data source=(local);initial catalog=bap;Integrated Security=true;Connect Timeout=30");

cnn1 = new Microsoft.SqlServer.Management.Common.ServerConnection(cnn);

Server local = new Server(cnn1);

Database vivenda = new Database(local, "pubs");

if (vivenda.Tables.Contains("AsereTablaSMO1"))

vivenda.Tables["AsereTablaSMO1"].Drop();

//--

//Table definition

//--

Table impTable = new Table(vivenda, "AsereTablaSMO1");

Column col1 = new Column(impTable, "AsID", new DataType(SqlDataType.Int));

col1.Nullable = false;

col1.Identity = true;

impTable.Columns.Add(col1);

Column col2 = new Column(impTable, "Description", new DataType(SqlDataType.NVarChar, 150));

col2.Nullable = true;

impTable.Columns.Add(col2);

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//Create object.

impTable.Create();

}

catch (Exception ex)

{

throw (ex);

}

finally

{

if (cnn1 != null)

{

cnn1.Disconnect();

cnn1 = null;

}

}

}

//End

You have to create the table before you can create an index on the table. Consider this VB example (derived from the Books Online examples):

Dim srv As Server
srv = New Server("MyServer")
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor.
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
Dim idx As Index
idx = New Index(tb, "TestIndex")
'Add indexed columns to the index.
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "ID", True)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = False
idx.FillFactor = 50
'Create the index on the instance of SQL Server.
idx.Create()

If you try to create the index before issuing the tb.Create() method you get an exception, because the table doesn't yet exist. By creating the index after the table has been created the idx.Create() works without exception.

|||

Thanks Allen. Here is the final code section changed and working:

Alvaro

//Create object. It is fundamental before set index.

impTable.Create();

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//-

//Create Index.

//-

idx.Create();

Can't create table definition with primary key and SMO.

I'm trying this code, but it doesn't work. Can you help me?

I show you the error and code...I don't understand what is the adverted "Primary element" in error messaje and I'm really astonished because one time the code works whitout index definition, but any way, it doesn't work now.

Thanks,

Asereware

Context Info:

Visual C# Express Edition.

SQL Server 2005 Express & SQL Server 2000. The behavior is the same in both.

Error message:

<ERROR>-
Fuente:Vivenda.Testings
Descripción simple:
Error de Crear para Tabla 'dbo.AsereTablaSMO1'.

Detalle:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Error de Crear para
Tabla 'dbo.AsereTablaSMO1'. > Microsoft.SqlServer.Management.Smo.FailedOper
ationException: No se puede crear Table '[dbo].[AsereTablaSMO1]' si aún no se ha
creado el elemento primario.
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetParentObject()
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplInit(StringColle
ction& createQuery, ScriptingOptions& so)
en Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
Fin del seguimiento de la pila de la excepción interna
en Vivenda.Testings.Program.TestSQLServerSMO() en Program.cs:línea 640
en Vivenda.Testings.Program.Main(String[] args) en Program.cs:línea 50

Source Code:

private static void TestSQLServerSMO()

{

SqlConnection cnn = null;

Microsoft.SqlServer.Management.Common.ServerConnection cnn1 = null;

try

{

cnn = new SqlConnection("data source=(local);initial catalog=bap;Integrated Security=true;Connect Timeout=30");

cnn1 = new Microsoft.SqlServer.Management.Common.ServerConnection(cnn);

Server local = new Server(cnn1);

Database vivenda = new Database(local, "pubs");

if (vivenda.Tables.Contains("AsereTablaSMO1"))

vivenda.Tables["AsereTablaSMO1"].Drop();

//--

//Table definition

//--

Table impTable = new Table(vivenda, "AsereTablaSMO1");

Column col1 = new Column(impTable, "AsID", new DataType(SqlDataType.Int));

col1.Nullable = false;

col1.Identity = true;

impTable.Columns.Add(col1);

Column col2 = new Column(impTable, "Description", new DataType(SqlDataType.NVarChar, 150));

col2.Nullable = true;

impTable.Columns.Add(col2);

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//Create object.

impTable.Create();

}

catch (Exception ex)

{

throw (ex);

}

finally

{

if (cnn1 != null)

{

cnn1.Disconnect();

cnn1 = null;

}

}

}

//End

You have to create the table before you can create an index on the table. Consider this VB example (derived from the Books Online examples):

Dim srv As Server
srv = New Server("MyServer")
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable by supplying the parent database and table name in the constructor.
Dim tb As Table
tb = New Table(db, "Test_Table")
'Add various columns to the table.
Dim col1 As Column
col1 = New Column(tb, "Name", DataType.NChar(50))
col1.Collation = "Latin1_General_CI_AS"
col1.Nullable = True
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "ID", DataType.Int)
col2.Identity = True
col2.IdentitySeed = 1
col2.IdentityIncrement = 1
tb.Columns.Add(col2)
Dim col3 As Column
col3 = New Column(tb, "Value", DataType.Real)
tb.Columns.Add(col3)
Dim col4 As Column
col4 = New Column(tb, "Date", DataType.DateTime)
col4.Nullable = False
tb.Columns.Add(col4)
'Create the table on the instance of SQL Server.
tb.Create()
Dim idx As Index
idx = New Index(tb, "TestIndex")
'Add indexed columns to the index.
Dim icol1 As IndexedColumn
icol1 = New IndexedColumn(idx, "ID", True)
idx.IndexedColumns.Add(icol1)
idx.IndexKeyType = IndexKeyType.DriUniqueKey
idx.IsClustered = False
idx.FillFactor = 50
'Create the index on the instance of SQL Server.
idx.Create()

If you try to create the index before issuing the tb.Create() method you get an exception, because the table doesn't yet exist. By creating the index after the table has been created the idx.Create() works without exception.

|||

Thanks Allen. Here is the final code section changed and working:

Alvaro

//Create object. It is fundamental before set index.

impTable.Create();

//-

//Index def

//-

if (impTable.Indexes.Contains("PKI_AsereTablaSMO1"))

impTable.Indexes["PKI_AsereTablaSMO1"].Drop();

Index idx = new Index(impTable, "PKI_AsereTablaSMO1");

idx.IndexedColumns.Add(new IndexedColumn(idx, col1.Name));

idx.IsClustered = true;

idx.IsUnique = true;

idx.IndexKeyType = IndexKeyType.DriPrimaryKey;

//-

//Create Index.

//-

idx.Create();

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

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

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:
>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!
>>
>>|||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!
>

cant convert char to varchar

I upgraded my datbase from 6.5 to 2000. Now I have primary key in one table datatype CHAR(5) and second table with a column having datatype VARCHAR(5).

I am trying to create a foreign key from second to first table but I am getting following error.

"Column 'dbo.dma.dma' is not the same data type as referencing column 'fone.dma' in foreign key 'FK_fone_dma'."

But I beleive CHAR to VARCHAR conversion is implicit.

COuld anyone please enlighten this problem.

Thanks in advance..The conversion is implicit, but the server won't do conversions for foreign keys. The two must be the same datatype.

You can choose to maintain relational integrity through the use of triggers, but you are better off making the field types uniform.

blindman|||If the table was created with ANSI_PADDING OFF, then you can drop the foreign key, alter table alter column <col_name> char(5) NULL + any defaults or check constraints. With ANSI_PADDING OFF the way data is stored in CHAR datatype is the same as for VARCHAR, providing that the column allows NULLs. After you altered the column re-create your foreign key and you're done.|||Cool. :cool:

blindman|||Doesn't appear so in RI

USE Northwind
GO

CREATE TABLE myTable99 (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
GO

CREATE TABLE myTablexx (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
ALTER TABLE myTablexx ADD CONSTRAINT myTablexx_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

CREATE TABLE myTable00 (Col1 varchar(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

DROP TABLE myTablexx
DROP TABLE myTable00
DROP TABLE myTable99
GO|||Uncool. :confused:
blindman|||Originally posted by rdjabarov
providing that the column allows NULLs.

Nulls...pk...hmmmmmmmmm

USE Northwind
GO

CREATE TABLE myTable99 (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
GO

CREATE TABLE myTablexx (Col1 char(5) NOT NULL PRIMARY KEY, Col2 int DEFAULT 0)
ALTER TABLE myTablexx ADD CONSTRAINT myTablexx_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

CREATE TABLE myTable00 (Col1 varchar(5) NOT NULL CONSTRAINT myTable00_PK PRIMARY KEY, Col2 int DEFAULT 0)
-- Will Fail
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
ALTER TABLE myTable00 ADD Col3 char(5) NULL
UPDATE myTable00 SET Col3 = Col1
ALTER TABLE myTable00 DROP CONSTRAINT myTable00_PK
ALTER TABLE myTable00 DROP COLUMN Col1
ALTER TABLE myTable00 ADD Col1 char(5) NULL
UPDATE myTable00 SET Col1 = Col3
ALTER TABLE myTable00 ALTER COLUMN Col1 char(5) NOT NULL
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_PK PRIMARY KEY (Col1)
-- Will NOT Fail
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO

DROP TABLE myTablexx
DROP TABLE myTable00
DROP TABLE myTable99
GO|||Originally posted by rdjabarov
If the table was created with ANSI_PADDING OFF, then you can drop the foreign key, alter table alter column <col_name> char(5) NULL + any defaults or check constraints. With ANSI_PADDING OFF the way data is stored in CHAR datatype is the same as for VARCHAR, providing that the column allows NULLs. After you altered the column re-create your foreign key and you're done.

Thanks.

But the same structure (PK CHAR / FK VARCHAR) is working fine in existing DB of 6.5.
How?|||Good old M$...

They forgot to forward engineer that "feature"...

Hey I still think that this is wrong...had lots of disagreements about it though...

USE Northwind
GO

DECLARE @.x int
CREATE TABLE myTable99(Col1 datetime)
SELECT @.x = 0
INSERT INTO myTable99(Col1) SELECT @.x
SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

In DB2, Oracle, ect, that breaks the rules...SQL Server loosley defines that 0 is a valid datatype for datetime...blew my mind when I found this out...

Oh, and btw, if it's a PK, you won't be able to do the ANSI thing (I don't thin)

If you can, post a sample...|||So I assume it worked the second time, Brett?

Cool? Uncool?|||If you mean the second alter, yes it works...cut and paste it in to QA and watch the test...

THE ANSI_PADDINGS thing I'll have to play with it...

But since a PK can't be NULL the point is probably moot...

You know what a faster way would be (but probably more resource intensive..)

CREATE TABLE myTable007 (Col1 char(5) NOT NULL CONSTRAINT myTable007_PK PRIMARY KEY, Col2 int DEFAULT 0)
INSERT INTO myTable007 (Col1, Col2) SELECT Col1, Col2 FROM myTable00
DROP TABLE myTable00
SELECT Col1, Col2 INTO myTable00 FROM myTable007
DROP TABLE myTable007
ALTER TABLE myTable00 ADD CONSTRAINT myTable00_FK1 FOREIGN KEY (Col1) REFERENCES myTable99 (Col1)
GO|||PK cannot allow NULLs, so Brett's experiment is not exactly demonstrating what I was talking about.

set ansi_padding off
go
create table parent (f1 char(5) not null primary key, f2 int null)
go
create table child (f1 char(5) null, f2 int null)
go
alter table child add constraint fk_child2parent foreign key (f1) references parent(f1)
go|||...And if you insert 'A' into PARENT and CHILD tables and select DATALENGTH(f1) from both, you'd see that the result on PARENT is 5 while on CHILD is 1. Thus, the behavior of VARCHAR datatype in CHILD table.|||Originally posted by nmajeed
I upgraded my datbase from 6.5 to 2000. Now I have primary key in one table datatype CHAR(5) and second table with a column having datatype VARCHAR(5).

But that was the original question...

PK cannot allow NULLs, so Brett's experiment is not exactly demonstrating what I was talking about.

But that's neat...

EDIT: But I rarely play with settings, because it can be dangerous...you have to remeber that you have a particular setting, and code for it...

If I do change a setting, it's always in the context of a transacxtion, and is set back at the conclusion...|||So what are your settings in the QA when you're about to create a table, for example? Or you're creating tables in EM?