Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kond.mohan
Posting Yak Master
213 Posts |
 Posted - 2013-07-20 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-20 : 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. |
 |
|
|
|
|
|
|
|
Error Description have to include on Mail Alert