Error Handling with the LiteSpeed XP's

by Jason Hall

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
  ,@filename ='V:\backup\database.BKP';

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!!