| Author |
 Topic  |
|
|
kond.mohan
Posting Yak Master
India
213 Posts |
 Posted - 07/20/2013 : 05:20:41
|
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
3873 Posts |
Posted - 07/20/2013 : 07:49:10
|
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. |
 |
|
| |
Topic |
|
|
|
Error Description have to include on Mail Alert