November 7, 2014 at 1:43 pm
Hello Mates
I got this situation and i don't know what is happening so, i will share my case and i hope some1 explain to me What's wrong with this query??? :w00t:
1. Alright let's start, at first point lets create an example table in your database
Create Table myTable1 (Id Int Identity(1,1), Field1 Int Default(1), Field2 Char(3))
2. I will insert a record to populate this table
Insert Into myTable1 (Field2) Values ('A')
3. Well, at this point, i going to generate 3 kind of diferent errors
3.1 Trying to Insert a value into the identity field.
Begin Try
Insert Into myTable1 (Id) values (5)
End Try
Begin Catch
Select 'This is an Error!! - ' + ERROR_MESSAGE()
End Catch
3.2 Changing the type of the "CHAR" field to INT Type. (Wonder that we already have inserted a record)
Begin Try
Alter Table myTable1 Alter Column Field2 Int
End Try
Begin Catch
Select 'This is an Error!! - ' + ERROR_MESSAGE()
End Catch
3.3 Last try, in this case i going to create the default over the field "Field1" obviusly this statment will cause an error because this already have a default but guess why the catch doesn't work or don't generate the select :unsure:
Begin Try
Alter Table myTable1 Add Constraint Default_On_Field1_With_1 Default 1 For Field1
End Try
Begin Catch
Select 'This is an Error!! - ' + ERROR_MESSAGE()
End Catch
November 7, 2014 at 2:07 pm
I can't find the documentation, but I'm sure that it's because that's a parsing error while the others are execution errors.
November 7, 2014 at 2:38 pm
It's not a parsing error, if you type the statement outside the try/catch you will figure it's a normal execution error, even if you execute it within a Exec('') you might figure that catch section works.
Begin Try
Exec('Alter Table myTable1 Add Constraint Default_On_Field1_With_1 Default 1 For Field1 ')
End Try
Begin Catch
Select 'This is an Error!! - ' + ERROR_MESSAGE()
End Catch
At the end, the question is very simple, what is wrong with the statement that makes the try dont catch this error when is not inside an EXEC or works like another normal error??¿Is because it's a DML statement?
Regards
November 7, 2014 at 11:02 pm
It's a batch-terminating error. The ALTER TABLE error ends the batch immediately at the point it occurs (ends the batch, not the connection), hence it cannot be caught by a TRY CATCH within the same batch scope.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply