0

I've a trigger in a sql server database with a very strange behavior, these are the strange instructions:

...
EXEC  [ope].[spMissionDepartment] @AircraftId = @vnAircraftId

RAISERROR('test error', 16, 1)

END TRY 
BEGIN CATCH
    SELECT @ErrorMessage = ERROR_MESSAGE()

    ROLLBACK TRANSACTION

    RAISERROR(@ErrorMessage, 16, 1) -- strange line

END CATCH

The strange behavior is this:
If I remove the strange line than I receive in web page the error

TIMEOUT EXPIRED. THE TIMEOUT PERIOD ELAPSED PRIOR TO COMPLETION OF THE OPERATION OR THE SERVER IS NOT RESPONDING.

otherwise, if I don't remove the strange line, I receive the error

test error

There are no other triggers, so the exec should be the last statement executed, how is possible that I receive a Timeout only without the Raiserror?

davidinho
  • 169
  • 1
  • 14
  • try this at strange line ` RAISERROR(@ErrorMessage, 9, 1)` – Anurag Dadheech Apr 14 '17 at 12:51
  • But if I use 9 as severity it will continue, and so is like there isn't a _Raiserror_, the strange behavior is that the _Raiserror_ should be the last statement executed, so why there is a timeout after it? – davidinho Apr 14 '17 at 13:07
  • Maybe this can help you http://sqlhints.com/2013/06/30/differences-between-raiserror-and-throw-in-sql-server/ – GuidoG Apr 14 '17 at 13:07
  • I read the article but I've a try catch, so the execution will not continue, and this is correct, the strange thing is that I receive a timeout only without the _Raiserror_ but there is nothing after it. So what is going in timeout? – davidinho Apr 14 '17 at 13:12
  • What happens if you add a FINALLY block with another test error? – Tab Alleman Apr 14 '17 at 13:19
  • Exists a FINALLY block in T-Sql? – davidinho Apr 14 '17 at 13:50

1 Answers1

0

Errors Unaffected by a TRY…CATCH Construct TRY…CATCH constructs do not trap the following conditions:

  1. Warnings or informational messages that have a severity of 10 or lower.
  2. Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  3. Attentions, such as client-interrupt requests or broken client connections.
  4. When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  1. Compile errors, such as syntax errors, that prevent a batch from running.
  2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
Anurag Dadheech
  • 629
  • 1
  • 5
  • 14
  • How does this help? Are you suggesting that he is getting an error that isn't causing the CATCH block to be executed? If that's the case, then why would removing a line in the CATCH block change the behavior? – Tab Alleman Apr 14 '17 at 13:18
  • do one more thing just manually execute the proc in sql studio and put print statement in every step. then figure out on which statement it will take time ... – Anurag Dadheech Apr 14 '17 at 13:24