Capturing all Error messages caused by SQL Server code

• Lonely Rogue

  SSCrazy

  Points: 2579

  More actions

  November 18, 2019 at 3:28 pm

  #3698561

  Hello -

  The ERROR_MESSAGE() is capturing only the last message in the error stack but nothing preceding it. But I want to capture all the errors that is seen on SSMS when a query fails to write it to my Log Table.

  In my SQL Server 2017, the below BACKUP command is failing and SSMS shows up 2 error messages - Error 3202 & 3013.

  BACKUP DATABASE [Promodag] TO DISK = N'\\ServerX\SQL\DBA_Full.bak' WITH INIT, CHECKSUM, COMPRESSION,STATS=10
  
  Error: 
  Msg 3202, Level 16, State 1, Line 1
  Write on "\\ServerX\SQL\DBA_Full.bak" failed: 112(There is not enough space on the disk.)
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.
  

  But when executed in TRY CATCH, to capture the error, the

  BEGIN TRY 
  BACKUP DATABASE [Promodag]  TO DISK = N'\\ServerX\Backup\DBA_Full.Bak' WITH INIT, CHECKSUM, COMPRESSION,STATS=10
  END TRY
  BEGIN CATCH
  SELECT ERROR_NUMBER(),ERROR_MESSAGE() AS ErrorMessage
  END CATCH
  
  Output as table :
  
  (No column name)ErrorMessage
  3013BACKUP DATABASE is terminating abnormally.

  So, please help on how to concatenate all the error messages in the CATCH block.

  --In 'thoughts'...

  --In 'thoughts'...
  Lonely Rogue

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 18, 2019 at 3:35 pm

  #3698571

  This is the only way that I know of doing it.

  John