November 2, 2011 at 2:28 am
hi,
i have an update statement with multiple joins which is updating say 100 rows.
If the whole command is failing due to corrupt data in one of the rows , then what is the simplest way to ignore and proceed with the remaining 99 updates?
Thanks in Advance.:-)
November 2, 2011 at 2:35 am
please let me know if using try,catch can help..
November 2, 2011 at 2:40 am
Depends how do u define failure here... a failure is , say, datatype mismatch or failure to find a matching row ?
November 2, 2011 at 2:42 am
If you've got corrupt data in your database then I would recommend that you deal with that before anything else. Have a read about DBCC CHECKDB if you haven't used it before.
John
November 2, 2011 at 2:43 am
If that's a single statement update, then it must succeed or fail as a single operation, not partially succeed and partially fail. That's required by the ACID rules of relational databases.
What's the error and what's the statement?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2011 at 3:23 am
if error occurs it rollbacks right?
===============================================================
"lets do amazing" our company motto..
November 2, 2011 at 4:13 am
thanks... the error was due to data type mismatch where the SQL is trying to convert an alphanumeric valkues into integer before comparing.
I have resolved it using ISNUMERIC() function , however, i would still like to know if there is any way i can find a specific row(s) which has such kind of issues in an Update statement and then ignore it from being updated.I'm allowed to write multiple TSQL commands.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply