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
3013 BACKUP 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