SQL Server
SQL Server
Create DSN (Delivery Status Notification) Email
Demonstrates how to create a DSN (Delivery Status Notification) Email having the format as defined in RFC 3464.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
DECLARE @email int
EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- A DSN email is a report about the attempt to delivery
-- an email. It is usually a report that indicates failure or
-- or a delay in delivery. The DSN is typically sent back to
-- the sender of an email.
-- IMPORTANT:
-- Note: Please be careful in sending automated DSN's.
-- It is important to be aware of an issue called "backscatter".
-- See the Wikipedia article here: http://en.wikipedia.org/wiki/Backscatter_%28e-mail%29
-- The ToDsn method will be called to create a new
-- DSN email based upon the email object calling ToDsn.
-- The email object instance used to call ToDsn would
-- typically be an email received from a POP3 or IMAP server.
-- In this case, to simplify the example, we'll load the email
-- from a .eml file.
-- This example will be creating a DSN for "someEmail.eml"
EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'someEmail.eml'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
RETURN
END
-- The DSN created by ToDsn will contain a
-- "message/delivery-status" MIME sub-part that will
-- be composed of a number of name-value pairs.
-- See RFC 3464 ( http://tools.ietf.org/html/rfc3464 )
-- The name-value content for this part is passed
-- as XML to ToDsn. This part of the example
-- prepares the XML:
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
-- For this example, we're just adding a few name-value pairs.
-- These should not be considered to be correct, required or
-- even sensible -- it's simply for the example.
EXEC sp_OASetProperty @xml, 'Tag', 'DeliveryStatusFields'
EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Status', '5.1.2 (bad destination system: no such domain)'
EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'Diagnostic-Code', 'smtp; Permanent failure: no such domain'
EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'X-BounceCategory', 'bad-domain'
DECLARE @xmlStr nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetXml', @xmlStr OUT
-- The last argument to be passed to ToDsn will be a boolean
-- indicating whether to include the entire MIME of the calling
-- email object in the DSN, or only the header.
-- It may make sense to include the entire email for small emails,
-- but for large emails (with large attachments) it's probably
-- best to make the DSN header-only.
DECLARE @bHeaderOnly int
SELECT @bHeaderOnly = 1
-- The first argument to be passed to ToDsn will
-- be a human-readable explanation that will be placed
-- into the 1st MIME part of the DSN (see RFC 3464).
DECLARE @humanReadableExplanation nvarchar(4000)
SELECT @humanReadableExplanation = 'Blah blah blah. The delivery of this email failed. Blah blah blah'
-- OK, we're ready to create DSN....
DECLARE @dsnEmail int
EXEC @hr = sp_OACreate 'Chilkat.Email', @dsnEmail OUT
EXEC sp_OAMethod @email, 'ToDsn', @success OUT, @humanReadableExplanation, @xmlStr, @bHeaderOnly, @dsnEmail
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dsnEmail
RETURN
END
-- Show the MIME of the source (original) email:
EXEC sp_OAMethod @email, 'GetMime', @sTmp0 OUT
PRINT @sTmp0
PRINT '**************************************'
-- Show the MIME of the DSN email:
EXEC sp_OAMethod @dsnEmail, 'GetMime', @sTmp0 OUT
PRINT @sTmp0
-- Sample output showing the MIME of the original email,
-- and the DSN email created based upon it:
-- X-Account-Key: account2
-- X-UIDL: 0D154945439D42BD80F180F6D9DE4D82
-- Received: with MailEnable Postoffice Connector;
-- Tue, 21 Jun 2011 09:21:23 -0400
-- Received: from qmta14.emeryville.ca.mail.comcast.net ([76.96.227.212]) by chilkatsoft.com with MailEnable ESMTP;
-- Tue, 21 Jun 2011 09:21:22 -0400
-- Received: from omta03.emeryville.ca.mail.comcast.net ([76.196.30.27]) by qmta14.emeryville.ca.mail.comcast.net with comcast id yd4k1g0050b6N64AEdLW9x;
-- Tue, 21 Jun 2011 13:20:30 +0000
-- Received: from [127.0.0.1] ([67.175.202.103]) by omta03.emeryville.ca.mail.comcast.net with comcast id ydLV1g00n2EMkZ78PdLWxi;
-- Tue, 21 Jun 2011 13:20:31 +0000
-- Message-ID: <4E009AA4.3090205@chilkatsoft.com>
-- Date: Tue, 21 Jun 2011 08:20:36 -0500
-- From: Chilkat Software <admin@chilkatsoft.com>
-- User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
-- MIME-Version: 1.0
-- To: Chilkat Support <support@chilkatsoft.com>
-- Subject: test 123
-- Content-Type: text/plain; charset=ISO-8859-1; format=flowed
-- Content-Transfer-Encoding: 7bit
-- Return-Path: <>
-- X-Antivirus: avast! (VPS 110621-0, 06/21/2011), Inbound message
-- X-Antivirus-Status: Clean
--
-- This is a test 123
--
--
--
-- **************************************
-- Content-Type: multipart/report; report-type=delivery-status;
-- boundary="------------070202040604030701060106"
--
-- --------------070202040604030701060106
-- Content-Type: text/plain
-- Content-Transfer-Encoding: 7bit
--
-- Blah blah blah. The delivery of this email failed. Blah blah blah
-- --------------070202040604030701060106
-- Content-Type: message/delivery-status
--
-- Status: 5.1.2 (bad destination system: no such domain)
-- Diagnostic-Code: smtp; Permanent failure: no such domain
-- X-BounceCategory: bad-domain
--
-- --------------070202040604030701060106
-- Content-Type: text/rfc822-headers
--
-- X-Account-Key: account2
-- X-UIDL: 0D154945439D42BD80F180F6D9DE4D82
-- Received: with MailEnable Postoffice Connector;
-- Tue, 21 Jun 2011 09:21:23 -0400
-- Received: from qmta14.emeryville.ca.mail.comcast.net ([76.96.227.212]) by chilkatsoft.com with MailEnable ESMTP;
-- Tue, 21 Jun 2011 09:21:22 -0400
-- Received: from omta03.emeryville.ca.mail.comcast.net ([76.196.30.27]) by qmta14.emeryville.ca.mail.comcast.net with comcast id yd4k1g0050b6N64AEdLW9x;
-- Tue, 21 Jun 2011 13:20:30 +0000
-- Received: from [127.0.0.1] ([67.175.202.103]) by omta03.emeryville.ca.mail.comcast.net with comcast id ydLV1g00n2EMkZ78PdLWxi;
-- Tue, 21 Jun 2011 13:20:31 +0000
-- Message-ID: <4E009AA4.3090205@chilkatsoft.com>
-- Date: Tue, 21 Jun 2011 08:20:36 -0500
-- From: Chilkat Software <admin@chilkatsoft.com>
-- User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
-- MIME-Version: 1.0
-- To: Chilkat Support <support@chilkatsoft.com>
-- Subject: test 123
-- Content-Type: text/plain; charset=ISO-8859-1; format=flowed
-- Content-Transfer-Encoding: 7bit
-- Return-Path: <>
-- X-Antivirus: avast! (VPS 110621-0, 06/21/2011), Inbound message
-- X-Antivirus-Status: Clean
-- --------------070202040604030701060106--
--
--
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dsnEmail
END
GO