| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-02 : 17:38:15
|
| I was trying to track down an error in one of our internal OLAP collection processes, when I noticed something very peculiar: An insert was failing, appeared to have the proper error trapping, but was not causing an error. How does that work? Article Link. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-02 : 23:02:30
|
| insert into BigTable select * from ImportTableset @RC = @@ROWCOUNTset @err = @@ERROR>> appeared to have the proper error trapping.Where is that?It will trap any errors in set @RC = @@ROWCOUNT==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-12-02 : 23:58:15
|
| I think that is the point NigelDamian |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-03 : 05:20:00
|
| That was meant to be irony - something we have in the UK.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-12-03 : 08:05:40
|
I was being sarcastic.Something we have in Australia  Damian |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-03 : 08:59:30
|
Makes up for the lack of proper beer.First to get a berr regference in  .==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
elmes
Starting Member
1 Post |
Posted - 2001-12-11 : 03:51:35
|
| There's another bug with this example. Using select @RC = @@ROWCOUNT, @err = @@ERRORin this case still won't work as the test being done is:if @@ERROR <> 0begin rollback transaction print 'Transaction Failed'endYou need to test @err not @@ERROR. Otherwise your checking for the sucess of the select statement. |
|
|
|
tinks
Starting Member
34 Posts |
Posted - 2001-12-11 : 07:13:19
|
| Wouldnt the @@ERROR test the select @RC = @@Rowcount? To the best of my knowledge the @@ERROR would be testing the last executed SQL statement. AFAIK the @@vars test the last statement so you have to add them directly after the statement you want to test.Of course I may be wrong! Taryn-Vee@>-'-,--- |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-11 : 09:13:53
|
| You're not wrong.As elmes says"You need to test @err not @@ERROR. Otherwise your checking for the sucess of the select statement."==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-13 : 14:30:49
|
| Congratulations Chris (RocketScientist) and SQLTeam! This short article stirred enough interest to be linked to from SQLWire.com ([url]http://www.sqlwire.com/brief.asp?1783[/url]) More exposure is a good thing!-------------------It's a SQL thing... |
|
|
|
REDDY
Starting Member
43 Posts |
Posted - 2002-09-26 : 14:04:36
|
| I ALSO GOT THE SAME PROBLEM BUT SOLVED USING THE BELLOW GIVEN STATEMENT. SET XACT_ABORT OFF |
|
|
|
Dommi
Starting Member
2 Posts |
Posted - 2002-10-15 : 09:26:33
|
| Is there any way to handle a fatal error?Because I am trying to make an insert, but the inserted Data is not the datatype the table expects. It's easy to handle this error by changing the datatype of the column but I am trying to create this Table automatically... and I can not be sure that the data I am trying to insert is always the same and always in the proper format.If anyone knows an answer please let me knowDommi |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-15 : 09:38:25
|
| You can't "handle" a fatal error...just like you can't cure or heal a fatal injury. The only thing you can do is to take steps to prevent it happening (which is the best kind of error handling there is!) Regardless, it would be better for you to work at improving the quality of the data file you're importing. Get whoever is supplying it to you to clean it up.Failing that, import the file into a generically formatted staging table. This table has no constraints, allow nulls, etc., and stores the data as varchar. Then you can apply CAST and CONVERT functions to change the data over to its proper data type and then INSERT it into the final destination table. This is a fairly common technique and is much faster and more reliable than trying to construct the "right" table on the fly.Edited by - robvolk on 10/15/2002 09:39:55 |
|
|
|
Dommi
Starting Member
2 Posts |
Posted - 2002-10-15 : 09:59:42
|
| I am not able to get better data, it is not in my responsibility. But maybe it is not a fatal-error if i try an Alter-Table statement with bad data... So lets try and learnDommibtw. Thanks for your quick reply |
|
|
|
|
Article: How to NOT trap errors in a stored procedure