January 18, 2012 at 12:26 pm
I'm working with msdb.dbo.sysmail_attachments
I'm trying to write a stored proc that will take failed messages with attachments, and resend them.
Since I don't know a means to reflag a message to be resent (changing the sent_status to 0 in sysmail_mailitems doesn't seem to do it), and we had a mail server problem that caused a bunch of messages queued up on the sql server to fail, I want to resend them.
Some of these messages have files attached. I was trying to export the files to disk via
SET @SQL = 'bcp "SELECT attachment from msdb.dbo.sysmail_attachments WHERE mailitem_id = ' + CAST(@mailitem_id AS VARCHAR) + '" queryout "'
+ CAST(@filename AS VARCHAR(260))
+ '" -T -n -S <servername>'
EXEC xp_cmdshell
@SQL
But while bcp puts out the files, they aren't exactly correct (for instance, rtf files open as text files with all the formatting commands being there to see). I realize if I had a format file, I could use that, but at present I have at least 30 different file types sitting in there, and I don't have format files for any of them.
Is there a simple way to have SQL export the varbinary(max) column as just a binary blob to disk that, if I were to double click it, would be readable by the application associated with it?
January 18, 2012 at 12:45 pm
What happens if you have a cursor step through the rows in sys_mail tables that you need, and call sp_send_dbmail using the values from that table?
I've done that before, but not with attachments.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2012 at 12:57 pm
GSquared (1/18/2012)
What happens if you have a cursor step through the rows in sys_mail tables that you need, and call sp_send_dbmail using the values from that table?I've done that before, but not with attachments.
Actually I had a stored proc that did that already
but the attachments included things like vacation sign-off forms and other bits and pieces....
I finally came up with this
CREATE PROCEDURE [dbo].[sysmail_resend_failed2] @mail_id INT = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql VARCHAR(8000)
DECLARE SYSMAIL_LOG_RESEND_CURSOR CURSOR READ_ONLY
FOR
SELECT DISTINCT
l.mailitem_id,
p.name,
m.recipients,
m.subject,
m.body_format,
m.body,
a.filename,
a.attachment
FROM
msdb.dbo.sysmail_log l
JOIN msdb.dbo.sysmail_mailitems m ON m.mailitem_id = l.mailitem_id
JOIN msdb.dbo.sysmail_profile p ON p.profile_id = m.profile_id
LEFT JOIN msdb.dbo.sysmail_attachments a ON m.mailitem_id = a.mailitem_id
WHERE
l.event_type = 3
AND m.sent_status = 2
AND (l.mailitem_id = @mail_id
OR @mail_id IS NULL)
ORDER BY
l.mailitem_id
OPEN SYSMAIL_LOG_RESEND_CURSOR
WHILE (1 = 1)
BEGIN
DECLARE
@mailitem_id INT,
@profile_name NVARCHAR(128),
@recipients VARCHAR(MAX),
@subject NVARCHAR(255),
@body_format VARCHAR(20),
@body NVARCHAR(MAX),
@filename NVARCHAR(260),
@attachment VARBINARY(MAX)
FETCH NEXT FROM SYSMAIL_LOG_RESEND_CURSOR INTO @mailitem_id,
@profile_name,@recipients,@subject,@body_format,@body,
@filename,@attachment
IF NOT @@FETCH_STATUS = 0
BEGIN
BREAK
END
PRINT CONVERT(VARCHAR,GETDATE(),121) + CHAR(9)
+ CONVERT(VARCHAR,@mailitem_id) + CHAR(9) + @recipients
IF @filename IS NOT NULL
BEGIN
SET @SQL = 'bcp "SELECT attachment from msdb.dbo.sysmail_attachments WHERE mailitem_id = ' + CAST(@mailitem_id AS VARCHAR) + '" queryout "'
+ CAST(@filename AS VARCHAR(260))
+ '" -T -S SQLSERVER -f C:\export.fmt'
EXEC xp_cmdshell
@SQL
IF DATALENGTH(@attachment) < 24000000
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name,
@recipients = @recipients,
@subject = @subject,
@body_format = @body_format,
@body = @body,
@file_attachments = @filename
ELSE
BEGIN
SET @body = 'This email had an attachment, but the size of the attachment exceeds the 25 MB file size limit. '
+ CHAR(10) + CHAR(13) + @body
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name,
@recipients = @recipients,
@subject = @subject,
@body_format = @body_format,
@body = @body
END
END
ELSE
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name,
@recipients = @recipients,
@subject = @subject,
@body_format = @body_format,
@body = @body
UPDATE
msdb.dbo.sysmail_mailitems
SET
sent_status = 1
WHERE
mailitem_id = @mailitem_id
END
CLOSE SYSMAIL_LOG_RESEND_CURSOR
DEALLOCATE SYSMAIL_LOG_RESEND_CURSOR
END
The Format file (which is a general purpose format file that seems to work for everything is
10.0
1
1 SQLBINARY 0 0 "" 1 attachment ""
January 18, 2012 at 12:59 pm
I'd like to point out that this is just a modification to the stored proc that somebody else put out there a while ago
http://www.codekeep.net/snippets/41a15075-29ea-4d7d-bb9f-6bd2eec45807.aspx
January 19, 2012 at 7:32 am
Take a look at the discussion here: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/29f30130-3a64-4b91-86b3-5fa09dbc4220
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 19, 2012 at 7:42 am
I had actually read that already, thanks!
No the SP I have plus the format file above actually works.... Up until then I had been trying to get by without a format file for BCP... once I gave in and made the format file... as above, it's a general purpose file that works to export any attachment of any file type.
Or at least it's worked correctly with
rtf
doc
xls
txt
and
zip
files
January 19, 2012 at 7:46 am
Cool biz.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply