SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error Description have to include on Mail Alert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
189 Posts

Posted - 07/20/2013 :  05:20:41  Show Profile  Reply with Quote
hi
we have created stored procedure in Sql server.
ALTER PROC [dbo].[ETL_START]
AS BEGIN DECLARE @EmailID VARCHAR(MAX)
SET @EmailID ='xxxx.x@abc.com'
SELECT @EmailID=@EmailID+';'+REPLACE(EmailID,'xxx') FROM UserMaster where USERNAME not In ('bbbb')
--SELECT REPLACE(EmailID,'','') as EmailID FROM UserMaster where EmailID = ''
SELECT @EmailID
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'Database Administrator',
@blind_copy_recipients = @EmailID,
@body = 'Dear All,
eports ETL Processing started for the day, we will confirm you once processing is completed
------------using above mail we are getting the alert mail(defined in the body).In Case of Failure we are able to getting the Failed alert mail.we have to get alert MAIL with FAILURE Message(Cause of Failure).is there way to solve it pls explain

James K
Flowing Fount of Yak Knowledge

3555 Posts

Posted - 07/20/2013 :  07:49:10  Show Profile  Reply with Quote
quote:
Originally posted by kond.mohan

hi
we have created stored procedure in Sql server.
ALTER PROC [dbo].[ETL_START]
AS BEGIN DECLARE @EmailID VARCHAR(MAX)
SET @EmailID ='xxxx.x@abc.com'
SELECT @EmailID=@EmailID+';'+REPLACE(EmailID,'xxx') FROM UserMaster where USERNAME not In ('bbbb')
--SELECT REPLACE(EmailID,'','') as EmailID FROM UserMaster where EmailID = ''
SELECT @EmailID
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'Database Administrator',
@blind_copy_recipients = @EmailID,
@body = 'Dear All,
eports ETL Processing started for the day, we will confirm you once processing is completed
------------using above mail we are getting the alert mail(defined in the body).In Case of Failure we are able to getting the Failed alert mail.we have to get alert MAIL with FAILURE Message(Cause of Failure).is there way to solve it pls explain

The stored procedure you posted does not include the portions of your code where you are detecting whether the ETL process succeeded or failed. You have to open up that part of the code and depending on success or failure, you have to change the message text appropriately.

You could add a parameter to the stored procedure that indicates failure or success and then compose the text of the message depending on the value of that parameter.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000