May 14, 2012 at 3:26 pm
Is there any way to catch multiple errors with a try catch block? I'm only getting the last error. For example, I've got the following statement:
RESTORE DATABASE [TestDB]
FROM DISK = '\\BackupPath\TestDB_db_201205130107.DMP'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10,
NORECOVERY,
MOVE 'TestDB' TO 'E:\MSSQL\Data\TestDB_BACKUP.
MDF',
MOVE 'TestDB_log' TO 'E:\MSSQL\Data\TestDB_log_BACKUP.
LDF'
(Yes, I know the error is the linefeed between the dot in the filename and the extension.)
In SSMS, the following errors are returned:
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '123(The filename, directory name, or volume label syntax is incorrect.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\MSSQL\Data\TestDB_BACKUP.
MDF'.
Msg 3156, Level 16, State 8, Line 1
File 'TestDB' cannot be restored to 'E:\MSSQL\Data\TestDB_BACKUP.
MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '123(The filename, directory name, or volume label syntax is incorrect.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\MSSQL\Data\TestDB_log_BACKUP.
LDF'.
Msg 3156, Level 16, State 8, Line 1
File 'TestDB_log' cannot be restored to 'E:\MSSQL\Data\TestDB_log_BACKUP.
LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
If I use a try catch block and capture error_message(), I only get the last line: "RESTORE DATABASE is terminating abnormally."
Is there any way to get the first error statement (which is really what I needed to troubleshoot this).
May 14, 2012 at 4:45 pm
shaun.stuart (5/14/2012)
Is there any way to catch multiple errors with a try catch block?
No, not using T-SQL.
If you use a programming language like C# (what SSMS uses to run your queries by the way) to call your process you can. C# supports the notion of an exceptions collection which will stockpile the exceptions as they are raised from the database engine and then you can iterate over them and inspect them.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2012 at 8:54 am
Drat. Ok, thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply