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 Replication how to continue

I have transactional Replication from A to B.

My distribution agent failed becos of a primary key violation error in a table.[i have not included the skip error option].

Then the replication stopped.
The transactions on other tables are also not replicated to B.

IF an error is encountered in one of the tables will the replication stop entirely??.Is there no way for the replication to continue for the rest of the tables..
Pls clarifyI don't think you can skip this error. To use Replication you have to have a primary key in the table you want to replicate.|||The transactions are processed in a serialized fashion, meaning that if one failed nothing else goes through.

Error in my 8 table SQL Script

/*Script Start*/

create database HorizonAirways

create table Sector
(
SectorID Char(5) constraint SectorID primary key clustered not null,
Description VarChar(50) not null,
WeekDay1 char(3) not null,
WeekDay2 char(3) not null,
FirstClassFare Money not null,
BusinessClassFare Money not null,
EconomyClassFare Money not null
)

select * from Sector

create table Aircraft
(
AircraftTypeID char(4) constraint AircraftTypeID primary key clustered not null,
Description char(30) not null,
FirstClassSeats int not null,
BusinessClassSeats int not null,
EconomyClassSeats int not null
)
select * from Aircraft

create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)

select * from Flights

create table ScheduledFlights
(
FlightNo char(5) references flights(FlightNo) not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)

select * from ScheduledFlights

create table passenger
(
PnrNo char(8) constraint PNR primary key clustered not null,
FlightNo char(5) references flights(FlightNo) not null,

TravelDate datetime constraint PassengerFlights Foreign key (FlightNo, DeptTime) references flights (FlightNo,DepTime) not null,

FName char(20) not null,
LName char(20) not null,
Age int not null,
Gender char(1) not null,
Class char(15) not null,
SeatPref char(6) not null,
MealPref char(15) not null,
SSR varchar(100) not null,
Status char(15)
)

create table DailyCollection
(
PnrNo char(8) references Passenger(PnrNo) not null,
TransDate Datetime not null,
TranType Char(1) not null,
Amount Float not null
)

create table users
(
UserName char(15) constraint Username primary key clustered not null,
Password char(15) not null,
UserRole Char(15) not null
)

!!OUTPUT!!

--
Msg 8140, Level 16, State 0, Line 48
More than one key specified in column level FOREIGN KEY constraint, table 'passenger'.
Msg 1769, Level 16, State 1, Line 48
Foreign key 'PassengerFlights' references invalid column 'DeptTime' in referencing table 'passenger'.
Msg 1750, Level 16, State 0, Line 48
Could not create constraint. See previous errors.
--

--
In the project given by our teacher it says about Passenger Table
Pnr(PK)- Char (8) no null values

FlightNo- Char(5) no null values

TravelDate- DateTime no null values
info-Date of travel. The flight number and the date of travel together gorm a foreign key that references the flight number and the flight date in theFlight Table.
--
rest is fine.....

CAN ANYONE PLEASE RECTIFY THESE ERRORS ?
There are some typos in your script as well as problem with contraints. My preferable way to define constraints is to use separate statements instead of inline within the table creation script. I addition you can only reference primary keys (one or more column as a combound key) with a foreign key constraint. So you will either have to extend the primary key in the flights table to the DeptTime ot narrow the foreign key on the passenger table to flightNo only. As of my knowledge about flight it has to be the first solution, right ? :-)

create table passenger

(

PnrNo char(8) constraint PNR primary key clustered not null,

FlightNo char(5) not null,

FName char(20) not null,

LName char(20) not null,

Age int not null,

Gender char(1) not null,

Class char(15) not null,

SeatPref char(6) not null,

MealPref char(15) not null,

SSR varchar(100) not null,

Status char(15),

TravelDate datetime

)

ALTER TABLE Passenger

ADD CONSTRAINT FK_Passenger_Flights FOREIGN KEY (FlightNo, TravelDate)

references flights(FlightNo, DepTime)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thank you very much.

In future i will use your way. I will define constraints separately. Cheers !!
|||Great reply and its works..

Error in my 8 table SQL Script

/*Script Start*/

create database HorizonAirways

create table Sector
(
SectorID Char(5) constraint SectorID primary key clustered not null,
Description VarChar(50) not null,
WeekDay1 char(3) not null,
WeekDay2 char(3) not null,
FirstClassFare Money not null,
BusinessClassFare Money not null,
EconomyClassFare Money not null
)

select * from Sector

create table Aircraft
(
AircraftTypeID char(4) constraint AircraftTypeID primary key clustered not null,
Description char(30) not null,
FirstClassSeats int not null,
BusinessClassSeats int not null,
EconomyClassSeats int not null
)
select * from Aircraft

create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)

select * from Flights

create table ScheduledFlights
(
FlightNo char(5) references flights(FlightNo) not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)

select * from ScheduledFlights

