Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
 Posted - 2006-08-08 : 23:21:23
|
| Hi all, I've got a large script that performs schema updates. The central functionality is taken care of inside a transaction, and there are GOTO statements to handle errors and rollback the transactions. I'm having a problem at the moment whereby the error handling code seems to be skipped and processing continues after the next GO statement. The error is currently occuring when I try to apply a FK constraint to the database.Does anyone have an idea as to why it might be happening?I should also note that the FK constraint is being applied using dynamic SQL (using a call to EXEC @Sql)btw it's a SQL 2000 db running on XP ProAny help appreciated,Cheers,Tim |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-08 : 23:34:59
|
| how about you post your code so we can have a look at it. -ec |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-08-08 : 23:38:00
|
| It's a bit big. And I doubt you'd be able to reproduce anyway without the existing db.The main thing I'm asking is "Is there any instance where SQL Server will automatically fail a script without first allowing me to check the @@ERROR code?" |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-08 : 23:43:17
|
quote:
Originally posted by timmy
It's a bit big. And I doubt you'd be able to reproduce anyway without the existing db.The main thing I'm asking is "Is there any instance where SQL Server will automatically fail a script without first allowing me to check the @@ERROR code?"
yes, error handling is terrible in TSQL. I have had problems where return codes are not correct from a stored proc or the execution of a procedure completely halts when an error occurs. I have always been able to work around these kinds of issues though.Can you paste the code you have for error handling? maybe it can be improved.-ec |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-08-08 : 23:43:36
|
OK - this illustrates the problem:ALTER TABLE [dbo].[cal] ADD CONSTRAINT [cal_FK01] FOREIGN KEY ([cal_cnty_code]) REFERENCES [dbo].[cnty] ([cnty_code])IF @@ERROR <> 0 GOTO catchELSe GOTO finallycatch: Print 'Something broke'finally:GOPrint 'This is the first statement after GO' To reproduce, just replace the ALTER TABLE statement to add a FK that already exists.Basically I want to make sure that the error handling code is run. I know there is a way around this problem (check that the FK exists first), but I'm curious to see that why the script is being aborted |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-09 : 16:07:00
|
Timmy,This is a logical order problem. What's happening is that your error code is working fine; the problem is that, after going to the catch: label, it's continuing to execute (which means it executes the GO and Print statements after finally.You can fix it somewhat by adding yet another label:IF @@ERROR <> 0 then GOTO catchELSE GOTO finallycatch: Print 'Something broke' GOTO donefinally: GOdone: Ken |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-08-09 : 17:50:33
|
| Ken, The but my issue is that the IF statement is not being run at all. The example I gave is simply to illustrate that problem and try and see if anyone knew how to fix it. btw - your example above doesn't work because you can't GOTO over a GO statement. In other words, your labels and their related GOTO statements need to be in the same script batch (i.e. between GO's).Cheers,Timps - I ended up fixing the underlying problem. The error wasn't due to a pre-existing constraint it was trying to create the FK where there was dodgy data. So I added the NOCHECK clause to my ALTER TABLE statement. Dirty but it works. But it still leaves me wondering about how SQL handles it's errors. |
|
|
|
|
|
|
|
|
Error handling skipped