trapping error

• RightOnTarget

  Hall of Fame

  Points: 3041

  More actions

  October 8, 2015 at 11:24 am

  #321181

  Hello,

  I would like to implement a logic that will allow me to switch to prod server if replication is not available.

  I am trying to trap the error, but for some reason my catch block won't catch it.

  What am I doing wrong?

  BEGIN TRY

  print 'In try block'

  select COUNT(*)

  from [test].rep_data.dbo.contacts

  END TRY

  BEGIN CATCH

  print 'In catch block'

  print 'Replication is not available. Running query against prod'

  select COUNT(*)

  from [prod].rep_data.dbo.contacts

  SELECT ERROR_NUMBER() AS ErrorNumber

  ,ERROR_SEVERITY() AS ErrorSeverity

  ,ERROR_STATE() AS ErrorState

  ,ERROR_PROCEDURE() AS ErrorProcedure

  ,ERROR_LINE() AS ErrorLine

  ,ERROR_MESSAGE() AS ErrorMessage;

  END CATCH

  Msg 18456, Level 14, State 1, Line 1

  Login failed for user 'report_user'.

  Thanks,

• SoHelpMeCodd

  SSCertifiable

  Points: 5871

  More actions

  October 11, 2015 at 7:13 am

  #1832707

  You need to SET XACT_ABORT ON. See https://msdn.microsoft.com/en-us/library/ms191515.aspx