sql server email

Hi all,

I have a question regarding the stored procedure results. I have two stored procedures. stored procedure  [spSync_Prod2Dev] and stored [dbo].[SpEmail_spSync_Prod2Dev]. Here stored procedure  [spSync_Prod2Dev] will load data from Production to DEV, this will be done every week. So ones it runs I should get an email "data loaded successfull" For this I have created another stored procedure called stored [dbo].[SpEmail_spSync_Prod2Dev], So ones stored procedure runs

exec [dbo].[SpEmail_spSync_Prod2Dev]  this stored proc this willgenerate a result values either it's 0 -1 or 1 based on this output it should call this stored procedure [spSync_Prod2Dev]. If the [SpEmail_spSync_Prod2Dev] result is 0 then it should email me an "Error message" and if the [SpEmail_spSync_Prod2Dev] result is success (1) it should go the this  [spSync_Prod2Dev]. stored proc and it should execute the SP ,and get an success email "Data loaded". Could you please help me on this. And I think I am clear if not you can ask me for questions.

Thanks,

sidhu

March 27th, 2015 3:22pm

I have a bad feeling about your design. if you need to continue this way, Create a main stored Proc and within that call two procedure [dbo].[SpEmail_spSync_Prod2Dev] and  [spSync_Prod2Dev]. as per your plan, evaluate the results of the SPROC [dbo].[SpEmail_spSync_Prod2Dev] and store it in an integer variable and then based on the value (0,1,-1) call the SPROC  [spSync_Prod2Dev] otherwise raise an error or send an email.
Free Windows Admin Tool Kit Click here and download it now
March 27th, 2015 4:07pm

Hi Siddu_123,

I am not quite getting the point why the subsequent executions should depend on the result of [SpEmail_spSync_Prod2Dev], as it is used to send an email per the description in the first paragraph. Anyway , according to the logic description in your post, you may reference the pseudo code below.

 DECLARE INT @flag

 EXEC [SpEmail_spSync_Prod2Dev] @flag OUTPUT --there should be a output parameter

 IF @flag =  0
	EXEC [SpEmail_spSync_Prod2Dev] @msg='Error message'
 ELSE IF @flag = 1
        BEGIN
	    EXEC [spSync_Prod2Dev]
	    EXEC [SpEmail_spSync_Prod2Dev] @msg='Data loaded'
        END
      ELSE  -- the case where @flag=-1
		BEGIN
		..
		END

If you have any question, feel free to let me know.
March 31st, 2015 1:47am

Why not use a Try Catch for this???

USE [YOUR_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Table_Name]

AS

BEGIN TRY

INSERT [dbo].[Table_Production] 
Select *
FROM [dbo].[Table_Staging]

SET NOCOUNT ON
declare @RowCount as int 
declare @EmailBody as varchar(1000)
SELECT @RowCount = count(*) FROM [dbo].[Table_Staging] 
set @EmailBody = 'Successfully Loaded TABLE' + cast(@RowCount as varchar(50)) + ' records loaded'

IF @RowCount > 0
BEGIN
	exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', 
	 @recipients = 'your_name@firm.com',
	 @subject = 'Successfully Production', 
	 @body = @EmailBody,
	 @body_format = 'HTML'
END

END TRY

BEGIN CATCH
	-- SELECT ERROR_NUMBER() AS ErrorNumber;
	exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', 
	 @recipients = 'your_name@firm.com', 
	 @subject = 'WARNING did NOT load Production', 
	 @body = 'WARNING did NOT load Production + @@RowCount ', 
	 @body_format = 'HTML'

END CATCH;

Please see these links for more info.

http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/02/sending-mail-using-sql-server-express-edition/

http://www.dotnet-tricks.com/Tutorial/sqlserver/4761260812-Remove-unsent-database-email-from-SQL-Server.html

http://www.mytechmantra.com/LearnSQLServer/Troubleshooting-SQL-Server-blocked-access-to-procedure-sp_send_dbmail.html

Free Windows Admin Tool Kit Click here and download it now
April 6th, 2015 11:33am

Well, since this is going to be done every week then this will be a part of a weekly job. Now weekly job should have three steps:-

Step 1. To run the stored proc [spSync_Prod2Dev]. If it succeeds go to Step 2. If it fails, go to Step 3.

Step 2. To send an email for success and quit the job reporting success. This can be done via exec sp_send_dbmail.

Step 3. To send a failure email and quit the job reporting failure.

April 6th, 2015 5:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics