Going nuts over this error...

Going nuts over this error...

Post by Brad M » Mon, 12 Aug 2002 10:20:38



I have SQL2000 using SEM.  Trying to edit a row in my deptdata table.  Each
time I try to update a NULL value, or delete the row, it gives me this
error:

Key column information is insufficient or incorrect.  Too many rows were
affected by update.

I have no primary key set in this table.  When I tried to set a primary key,
it gave me this error:

'deptdata' table
- Unable to create index 'PK_deptdata'.
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 'AB807'.
[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.

The row that contains the data 'AB807' is the same row that gave me the
first error that I presented in this posting.

Please help!  I am going nuts over this error.  Also, if its just that the
table is corrupted, could someone walk me through exporting the data?  I
just want a simple, comma delimited file, so I can delete the deptdata
table, re-create it, and then import the data.  Losing the data is not an
option.

Once again, thanks so much to everyone for all the help, couldn't do it
without you. <g> literally <g>

Brad.

 
 
 

Going nuts over this error...

Post by oj » Mon, 12 Aug 2002 10:59:15


1. don't use EM for data manipulation
2. your data column has to be unique for you to create PK on it
3. here is a quick script to run in query analyzer to get the data out





--
-oj
Rac v2.1 Public Beta is RELEASED.
http://www.rac4sql.net


Quote:> I have SQL2000 using SEM.  Trying to edit a row in my deptdata table.
Each
> time I try to update a NULL value, or delete the row, it gives me this
> error:

> Key column information is insufficient or incorrect.  Too many rows were
> affected by update.

> I have no primary key set in this table.  When I tried to set a primary
key,
> it gave me this error:

> 'deptdata' table
> - Unable to create index 'PK_deptdata'.
> 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 'AB807'.
> [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.

> The row that contains the data 'AB807' is the same row that gave me the
> first error that I presented in this posting.

> Please help!  I am going nuts over this error.  Also, if its just that the
> table is corrupted, could someone walk me through exporting the data?  I
> just want a simple, comma delimited file, so I can delete the deptdata
> table, re-create it, and then import the data.  Losing the data is not an
> option.

> Once again, thanks so much to everyone for all the help, couldn't do it
> without you. <g> literally <g>

> Brad.


 
 
 

Going nuts over this error...

Post by Steve Kas » Mon, 12 Aug 2002 11:11:49


Brad,

  Another way to get through this problem is to add an additional column to your table of type int, with the identity property set.  That will make all the rows unique and allow you to edit any one of them, regardless of whether you set the new column as primary key or not.  You can delete the column later or leave it to keep the problem from recurring.

Steve Kass
Drew University


> I have SQL2000 using SEM.  Trying to edit a row in my deptdata table.  Each
> time I try to update a NULL value, or delete the row, it gives me this
> error:

> Key column information is insufficient or incorrect.  Too many rows were
> affected by update.

> I have no primary key set in this table.  When I tried to set a primary key,
> it gave me this error:

> 'deptdata' table
> - Unable to create index 'PK_deptdata'.
> 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 'AB807'.
> [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.

> The row that contains the data 'AB807' is the same row that gave me the
> first error that I presented in this posting.

> Please help!  I am going nuts over this error.  Also, if its just that the
> table is corrupted, could someone walk me through exporting the data?  I
> just want a simple, comma delimited file, so I can delete the deptdata
> table, re-create it, and then import the data.  Losing the data is not an
> option.

> Once again, thanks so much to everyone for all the help, couldn't do it
> without you. <g> literally <g>

> Brad.

 
 
 

Going nuts over this error...

Post by Roy Harve » Mon, 12 Aug 2002 11:12:05


Brad,

Quote:>AB807

By the looks of this variable name I am guessing you have the
misfortune to be trying to use relation operations (SQL) against data
from JD Edwards.  You have my sympathy.  When I last worked with data
from their system they had not yet reached the stage of understanding
that every table needs a primary key.

Quote:>I have SQL2000 using SEM.  Trying to edit a row in my deptdata table.  Each
>time I try to update a NULL value, or delete the row, it gives me this
>error:

>Key column information is insufficient or incorrect.  Too many rows were
>affected by update.

The only way this can work is if the table has a primary key or unique
index.

Quote:>I have no primary key set in this table.  

So that fits.

Quote:>When I tried to set a primary key,
>it gave me this error:

>'deptdata' table
>- Unable to create index 'PK_deptdata'.
>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 'AB807'.
>[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.

So you have non-unique data in your intended key.

Quote:>The row that contains the data 'AB807' is the same row that gave me the
>first error that I presented in this posting.

Lets approach this in simple SQL run in Query Analyzer.  Start with
something like:

SELECT AB807, count(*) as rows
  INTO #keys
  FROM AddressBook
 GROUP BY AB807
HAVING count(*) > 1

SELECT * FROM #keys

This will show you all the values of AB807 that are stopping the key
from being created.  Now look at the complete rows that match the
problem.

SELECT *
  FROM AddressBook
 WHERE AB807 IN (select AB807 from #keys)

This shows you everything about the problem row(s).

Now for the real work.  Each value of AB807 that has a problem has two
or more rows in the result set just returned.  If the rows are
absolutely identical - in ALL columns - you have to approach the
problem one way, which I'll get to shortly.  If there are differences
in another column between (or among) the rows with the same value of
AB807 it is simpler, so lets deal with that first.

Suppose the column AB888 is also in the table, and there are two rows
returned with the same AB807, but different values of AB888.  Pick the
value of AB888 you want to get rid of and use it to write a select
that shows just that row:

SELECT *
  FROM AddressBook
 WHERE AB807 = 'value you already knew'
   AND AB888 = 'value you just figured out'

If you can write a select like this, even if it must test five or ten
columns for specific values, and the select returns JUST the row(s)
you must delete, you are in good shape.  Once it looks right you can
turn this into a DELETE:

DELETE
  FROM AddressBook
 WHERE AB807 = 'value you already knew'
   AND AB888 = 'value you just figured out'

Or maybe the rows are completely duplicated.  In this case, see the
FAQ entry deleteduprows.txt at http://www.sqlserverfaq.com/.

Good luck!

Roy

 
 
 

Going nuts over this error...

Post by mountain ma » Mon, 12 Aug 2002 11:10:31



Quote:> I have SQL2000 using SEM.  Trying to edit a row in my deptdata table.
Each
> time I try to update a NULL value, or delete the row, it gives me this
> error:

> Key column information is insufficient or incorrect.  Too many rows were
> affected by update.

> I have no primary key set in this table.  When I tried to set a primary
key,
> it gave me this error:

> 'deptdata' table
> - Unable to create index 'PK_deptdata'.
> 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 'AB807'.
> [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.

> The row that contains the data 'AB807' is the same row that gave me the
> first error that I presented in this posting.

> Please help!  I am going nuts over this error.  Also, if its just that the
> table is corrupted, could someone walk me through exporting the data?  I
> just want a simple, comma delimited file, so I can delete the deptdata
> table, re-create it, and then import the data.  Losing the data is not an
> option.

> Once again, thanks so much to everyone for all the help, couldn't do it
> without you. <g> literally <g>

> Brad.

Do a      select key-field, count(*)
              from yourtable
              group by key-field
              having count(*) > 1

This will identify an key data which
is non-unique, and if this were to be
removed from the table then your
problem might evaporate.

If there is only one row of duplication
(perhaps just 'AB807') and deletion of
it is no problem, end of story.  OTOH
if there are multiple rows returned by
the above select, you may have to think.

Good luck,

Farmer Brown
Falls Creek, OZ

 
 
 

Going nuts over this error...

Post by Brad M » Tue, 13 Aug 2002 06:03:13


Thanks to everyone.  Got the problem fixed.

Best Regards,
Brad


Quote:> Brad,

> >AB807

> By the looks of this variable name I am guessing you have the
> misfortune to be trying to use relation operations (SQL) against data
> from JD Edwards.  You have my sympathy.  When I last worked with data
> from their system they had not yet reached the stage of understanding
> that every table needs a primary key.

> >I have SQL2000 using SEM.  Trying to edit a row in my deptdata table.
Each
> >time I try to update a NULL value, or delete the row, it gives me this
> >error:

> >Key column information is insufficient or incorrect.  Too many rows were
> >affected by update.

> The only way this can work is if the table has a primary key or unique
> index.

> >I have no primary key set in this table.

> So that fits.

> >When I tried to set a primary key,
> >it gave me this error:

> >'deptdata' table
> >- Unable to create index 'PK_deptdata'.
> >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 'AB807'.
> >[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.

> So you have non-unique data in your intended key.

> >The row that contains the data 'AB807' is the same row that gave me the
> >first error that I presented in this posting.

> Lets approach this in simple SQL run in Query Analyzer.  Start with
> something like:

> SELECT AB807, count(*) as rows
>   INTO #keys
>   FROM AddressBook
>  GROUP BY AB807
> HAVING count(*) > 1

> SELECT * FROM #keys

> This will show you all the values of AB807 that are stopping the key
> from being created.  Now look at the complete rows that match the
> problem.

> SELECT *
>   FROM AddressBook
>  WHERE AB807 IN (select AB807 from #keys)

> This shows you everything about the problem row(s).

> Now for the real work.  Each value of AB807 that has a problem has two
> or more rows in the result set just returned.  If the rows are
> absolutely identical - in ALL columns - you have to approach the
> problem one way, which I'll get to shortly.  If there are differences
> in another column between (or among) the rows with the same value of
> AB807 it is simpler, so lets deal with that first.

> Suppose the column AB888 is also in the table, and there are two rows
> returned with the same AB807, but different values of AB888.  Pick the
> value of AB888 you want to get rid of and use it to write a select
> that shows just that row:

> SELECT *
>   FROM AddressBook
>  WHERE AB807 = 'value you already knew'
>    AND AB888 = 'value you just figured out'

> If you can write a select like this, even if it must test five or ten
> columns for specific values, and the select returns JUST the row(s)
> you must delete, you are in good shape.  Once it looks right you can
> turn this into a DELETE:

> DELETE
>   FROM AddressBook
>  WHERE AB807 = 'value you already knew'
>    AND AB888 = 'value you just figured out'

> Or maybe the rows are completely duplicated.  In this case, see the
> FAQ entry deleteduprows.txt at http://www.sqlserverfaq.com/.

> Good luck!

> Roy

 
 
 

Going nuts over this error...

Post by Brad M » Tue, 13 Aug 2002 06:18:08


By the way, I was wondering on this practice...I have seen most PK's
implemented in a table have a column of their own.  However, my DeptID (is
now and will remain) column is unique.  Can I make this column my Primary
Key?  Any thoughts on implementing this practice would be greatly
appreciated.

Best Regards,
Brad


Quote:> I have SQL2000 using SEM.  Trying to edit a row in my deptdata table.
Each
> time I try to update a NULL value, or delete the row, it gives me this
> error:

> Key column information is insufficient or incorrect.  Too many rows were
> affected by update.

> I have no primary key set in this table.  When I tried to set a primary
key,
> it gave me this error:

> 'deptdata' table
> - Unable to create index 'PK_deptdata'.
> 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 'AB807'.
> [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.

> The row that contains the data 'AB807' is the same row that gave me the
> first error that I presented in this posting.

> Please help!  I am going nuts over this error.  Also, if its just that the
> table is corrupted, could someone walk me through exporting the data?  I
> just want a simple, comma delimited file, so I can delete the deptdata
> table, re-create it, and then import the data.  Losing the data is not an
> option.

> Once again, thanks so much to everyone for all the help, couldn't do it
> without you. <g> literally <g>

> Brad.

 
 
 

Going nuts over this error...

Post by Roy Harve » Tue, 13 Aug 2002 06:42:58


Brad,

Quote:>By the way, I was wondering on this practice...I have seen most PK's
>implemented in a table have a column of their own.  However, my DeptID (is
>now and will remain) column is unique.  Can I make this column my Primary
>Key?  Any thoughts on implementing this practice would be greatly
>appreciated.

The syntax is something like:

ALTER TABLE AddressBook
ADD Constraint AddressBookPK PRIMARY KEY(DeptID)

Roy

 
 
 

1. Going Nuts ! Stored Proc and ASP

hi all

firstable, i've read thru several threads here and some other docs - can't
find the solution :(
i'm trying to get the output parameter of a stored procedure back into ASP.
the SP is somewhat set as is, can't do much about it.

there are about 15 input and 1 output parameter in the SP. all parameters
have a default. as a first step i only need to call the procedure and get
the output parameter (a ID) back.

this is what i tried so far, copied from examples...

----------
Set cmd = Server.CreateObject("ADODB.command")
cmd.ActiveConnection = session("wwwcn")
cmd.CommandText = "newAccount"
cmd.CommandType = 4

Set param1 = cmd.CreateParameter("Output",3,2)    <--- IIS would not accept
constants like adParamOutput....
param1.value=0
cmd.Parameters.Append param1

Set rs = cmd.Execute
While Not rs.EOF
    For Each fldloop In rs.Fields
        Response.Write("rs.Fields(i)")
        i = i + 1
    Next
    i = 0
    rs.MoveNext
Wend
---------

the first parameter in the SP is the Output parameter, is defaulted to 0 and
will contain the newly created ID.

if i run this code, following errormessage at the "cmd.execute" line
appears: (translated from english)

"The Value NULL cannot be inserted into blablabla"      it's a constrain.

if i run    "exec newAccount"    in Query Analyzer it works

what is wrong ? i also need to know, if i can use named parameters

named parameters are possible, aren't they, plleaaase

please, do not suggest to change the SP.

thanks a lot for any help.

Beat

PS. this command object is a incredibly annoying way to handle SP
output/input values. i hate it... it's like the developpers sat together and
asked "how could we make as a complicated as humanly possible". but well,
maybe someone can shed some light on this. i hope so.

Beorda Direktwerbung AG
Abteilung EDV
Kantonsstrasse 101
6234 Triengen
Tel.: 041 935 40 60
Fax: 041 935 40 76
Tel. direkt: 041 935 40 68

2. converting data question

3. Full-text searching (Microsoft Search service) goes nuts

4. sdm model to relational model

5. HELP--Going NUTS-->>DTS Package and Foxpro

6. datagrid

7. Going Nuts - TEXT/Blobs need to be the last field in a table

8. temp change of database

9. Going Nuts

10. Inputting dates and currency - going nuts!

11. Going Nuts

12. Going Nuts with DataGrid

13. Going nuts