locked
Send HTML as attachment in email in Stored Procedure RRS feed

  • Question

  • Hi Guys,
    I am writing a stored procedure to send HTML attachment. Below is my code but no html attachment sent in mail. Kindly advise.

    USE [CarsemERP]
    GO
    /****** Object:  StoredProcedure [dbo].[DBA_CarsemERP_SQLBlocks]    Script Date: 04/01/2015 15:14:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<THARMENDRAN>
    -- Create date: <2014FEB21>
    -- Description:	TO CHECK ANY LOCKS MORE THN 20 SEC IN SQL SERVER
    -- =============================================
    ALTER PROCEDURE [dbo].[DBA_CarsemERP_SQLBlocks]
    AS
    BEGIN
    DECLARE @iCnt As Int
    DECLARE @ICnt2 As Int
    DECLARE @SendEmail AS VARCHAR(3)
    
    DECLARE @Temp_DetailReq1 TABLE (
    			idx smallint Primary Key IDENTITY(1,1),
                            DBName varchar(30), RequestId Int, BlockingId Int, BlockedObjectName varchar(30),LockType varchar(30), RequestingText varchar(max), BlockingText varchar(max),
                            LoginName varchar (30), HostName varchar (30))
    
    DECLARE @Temp_DetailReq2 TABLE (
    			idx smallint Primary Key IDENTITY(1,1),
                            DBName varchar(30), RequestId Int, BlockingId Int, BlockedObjectName varchar(30),LockType varchar(30), RequestingText varchar(max), BlockingText varchar(max),
                            LoginName varchar (30), HostName varchar (30))
    
    SET @SendEmail = 'NO'
    SELECT @iCnt= COUNT(*) FROM DBA_SQLBlocksViewTharmen
    
              IF @iCnt > 0
    		    BEGIN
    			Insert Into @Temp_DetailReq1 
    			Select DBName, request_session_id, blocking_session_id, BlockedObjectName,resource_type, RequestingText, BlockingTest, LoginName, HostName From DBA_SQLBlocksViewTharmen
                 END   
    
           WAITFOR DELAY '00:00:20'
    
    select @iCnt2= COUNT(*) FROM DBA_SQLBlocksViewTharmen
    
              IF @iCnt2 > 0
    				BEGIN
    				DECLARE @columnHeaders NVARCHAR(MAX)
    				DECLARE @tableHTML NVARCHAR(MAX)
    				DECLARE @body NVARCHAR(MAX)
    					Insert Into @Temp_DetailReq2
    					                    
                                            Select DBName, request_session_id, blocking_session_id, BlockedObjectName,resource_type, RequestingText, BlockingTest, LoginName, HostName From DBA_SQLBlocksViewTharmen
                                            SET @SendEmail = 'YES'
                                            BEGIN
                                                SET @columnHeaders = 'DBName</th><th>RequestId</th><th>BlockingId</th><th>BlockedObjectName</th><th>LockType</th><th>RequestingText</th><th>BlockingText</th><th>LoginName</th><th>HostName'
                                                set @tableHTML =
    	                                    '<div><b>There is blocking in VERPSVR02-02.</b></div><br>' + -- This is the bold text at the top of your email
    	                                    '<table border="0" cellpadding="5"><font face="Calibri" size=2>' +
    	                                    '<tr><th>' + @columnHeaders + '</th></tr>' +
    	                                     convert(nvarchar(max),
    	                                    (
    		                                  select td = [DBName], '',			-- Here we put the column names
    				                                 td = [RequestId], '',
    				                                 td = [BlockingId], '',
                                                     td = [BlockedObjectName], '',
                                                     td = [LockType], '',
                                                     td = [RequestingText], '',
                                                     td = [BlockingText], '',
                                                     td = [LoginName], '',
                                                     td = [HostName], ''		-- Here we put the column names
    		                                  from @Temp_DetailReq2
                                              for xml path('tr'), type)) +'</font></table>'
    END
    END
                            IF @SendEmail = 'YES'
                            BEGIN
                            EXEC msdb.dbo.sp_send_dbmail
    						@profile_name	= 'MyDBMailProfileName',
    						@recipients	= 'itdba@xxx.com.my',
    						@body		= 'Please refer the attachment' ,
    						@body_format	= 'HTML',
    						@subject	= 'Alert! Blocking On ERPSVR02-02 Live Server',
    						@file_attachments = @tableHTML,
    						@importance     = 'High';
    						
    						END
    	END

    Saturday, April 4, 2015 1:15 PM

Answers

  • Hi Tharmendran,

    The error says attachment invialid which indicates there not exists such a file. Since this file is created with the cmdshell script, so there must be some mistake in the cmdshell script code.

    Based on my test, there are some flaws in your code and the modification as below can make your code work.

    DECLARE @cmd varchar(8000) -- the orginal declared length is not enough
    
    EXEC master..xp_cmdshell 'del d:\Block.html', no_output
    SET @cmd = 'echo "'+@tableHTML+'" >c:\Block.html' -- double quote the tableHTML as the > in the HTML body is not expected in the command
    EXEC master..xp_cmdshell @cmd, no_output

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Monday, April 6, 2015 7:21 AM
    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 2:15 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:20 AM
    Monday, April 6, 2015 7:19 AM
  •                         SET @cmd = '(echo '+@tableHTML+') >d:\Block.html'

    This is not going to work out. First of all @tableHTML must not include any line breaks, as the command-line shell is line-oriented. Next your HTML includes lots of characters that are special to the shell, <, > etc. You can try this by adding this command to your procedure:

    PRINT @cmd

    Then copy the output and try to run it from a command-line window.

    To follow this route, you would need to write a CLR stored procedure that writes the HTML contents to file, but that sounds just crazy. I think it is a better idea to back to the drawing board to figure out the best way for what you want to achieve. I suspect that a better solution is write a C# program or a PowerShell script and not write a stored procedure at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 2:15 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:20 AM
    Monday, April 6, 2015 8:36 AM
  • I think this is pretty much achievable within this code with a few modifications. As far as i understand the only requirement is to send the variable @tableHTML in an attached .html file. First, this is a vague requirement because you can pretty much define this variable as @body and just send the html as an email. However, in order to accomplish what you are trying to do which is not going to be easy but let me try to explain the steps involved:-

    1. Declare a permanent table with a varchar(max) single column.

    2. Then insert the variable @tableHTML into it.

    3. Then declare a @cmd nvarchar(8000) variable for outputting the html file.

    4. Use bcp to select from the new table declared in step 1 and output to the location d:\test.html

    5. Drop table created in step 1.

    6. Use the sp_send_dbmail cause now you have a permanent location.

    Something like below:-

    DECLARE @tableHTML nvarchar(max)
    DECLARE @cmd varchar(8000)
    SET @tableHTML = '<div><b>There is blocking in VERPSVR02-02.</b></div><br>' + -- This is the bold text at the top of your email
    	                                    '<table border="0" cellpadding="5"><font face="Calibri" size=2>' +
    	                                '<tr><th>'
    
    CREATE TABLE test
    (test varchar(max))
    
    INSERT INTO test
    VALUES (@tableHTML)
    
    select @cmd='bcp "select * from test" ' +'queryout "'
    							+'E:\test.html'
    							+'"'
    							+' -S'+@@servername
    							+' -T -c'
    
    --PRINT @cmd
    exec master.dbo.xp_cmdshell @cmd, no_output
    
    DROP TABLE test

    There will be a little work involved but it will get you there. You will also need to delete the html file when done or perhaps choose a naming convention by adding a counter value.


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 2:15 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:20 AM
    Monday, April 6, 2015 10:49 PM

All replies

  • Hi,

    [ @file_attachments= ] 'file_attachments'
    Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

    hope this will help you

    https://msdn.microsoft.com/en-US/en-en/library/ms190307.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Saturday, April 4, 2015 4:20 PM
  • Hi paSQuale, I have increase the limits to 5MB. The HTML size is less than 1 MB. SO how to save the generated HTML into a path and send it as attachment to email?
    Sunday, April 5, 2015 2:41 AM
  • Hi Guys,

    I need help on how to generated HTML as attachment to email. I have write below stored procedure but the html did not send as attachment. Kindly guide me on how to save the generated HTML as a file and send it as attachment.

    USE [CarsemERP]
    GO
    /****** Object:  StoredProcedure [dbo].[DBA_CarsemERP_SQLBlocks]    Script Date: 04/01/2015 15:14:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<THARMENDRAN>
    -- Create date: <2014FEB21>
    -- Description:	TO CHECK ANY LOCKS MORE THN 20 SEC IN SQL SERVER
    -- =============================================
    ALTER PROCEDURE [dbo].[DBA_CarsemERP_SQLBlocks]
    AS
    BEGIN
    DECLARE @iCnt As Int
    DECLARE @ICnt2 As Int
    DECLARE @SendEmail AS VARCHAR(3)
    
    DECLARE @Temp_DetailReq1 TABLE (
    			idx smallint Primary Key IDENTITY(1,1),
                            DBName varchar(30), RequestId Int, BlockingId Int, BlockedObjectName varchar(30),LockType varchar(30), RequestingText varchar(max), BlockingText varchar(max),
                            LoginName varchar (30), HostName varchar (30))
    
    DECLARE @Temp_DetailReq2 TABLE (
    			idx smallint Primary Key IDENTITY(1,1),
                            DBName varchar(30), RequestId Int, BlockingId Int, BlockedObjectName varchar(30),LockType varchar(30), RequestingText varchar(max), BlockingText varchar(max),
                            LoginName varchar (30), HostName varchar (30))
    
    SET @SendEmail = 'NO'
    SELECT @iCnt= COUNT(*) FROM DBA_SQLBlocksViewTharmen
    
              IF @iCnt > 0
    		    BEGIN
    			Insert Into @Temp_DetailReq1 
    			Select DBName, request_session_id, blocking_session_id, BlockedObjectName,resource_type, RequestingText, BlockingTest, LoginName, HostName From DBA_SQLBlocksViewTharmen
                 END   
    
           WAITFOR DELAY '00:00:20'
    
    select @iCnt2= COUNT(*) FROM DBA_SQLBlocksViewTharmen
    
              IF @iCnt2 > 0
    				BEGIN
    				DECLARE @columnHeaders NVARCHAR(MAX)
    				DECLARE @tableHTML NVARCHAR(MAX)
    				DECLARE @body NVARCHAR(MAX)
    					Insert Into @Temp_DetailReq2
    					                    
                                            Select DBName, request_session_id, blocking_session_id, BlockedObjectName,resource_type, RequestingText, BlockingTest, LoginName, HostName From DBA_SQLBlocksViewTharmen
                                            SET @SendEmail = 'YES'
                                            BEGIN
                                                SET @columnHeaders = 'DBName</th><th>RequestId</th><th>BlockingId</th><th>BlockedObjectName</th><th>LockType</th><th>RequestingText</th><th>BlockingText</th><th>LoginName</th><th>HostName'
                                                set @tableHTML =
    	                                    '<div><b>There is blocking in VERPSVR02-02.</b></div><br>' + -- This is the bold text at the top of your email
    	                                    '<table border="0" cellpadding="5"><font face="Calibri" size=2>' +
    	                                    '<tr><th>' + @columnHeaders + '</th></tr>' +
    	                                     convert(nvarchar(max),
    	                                    (
    		                                  select td = [DBName], '',			-- Here we put the column names
    				                                 td = [RequestId], '',
    				                                 td = [BlockingId], '',
                                                     td = [BlockedObjectName], '',
                                                     td = [LockType], '',
                                                     td = [RequestingText], '',
                                                     td = [BlockingText], '',
                                                     td = [LoginName], '',
                                                     td = [HostName], ''		-- Here we put the column names
    		                                  from @Temp_DetailReq2
                                              for xml path('tr'), type)) +'</font></table>'
    END
    END
                            IF @SendEmail = 'YES'
                            BEGIN
                            EXEC msdb.dbo.sp_send_dbmail
    						@profile_name	= 'MyDBMailProfileName',
    						@recipients	= 'itdba@xxx.com.my',
    						@body		= 'Please refer the attachment' ,
    						@body_format	= 'HTML',
    						@subject	= 'Alert! Blocking On ERPSVR02-02 Live Server',
    						@file_attachments = @tableHTML,
    						@importance     = 'High';
    						
    						END
    	END

    • Merged by Eric__Zhang Monday, April 6, 2015 7:57 AM same question from one OP
    Sunday, April 5, 2015 4:01 PM
  • Good day Tharmendran Sukumaran,

    Please don't post duplicate threads!
    * When you response to your thread it will jump to the first place.
    Thanks

    This quetion was asked here:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/93a4911d-9af0-430a-b438-46ae073c3055/send-html-as-attachment-in-email-in-stored-procedure?forum=transactsql


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]



    • Edited by pituachMVP Sunday, April 5, 2015 4:08 PM
    Sunday, April 5, 2015 4:06 PM
  • Did you notice this sentence in paSQuaLe's response?

    "Files in the list must be specified as absolute paths."

    * You ask about attaching a file but you re trying to attach HTML code. If you want to attach the HTML as attached file you need to create the file and attach it (or use filestream to create in in memory).

    * if you want to add the HTML code to the content of the email that this is a different question.

    to sent HTML email you do not attach it but just use:
    @body_format = 'HTML'
    and then in the @body you just insert the HTML code


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    • Edited by pituachMVP Sunday, April 5, 2015 4:23 PM
    Sunday, April 5, 2015 4:20 PM
  • Hi Ronen,

    Yes I noticed PasQuaLe response.

    I have try to change the sending email code as below but receive the attachment invalid error.

    Need your advise Ronen

    DECLARE @cmd varchar(256)
                            EXEC master..xp_cmdshell 'del d:\Block.html', no_output
                            SET @cmd = '(echo '+@tableHTML+') >d:\Block.html'
                            EXEC master..xp_cmdshell @cmd, no_output
                            BEGIN
                            EXEC msdb.dbo.sp_send_dbmail
    						@profile_name	= 'MyDBMailProfileName',
    						@recipients	= 'itdba@carsem.com.my',
    						@body		= 'Please refer the attachment' ,
    						@body_format	= 'HTML',
    						@subject	= 'Alert! Blocking On ERPSVR02-02 Live Server',
    						@file_attachments = 'd:\Block.html',
    						@importance     = 'High';

    Monday, April 6, 2015 2:20 AM
  • Hi,

    let's work in 3 steps to make sure you can do it.

    1. please check the the file created correctly in your current script. My first guess is that this is your current issue. if you get any error (check the SQL Server error log as well) then post it for us to review. A common issue might be the path that you are using and the privileges to write/read files there, for example. 

    2. please try to go over this tutorial and send a simple email wit a simple file attached to it (use the same file in the same path that you created in step 1)
    http://blogs.msdn.com/b/sqlagent/archive/2010/11/10/sql-database-mail-send-emails-with-attachment.aspx

    3. combine all together ONLY if and when previous steps completed successfully.

    * informa us which step is not working and why or if this tutorial an working by steps was success and you got the solution.

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Monday, April 6, 2015 5:09 AM
  • Hi Tharmendran,

    The error says attachment invialid which indicates there not exists such a file. Since this file is created with the cmdshell script, so there must be some mistake in the cmdshell script code.

    Based on my test, there are some flaws in your code and the modification as below can make your code work.

    DECLARE @cmd varchar(8000) -- the orginal declared length is not enough
    
    EXEC master..xp_cmdshell 'del d:\Block.html', no_output
    SET @cmd = 'echo "'+@tableHTML+'" >c:\Block.html' -- double quote the tableHTML as the > in the HTML body is not expected in the command
    EXEC master..xp_cmdshell @cmd, no_output

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Monday, April 6, 2015 7:21 AM
    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 2:15 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:20 AM
    Monday, April 6, 2015 7:19 AM
  •                         SET @cmd = '(echo '+@tableHTML+') >d:\Block.html'

    This is not going to work out. First of all @tableHTML must not include any line breaks, as the command-line shell is line-oriented. Next your HTML includes lots of characters that are special to the shell, <, > etc. You can try this by adding this command to your procedure:

    PRINT @cmd

    Then copy the output and try to run it from a command-line window.

    To follow this route, you would need to write a CLR stored procedure that writes the HTML contents to file, but that sounds just crazy. I think it is a better idea to back to the drawing board to figure out the best way for what you want to achieve. I suspect that a better solution is write a C# program or a PowerShell script and not write a stored procedure at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 2:15 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:20 AM
    Monday, April 6, 2015 8:36 AM
  • I think this is pretty much achievable within this code with a few modifications. As far as i understand the only requirement is to send the variable @tableHTML in an attached .html file. First, this is a vague requirement because you can pretty much define this variable as @body and just send the html as an email. However, in order to accomplish what you are trying to do which is not going to be easy but let me try to explain the steps involved:-

    1. Declare a permanent table with a varchar(max) single column.

    2. Then insert the variable @tableHTML into it.

    3. Then declare a @cmd nvarchar(8000) variable for outputting the html file.

    4. Use bcp to select from the new table declared in step 1 and output to the location d:\test.html

    5. Drop table created in step 1.

    6. Use the sp_send_dbmail cause now you have a permanent location.

    Something like below:-

    DECLARE @tableHTML nvarchar(max)
    DECLARE @cmd varchar(8000)
    SET @tableHTML = '<div><b>There is blocking in VERPSVR02-02.</b></div><br>' + -- This is the bold text at the top of your email
    	                                    '<table border="0" cellpadding="5"><font face="Calibri" size=2>' +
    	                                '<tr><th>'
    
    CREATE TABLE test
    (test varchar(max))
    
    INSERT INTO test
    VALUES (@tableHTML)
    
    select @cmd='bcp "select * from test" ' +'queryout "'
    							+'E:\test.html'
    							+'"'
    							+' -S'+@@servername
    							+' -T -c'
    
    --PRINT @cmd
    exec master.dbo.xp_cmdshell @cmd, no_output
    
    DROP TABLE test

    There will be a little work involved but it will get you there. You will also need to delete the html file when done or perhaps choose a naming convention by adding a counter value.


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 2:15 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:20 AM
    Monday, April 6, 2015 10:49 PM