Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, that should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value for the identity
column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

Error inserting record in IDENTITY column

Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT

error in ssis package!

Hi,

[OLE DB Destination [1146]] Error: An OLE DB error has occurred. Error code: 0x80040E23. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E23 Description: "Cursor operation conflict".

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (1146) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.

have any of u encountered this error?

package is working good on source side but destination is indicated in red with the above error,it was working good yesterday,not sure what happened today all of a sudden.

pls help!

Regards,

sg

That's an error returned from SQL Server so I would suggest the problem is in SQL Server rather than SSIS. That's not much help I know but hopefully it gives you some pointes about where to to investigate.

Google turned these up:

http://blogs.conchango.com/kristianwedberg/archive/2006/03/08/3045.aspx (from my friend and fellow Conchango-ite Kristian Wedberg)

http://support.microsoft.com/default.aspx/kb/324900

-Jmie

|||

Hi jamie,

Thanks a lot,ur information was valuable.The package is working.

|||

Cool. If it proved to answer your problem, please could you mark it as an answer.

Thanks.

sql

Error in deleting

When I am trying to delete the record from flat table it gave me following
error and that record doesn't have a constraints / triggers so why it's
giving me this error.
SQL Server Enterprise Manager
Key column information is insufficient or incorrect. Too many rows were
affected by update.
OK Help
Thanks in advance.
you don't have a unique identifier/key
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> When I am trying to delete the record from flat table it gave me following
> error and that record doesn't have a constraints / triggers so why it's
> giving me this error.
> --
> SQL Server Enterprise Manager
> --
> Key column information is insufficient or incorrect. Too many rows were
> affected by update.
> --
> OK Help
> --
> Thanks in advance.
>
>
|||You need to have a primary key.
"Rogers" wrote:

> When I am trying to delete the record from flat table it gave me following
> error and that record doesn't have a constraints / triggers so why it's
> giving me this error.
> --
> SQL Server Enterprise Manager
> --
> Key column information is insufficient or incorrect. Too many rows were
> affected by update.
> --
> OK Help
> --
> Thanks in advance.
>
>
|||you need to have a primary key on that table .
"Rogers" wrote:

> When I am trying to delete the record from flat table it gave me following
> error and that record doesn't have a constraints / triggers so why it's
> giving me this error.
> --
> SQL Server Enterprise Manager
> --
> Key column information is insufficient or incorrect. Too many rows were
> affected by update.
> --
> OK Help
> --
> Thanks in advance.
>
>

Error Handling in Sql Server 2000

I am having a SP (Stored Procedure) I want to handle the error in any case for the SP. My SP is inserting a record in a table when I had changed the Column name of the Table the error is not traced but the error is directly displayed in the TSql. How to Handle any error that occured in the SP? Thanks in Advance.

CREATE PROCEDURE SP_InvoiceFromRBOPInvNoHdr @.AutoBillID int, @.NextBillDate DateTime, @.OPInvNo int OutPut
AS
declare @.InvNo int
declare @.CustID int
declare @.InvAdd1 varchar(50)
declare @.InvAdd2 varchar(50)
declare @.city varchar(50)
declare @.Postal varchar(20)
declare @.Country varchar(50)
declare @.Province varchar(50)
declare @.BillDate datetime
declare @.BillingContact varchar(50)
declare @.BillingCurrencyCode varchar(5)
declare @.StoreID int
declare @.BillingPeriod varchar(10)
declare @.BillingTime varchar(10)
declare @.comment varchar(100)
declare @.TotalBillAmount float
declare @.TotalTax1 float
declare @.TotalTax2 float

declare @.err int

if exists(select * from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID)
begin
select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory

select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,
@.BillDate = NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime
from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID

Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))
exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output
--BillInvoiceHeaderHistory
insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,
Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,
AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,
@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,
@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)

select @.err = @.@.ERROR
if @.err != 0
begin
--Error Occured
--Set @.OPInvNo = -1
return @.err
end
else
begin
--No Errors
Set @.OPInvNo = @.InvNo
end
end
GO

Error handling in sqlserver2000 is really a pain, you have to check the @.@.ERROR after any statement that may fail.

declare @.err int

select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory

set @.err = @.@.error

if (@.err != 0) begin ... end

select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,

@.BillDate

=

NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime

from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID

set @.err = @.@.error

if (@.err != 0) begin ... end

Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))

set @.err = @.@.error

if (@.err != 0) begin ... end

exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output

set @.err = @.@.error

if (@.err != 0) begin ... end

--BillInvoiceHeaderHistory

insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,

Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,

AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,

@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,

@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)

set @.err = @.@.error

if (@.err != 0) begin ... end

|||

I had changed the column name of the table and this error is what I am unable to handle. The SP stops in the insert statement only. I had changed the column BillingAddress2 to BillingAddress23 and I am getting this error :

Server: Msg 207, Level 16, State 1, Procedure SP_InvoiceFromRBOPInvNoHdr, Line 39
Invalid column name 'BillingAddress2'.

|||

Check this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=944372&SiteID=1

|||

Just to add my 2 cents worth, Error Handling in SQL Server 2000 was pretty weak. Check this article: http://www.sommarskog.se/error-handling-I.html for a good bit of information about this, and this one that is about stored procedures: http://www.sommarskog.se/error-handling-II.html. Bottom line, in 2000 every error was sent to the client. In 2005, you can take care of some of this with TRY..CATCH, but not all.

I don't think even 2005 will help you one a column name change, this is something that you need to manage in your code/testing.

|||

There is no way in 2000 or 2005 to trap an errror if the column does not exist. The error trapping in 2000 and 2005 is almost non-existent.

You cannot trap a non-existent column in either version.

|||

I wouldn't put it that way: "The error trapping in 2000 and 2005 is almost non-existent"

There are only a few errors that cannot be trapped, and I am not even sure if it would be a good idea for this kind of error to be trapped. This is a compilation error, so more or less the code cannot be executed. SQL is a very flexible language (exec ("select ...") for example) but it shouldn't be that flexible. It does catch most errors that it should and is 1000 times better than in 2000.