create table passenger
(
PnrNo char(8) constraint PNR primary key clustered not null,
FlightNo char(5) references flights(FlightNo) not null,

TravelDate datetime constraint PassengerFlights Foreign key (FlightNo, DeptTime) references flights (FlightNo,DepTime) not null,

FName char(20) not null,
LName char(20) not null,
Age int not null,
Gender char(1) not null,
Class char(15) not null,
SeatPref char(6) not null,
MealPref char(15) not null,
SSR varchar(100) not null,
Status char(15)
)

create table DailyCollection
(
PnrNo char(8) references Passenger(PnrNo) not null,
TransDate Datetime not null,
TranType Char(1) not null,
Amount Float not null
)

create table users
(
UserName char(15) constraint Username primary key clustered not null,
Password char(15) not null,
UserRole Char(15) not null
)

!!OUTPUT!!

--
Msg 8140, Level 16, State 0, Line 48
More than one key specified in column level FOREIGN KEY constraint, table 'passenger'.
Msg 1769, Level 16, State 1, Line 48
Foreign key 'PassengerFlights' references invalid column 'DeptTime' in referencing table 'passenger'.
Msg 1750, Level 16, State 0, Line 48
Could not create constraint. See previous errors.
--

--
In the project given by our teacher it says about Passenger Table
Pnr(PK)- Char (8) no null values

FlightNo- Char(5) no null values

TravelDate- DateTime no null values
info-Date of travel. The flight number and the date of travel together gorm a foreign key that references the flight number and the flight date in the Flight Table.
--
rest is fine.....

CAN ANYONE PLEASE RECTIFY THESE ERRORS ?There are some typos in your script as well as problem with contraints. My preferable way to define constraints is to use separate statements instead of inline within the table creation script. I addition you can only reference primary keys (one or more column as a combound key) with a foreign key constraint. So you will either have to extend the primary key in the flights table to the DeptTime ot narrow the foreign key on the passenger table to flightNo only. As of my knowledge about flight it has to be the first solution, right ? :-)

create table passenger

(

PnrNo char(8) constraint PNR primary key clustered not null,

FlightNo char(5) not null,

FName char(20) not null,

LName char(20) not null,

Age int not null,

Gender char(1) not null,

Class char(15) not null,

SeatPref char(6) not null,

MealPref char(15) not null,

SSR varchar(100) not null,

Status char(15),

TravelDate datetime

)

ALTER TABLE Passenger

ADD CONSTRAINT FK_Passenger_Flights FOREIGN KEY (FlightNo, TravelDate)

references flights(FlightNo, DepTime)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thank you very much.

In future i will use your way. I will define constraints separately. Cheers !!|||Great reply and its works..

Error in Identity Column

For some reason my primary key, identity column skipped a couple of numbers. It went from row 734 to 736, 737, 739

Any ideas why this would happen?

thanks

hi

735 & 738 must've been created but deleted somehow.

|||

No, it was not deleted

|||

Rick0194:

No, it was not deleted

Yes they were, that's the only way it could have been skipped. Now, it may be that they get inserted as part of a transaction and something goes wrong and rolls it back -- the identity colum's value will appear to skip the next time a good insert occurs (I just confirmed that on sql 2000)

Error in : MSmerge_tombstone

I when i am try to update data in one table having replication, it gives me the error msg below;

cannot insert duplicate key row in object 'MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'

why this happens? pls. advise how i can get rid of this message.

I discovered that the table MSmerge_tombstone on subscriber is having triggers. I removed this trigger and the problem doenot reapper. Is it advisable to do that? Pls. advise.

Thanks,

I do not think removing triggers is good idea, MSmerge_tombstone table is used by replication process to track the rows that have been deleted on the subscriber
|||Could you pls. advise how can i get rid of this error?

Error help when deploying a cube

I'm getting an error message and i'm not sure what it's trying to tell me.

Errors

Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_rpt_vr_flat_cmb_ih_so, Column: idcol, Value: 441183. 0 0

Warning 2 Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Rpt Vr Flat Cmb Ih So of Dimension: Rpt Vr Flat Cmb Ih So from Database: sdw4, Cube: SDW, Measure Group: Rpt Vr Flat Cmb Ih So, Partition: Rpt Vr Flat Cmb Ih So, Record: 441183. 0 0

Error 3 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0

Error 4 Errors in the OLAP storage engine: An error occurred while processing the 'Rpt Vr Flat Cmb Ih So' partition of the 'Rpt Vr Flat Cmb Ih So' measure group for the 'SDW' cube from the sdw4 database. 0 0

Error 5 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0

-background-

My cube is just one flat table the only linking it has is to a time table. This table is put together over night for users to query off of, and when we built this table a primary key wasn't a option so we added a auto incermenting col just to give us a key and that is the key is called idcol which i think is telling me something is wrong with that record. but i'm not sure.

suggestions?

Hello! A guess is that you have a factrecord with a time key that is not in the time table/flat file.

HTH

Thomas Ivarsson

error handling with linked servers

Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
Stefano
The batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano
|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano
|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano
|||ok, thanks!

error handling with linked servers

Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
StefanoThe batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano|||ok, thanks!

Error Handling in SQL?

Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LX
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>

Error Handling in SQL?

Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>

Error Handling in SQL?

Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
--
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>