Feeds:
Posts
Comments

Posts Tagged ‘sp_send_dbmail’

This is a very common issue, when you work on long running queries or procedures, and the PROBLEM is that you need to check the stored procedure from time to time whether it has been executed successfully or failed or it is still running. Sometimes you are busy with some other stuff and you forget to check the status. I came across this scenario many times specially, when I am working on parallel projects and each of them taking time specially if it is a migration and the database size is too big.

Let me demonstrate step by step, how I resolved the issue and made my life easier.

Step 1 :
First of all you need to configure the database mail in each SQL Server using this article and make sure that is working.

Step 2 :
You need to alter your stored procedure and add few additional scripts. These additional lines of script are basically the TRY…CATCH block and sp_send_dbmail to send Email in case of success or failure. Whenever, the procedure will come across any error or if it is executed successfully, it will generate an email and notify the admin. In addition you can format the email and add any additional information that can help you to trace the error.

Given below is the sample script in which I created a table and inserted records using stored procedure and generated an email whether it has been executed successfully or failed.
This script may take few minutes to execute.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
DROP TABLE tbl_sample
GO
--Create Sample table
CREATE TABLE tbl_sample
(
 [ID] INT IDENTITY(1,1),
 [Name] varchar(10)
)
GO

--DROP PROCEDURE usp_test
--GO
--Create sample stored procedure
CREATE PROCEDURE usp_test
AS
BEGIN TRANSACTION;
BEGIN TRY
	DECLARE @int INT
	SET @int = 1
	WHILE (@int <=400000)
	BEGIN
	    INSERT INTO tbl_sample VALUES ('Test')
		SET @int = @int + 1
	END
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
	 BEGIN
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SET @ErrorMessage = 'Dear Admin,<BR><BR>Due to some error
        , your procedure ''usp_test'' at Server ' + @@Servername +
        ' has been rollback.<BR>'
        + 'Given below is the error message.
        <BR><BR> ErrorMessage : ' +  ERROR_MESSAGE()

        ROLLBACK TRANSACTION;
		EXEC msdb.dbo.sp_send_dbmail @profile_name='My Profile',
		@recipients='admin@raresql.com',
		@subject='Procedure Failed & Rollback',
        @body= @ErrorMessage,
        @body_format = 'HTML',
		@execute_query_database = 'tempdb'
     END
END CATCH;

IF @@TRANCOUNT > 0
  BEGIN
        COMMIT TRANSACTION;
	    EXEC msdb.dbo.sp_send_dbmail @profile_name='My Profile',
		@recipients='admin@raresql.com',
		@subject='Procedure Executed Successfully',
		@execute_query_database = 'tempdb'
  END
GO

--Execute the Stored Procedure
EXEC usp_test
GO

I found it very handy, let me know if you come across this issue and its resolution.

Advertisements

Read Full Post »

I have been using Database email for a long time. This is very nice features introduced in SQL Server 2005. But when it comes to HTML formatting, you need to know a little bit HTML to format it and it takes a quite a while to format it. In this article, I will share a tip that can make your life easy and you can format even a complex HTML formatted email in few minutes.

Lets process it step by step.

Step 1 :
Mostly we receive the email format in word documents. First of all, you need to open the word document in MS word.
Sample attached Test Document 1.1.
SQL email1.1

Step 2 :
Then Go to File menu>>Save as and save it as html document.
SQL email1.2

Step 3 :
Open the saved html document, right click on it and go to view source.
SQL email1.3

Step 4 :
It will open a new window. Now you need to search for the tags namely Body and /Body and whatever text lies between both tags, just copy and paste it in the SQL query window.
SQL email1.4

Step 5 :
Now, you need to replace one single quote (‘) to two single quotes (”) in SQL query window and add single quote (‘) in the beginning and end of the text. Rest of the format will remain same.

Use msdb
GO
EXEC sp_send_dbmail @profile_name='My profile',
@recipients='mimran18@gmail.com',
@subject='Test Message with Formatted HTML',
@body_format = 'HTML' ,
@body='&lt;/pre&gt;
&lt;div class=&quot;WordSection1&quot;&gt;&lt;s&gt;This is a TEST email&lt;/s&gt;

&lt;i&gt;&lt;span style=&quot;text-decoration: underline;&quot;&gt;This
is a TEST email&lt;/span&gt;&lt;/i&gt;

This is a TEST email

This is a TEST
&lt;table class=&quot;MsoTableLightListAccent1&quot; border=&quot;1&quot; cellspacing=&quot;0&quot; cellpadding=&quot;0&quot;&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td valign=&quot;top&quot; width=&quot;97&quot;&gt;&lt;span class=&quot;SpellE&quot;&gt;&lt;b&gt;S.No&lt;/b&gt;&lt;/span&gt;&lt;b&gt;&lt;/b&gt;&lt;/td&gt;
&lt;td valign=&quot;top&quot; width=&quot;375&quot;&gt;&lt;b&gt;SQL Server Versions&lt;/b&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td valign=&quot;top&quot; width=&quot;97&quot;&gt;&lt;b&gt;1&lt;/b&gt;&lt;/td&gt;
&lt;td valign=&quot;top&quot; width=&quot;375&quot;&gt;SQL Server 2005&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td valign=&quot;top&quot; width=&quot;97&quot;&gt;&lt;b&gt;2&lt;/b&gt;&lt;/td&gt;
&lt;td valign=&quot;top&quot; width=&quot;375&quot;&gt;SQL Server 2008&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td valign=&quot;top&quot; width=&quot;97&quot;&gt;&lt;b&gt;3&lt;/b&gt;&lt;/td&gt;
&lt;td valign=&quot;top&quot; width=&quot;375&quot;&gt;SQL Server 2012&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;
&lt;/div&gt;
&lt;pre&gt;
'

Step 6 :
Now, just execute it and it will send a nice formatted HTML email.

Read Full Post »