0

Here's my problem's background: I have a couple of merge queries to UPSERT in my SQL Server tables (1 merge query per table). I converted them into stored procedures and loaded their names in a table which I am then executing via my SSIS package.

The problem is I want to embed error handling/status update in each of my stored procedures. I want it to email me the output if the merge query is successful, otherwise, send me the error in the email (as an attachment or printed in the email body).

I researched and found a catch block but how do I switch between success and failure?

I have tried, using sp_send_dbmail twice, once in try block to send me output and once in the catch block to send me error, I tried printing the error like SELECT ERROR_MESSAGE() and it printed this text - I know it would print the text. I thought I am in the right direction so declared a variable @errmsg to contain the error message but where do I set the value for this variable? I tried setting it in the catch block but it gives me error. Please advise.

        SET @Output = @@ROWCOUNT  
    END 
    ELSE 
    BEGIN 
        EXEC msdb.dbo.sp_send_dbmail
                  @profile_name = 'Notifyme',
                  @recipients = 'johnkovich@abc.me',
                  @body = '@output',
                  @subject = 'Testmsg';
    END 
END TRY
BEGIN CATCH
    SET @ERRMSG = ERROR_MESSAGE()

    EXEC msdb.dbo.sp_send_dbmail
              @profile_name = 'Notifyme',
              @recipients = 'johnkovich@abc.me',
              @body = @ERRMSG,
              @subject = 'Testmsg2'
END CATCH
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mehek
  • 1
  • 2
    `it gives me error` And the error message is ? – Squirrel Dec 15 '20 at 05:57
  • 1
    Have you declared `@ERRMSG` somewhere? The code sample doesn't show a declaration. – AlwaysLearning Dec 15 '20 at 06:00
  • @Squirrel Msg 22050, Level 16, State 1, Line 2 Failed to initialize sqlcmd library with error number -2147467259. – Mehek Dec 15 '20 at 06:09
  • @AlwaysLearning Yes I have, in the catch block, because I read somewhere it doesn't work outside catch block – Mehek Dec 15 '20 at 06:09
  • I know my query is failing with a foreign key error and I want it printed in email. that's all I want. If and when it succeeds it should get me the output, total number of rows affected. – Mehek Dec 15 '20 at 06:15

0 Answers0