July 26, 2016 at 3:30 am
Hi,
I'm using SQL Sever 2014. I'm wondering is it possible to get the ERROR_MESSAGE() statement to display the full error message. I know it only displays the last error if there is more than 1 but I would really like to capture all the error if more than 1 exists inside the catch area ofa sql try catch?
Comments appreciate,
J.
July 26, 2016 at 5:34 am
jellybean (7/26/2016)
Hi,I'm using SQL Sever 2014. I'm wondering is it possible to get the ERROR_MESSAGE() statement to display the full error message. I know it only displays the last error if there is more than 1 but I would really like to capture all the error if more than 1 exists inside the catch area ofa sql try catch?
Comments appreciate,
J.
If your code is using TRY ... CATCH, control is passed to CATCH on the first error ... so there are not multiple messages to be caught. Or am I missing something? Can you provide sample code to demonstrate your request?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 26, 2016 at 6:43 am
Lets use a database restore as an example:
RESTORE DATABASE TestRestore
FROM DISK = 'SomeCorrectPath'
WITH RECOVERY,
MOVE 'WrongDataName' TO 'D:\SQL2014\Data\TEMPCOPY.mdf',
MOVE 'WrongLogFile' TO 'E:\SQL2014\Logs\TEMPCOPY.ldf'
The above will throw an error as the logical file names are wrong. The error thrown is:
Msg 3234, Level 16, State 2, Line 17
Logical file 'WrongDataName' is not part of database 'TestRestore'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 17
RESTORE DATABASE is terminating abnormally.
Putting this in a try catch does not help as it only displays the last error as below indicates:
BEGIN TRY
RESTORE DATABASE TestRestore
FROM DISK = 'SomeCorrectPath'
WITH RECOVERY,
MOVE 'WrongDataName' TO 'D:\SQL2014\Data\TEMPCOPY.mdf',
MOVE 'WrongLogFile' TO 'E:\SQL2014\Logs\TEMPCOPY.ldf'
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState int= ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
So, ERROR_MESSAGE() only returns the last error. Which is a pity. I'm using Sql server 2014 - surely this can be improved? I want to return as much debugging information as possible...
Comments/suggestions - much appreciated,
J.
July 29, 2016 at 1:13 pm
Since you're using 2014, use THROW instead of RAISERROR:
BEGIN TRY
RESTORE DATABASE TestRestore
FROM DISK = 'SomeCorrectPath'
WITH RECOVERY,
MOVE 'WrongDataName' TO 'D:\SQL2014\Data\TEMPCOPY.mdf',
MOVE 'WrongLogFile' TO 'E:\SQL2014\Logs\TEMPCOPY.ldf';
END TRY
BEGIN CATCH
THROW;
END CATCH;
The downside of this approach is that THROW will terminate the batch at that point, so if there's code after this that has to execute, this won't help.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply