TRY..CATCH For multiple errors

• beath

  SSCrazy

  Points: 2939

  More actions

  April 12, 2010 at 4:51 am

  #146413

  Hi

  If you run:

  backup database xxx to disk = 'c:\xxx.bak'

  It returns:

  Msg 911, Level 16, State 11, Line 1

  Could not locate entry in sysdatabases for database 'xxx'. No entry found with that name. Make sure that the name is entered correctly.

  Msg 3013, Level 16, State 1, Line 1

  BACKUP DATABASE is terminating abnormally.

  However running:

  begin try

  backup database xxx to disk = 'c:\xxx.bak'

  end try

  begin catch

  select error_message()

  end catch

  Only returns BACKUP DATABASE is terminating abnormally.

  How can I capture the first of those errors in my try..catch routine. It's not a show stopper but in this case the important message is the first one which isn't returned.

  cheers

• Paul White

  SSC Guru

  Points: 150468

  More actions

  April 12, 2010 at 5:05 am

  #1149914

  The ERROR functions just return information for the last error - much the same way as @@ERROR and sys.messages work. I believe there is a Connect item open for the ability to 'walk the stack' of errors, but nothing in current products, as far as I know.

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi