locked
Help With THROW statement RRS feed

  • Question

  • Hello,

    I want to write custom stored procedure to take backup and send email from the procedure if backup fails. Most of you might be aware that when backup fails, it throws two error messages and using existing ERRORMESSAGE statment we were able to catch only last error which was not useful.

    Now i want to use THROW statement like below sample procedure:

    ----------

    create procedure backupDB

    as

    begin try
     backup database xyz
    end try
    begin catch
     declare @ErrorMessage nvarchar(2048)
     set @ErrorMessage THROW;
     
      -- Send email with this error message as content
      xp_sendemail

    end catch

    -----

    You can see that i want to catch the output of throw statement into variable just like we used to do with ERRORMESSAGE statement. However it doesn't look possible with THROW statment.

    Can you please help me with this and guide me on how to achieve this requirement?

    Thanks for the help in advance.

    -Malkesh

     

    Friday, January 31, 2014 6:31 AM

Answers

  • Obviously, if you want to send a mail, you need to do that before you use ;THROW, as ;THROW will abort the current scope on the spot. (And the current batch of if there is no outer CATCH handler.)

    If you are looking to the capture the error messages to put them into the mail, you are out of luck, as long as you are only using T-SQL. You can do it in SQL Server, if you have a CLR procedure that captures the error, but it works so-so. I know, because I implemented this for an article about error handling that I'm writing.

    More realistically, write something in C#, PowerShell, whatever you fancy that captures the errors and send the mail.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Malkesh Monday, February 3, 2014 9:46 AM
    Friday, January 31, 2014 10:02 AM

All replies

  • Satheesh, Raiserror is supposed to be used as THROW starting in 2012 SQL Server.
     If needed you can THROW a custom error message and use that same variable to show / save / server your purpose.

    refer to this below code from MSDN

    EXEC sys.sp_addmessage
         @msgnum   = 60000
    ,@severity = 16
    ,@msgtext  = N'This is a test message with one numeric parameter (%d), one string parameter (%s), and another string parameter (%s).'
        ,@lang = 'us_english'; 
    GO
    
    DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60000, 500, N'First string', N'second string'); 
    
    THROW 60000, @msg, 1; 

    Please mark as answer if this has helped you solve the issue.

    Good Luck :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    • Edited by sqlsaga Friday, January 31, 2014 7:22 AM
    • Proposed as answer by Satheesh Variath Friday, January 31, 2014 7:24 AM
    Friday, January 31, 2014 7:12 AM
  • Hi,

    Throw is used to throw an error message and not to capture error message as ERROR_MESSAGE() function can do. Throw is smillar as earlier raiserror. So you cant capture error message(s) using throw stmt

    Friday, January 31, 2014 7:16 AM
  • Hi Satheesh,

    Thanks for the reply. Basically i don't want to display the custom message. I wan to display the actual error in email notification. I work as a DBA so if i get actual message such as "Operating System Error (No disk space)" or "Network error". It would be useful as we can ask our IS team to take corrective action based on the error.

    Friday, January 31, 2014 8:14 AM
  • I think collecting all the error messages from ERROR_MESSAGE is not possible. It will get mostly the last error message.

    The below is not applicable for your scenario, but just wanted to mention.

    I do not have more idea in .NET. But, you can try to capture the messages in .NET code. Please have a look and try.

    catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    errorMessages.Append("Index #" + i + "\n" +
                        "Message: " + ex.Errors[i].Message + "\n" +
                        "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                        "Source: " + ex.Errors[i].Source + "\n" +
                        "Procedure: " + ex.Errors[i].Procedure + "\n");
                }
                Console.WriteLine(errorMessages.ToString());
            }

    Ref:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception(v=vs.110).aspx


    • Edited by SQLZealots Friday, January 31, 2014 9:02 AM
    Friday, January 31, 2014 9:02 AM
  • Obviously, if you want to send a mail, you need to do that before you use ;THROW, as ;THROW will abort the current scope on the spot. (And the current batch of if there is no outer CATCH handler.)

    If you are looking to the capture the error messages to put them into the mail, you are out of luck, as long as you are only using T-SQL. You can do it in SQL Server, if you have a CLR procedure that captures the error, but it works so-so. I know, because I implemented this for an article about error handling that I'm writing.

    More realistically, write something in C#, PowerShell, whatever you fancy that captures the errors and send the mail.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Malkesh Monday, February 3, 2014 9:46 AM
    Friday, January 31, 2014 10:02 AM
  • Oh, I forgot. Always, always, write ;THROW. THROW is one of these commands that must be preceded by a semicolon. This does not do what you might think.

    BEGIN TRY
      SELECT 1/0
    END TRY
    BEGIN CATCH
       SELECT error_message()
       THROW
    END CATCH


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 31, 2014 10:04 AM
  • Thanks Erland,

    I know, it is difficult or altogether not possible using T-SQL. However thought to put a question to the experts before confirming. 

    Thanks again.

    Monday, February 3, 2014 9:48 AM