Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Saturday, April 4, 2015 1:15 PM
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
Monday, April 6, 2015 7:19 AM ✅Answered
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
Monday, April 6, 2015 8:36 AM ✅Answered
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
Monday, April 6, 2015 10:49 PM ✅Answered
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
Saturday, April 4, 2015 4:20 PM
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
Sunday, April 5, 2015 2:41 AM
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 4:01 PM
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
Sunday, April 5, 2015 4:06 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
| Ronen Ariely [Personal Site] [Blog] [Facebook] |
Sunday, April 5, 2015 4:20 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
| Ronen Ariely [Personal Site] [Blog] [Facebook] |
Monday, April 6, 2015 2:20 AM
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 5:09 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.
| Ronen Ariely [Personal Site] [Blog] [Facebook] |