Error Handling with the LiteSpeed XP’s
One question that comes up quite a bit pertains to how you can handle errors generated by the LiteSpeed Extended Stored Procedures (XP’s) in your own custom scripts. Scripting with XP’s is fairly straight forward, because they accept parameters just like any other stored proc, but how they handle errors is a bit different. Unlike standard SQL Statements that will populate @@error and can be handled with TRY/CATCH blocks, XP’s simply return an error code. By capturing the value returned by an XP you can succesfully trap and code around many types of errors. View the following block of code to show how this is done. In this example, the drive I am trying to backup to does not exist, therefore the backup fails with error code 50003.
DECLARE @rc INT EXEC @rc = master.dbo.xp_backup_database @DATABASE='master' ,@filename ='V:\backup\database.BKP'; SELECT @rc
I know some folks out there have done some pretty cool things scripting with LiteSpeed. If anyone has any scripts they’d like to share, we’d love to take a look!!
Tags: litespeed
February 26th, 2010 at 9:50 AM
I’ve tried your script and it won’t catch the error. The xp_backup_database SP throws an error so it will never get to your SELECT. How can this be used to catch errors?
February 26th, 2010 at 5:07 PM
What error is xp_backup_database throwing? As long as its not a syntax error, xp_backup_database should return its error to the @rc variable. Make sure you are viewing the results tab in SSMS and not the messages tab.