|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
XACT_ABORT AND char insert in INT datatypeHi,
I have a series of insert statements and if it fails he has to rollback the transaction, do logging and set the current row in status 99. So after each insert statement I check @@ERROR to see if it failed. If the statement failed I will do all the above things. This works fine when my insert has a violation agains a constrain. But it doesn't work when my insert tries to put a varchar in a integer column. (value: '57.088' Then the procedure just stops after the statement. Is there a way to work around this? regards, NIco I suggest you read these articles by Erland Sommarskog:
http://www.sommarskog.se/error-handling-I.html http://www.sommarskog.se/error-handling-II.html ML --- http://milambda.blogspot.com/ Some errors will abort the batch. See http://www.sommarskog.se/ for a good
discussion on SQL Server error handling. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "verbani" <verb***@discussions.microsoft.com> wrote in message news:64076C90-7F4F-449E-B222-5814BA66EB39@microsoft.com... > Hi, > > I have a series of insert statements and if it fails he has to rollback > the > transaction, do logging and set the current row in status 99. > > So after each insert statement I check @@ERROR to see if it failed. If > the > statement failed I will do all the above things. This works fine when my > insert has a violation agains a constrain. But it doesn't work when my > insert tries to put a varchar in a integer column. (value: '57.088' Then > the > procedure just stops after the statement. > > Is there a way to work around this? > > regards, > NIco YOu are probably getting a batch abort. and as the previous poster suggests,
Erlands articles are the best place to learn about this. -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "verbani" wrote: > Hi, > > I have a series of insert statements and if it fails he has to rollback the > transaction, do logging and set the current row in status 99. > > So after each insert statement I check @@ERROR to see if it failed. If the > statement failed I will do all the above things. This works fine when my > insert has a violation agains a constrain. But it doesn't work when my > insert tries to put a varchar in a integer column. (value: '57.088' Then the > procedure just stops after the statement. > > Is there a way to work around this? > > regards, > NIco Well, don't do that! Your application should scrub the data before it's
presented to the database. Rollbacks are expensive. In addition, any procedures should verify that any parameters contain valid information prior to touching any other database objects. Note: don't take this to mean that you shouldn't have constraints on the database too. Show quote "verbani" <verb***@discussions.microsoft.com> wrote in message news:64076C90-7F4F-449E-B222-5814BA66EB39@microsoft.com... > Hi, > > I have a series of insert statements and if it fails he has to rollback > the > transaction, do logging and set the current row in status 99. > > So after each insert statement I check @@ERROR to see if it failed. If > the > statement failed I will do all the above things. This works fine when my > insert has a violation agains a constrain. But it doesn't work when my > insert tries to put a varchar in a integer column. (value: '57.088' Then > the > procedure just stops after the statement. > > Is there a way to work around this? > > regards, > NIco  |
|||||||||||||||||||||||
Other interesting topics