SQL Server Requires Chilkat v11.0.0+
SQL Server
Transition from Email.GetDsnFinalRecipients to Email.GetDsnInfo
Provides instructions for replacing deprecated GetDsnFinalRecipients method calls with GetDsnInfo.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 @iTmp0 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
-- Load a multipart/report DSN email.
EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/dsn_example.eml'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
RETURN
END
-- ------------------------------------------------------------------------
-- The GetDsnFinalRecipients method is deprecated:
DECLARE @sa int
EXEC sp_OAMethod @email, 'GetDsnFinalRecipients', @sa OUT
EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
RETURN
END
DECLARE @i int
SELECT @i = 0
DECLARE @count int
EXEC sp_OAGetProperty @sa, 'Count', @count OUT
WHILE @i < @count
BEGIN
EXEC sp_OAMethod @sa, 'GetString', @sTmp0 OUT, @i
PRINT @i + ': ' + @sTmp0
SELECT @i = @i + 1
END
PRINT '----'
EXEC @hr = sp_OADestroy @sa
-- ------------------------------------------------------------------------
-- Do the equivalent using GetDsnInfo.
-- Your application creates a new, empty JsonObject object which is passed
-- in the last argument and filled upon success.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @email, 'GetDsnInfo', @success OUT, @json
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT '----'
-- The JSON contains the following:
-- {
-- "reporting-mta": "dns; mail.example.com",
-- "received-from-mta": "dns; smtp.example.com",
-- "arrival-date": "Fri, 8 May 2025 14:23:42 -0400",
-- "final-recipient": [
-- "rfc822; alice@example.com",
-- "rfc822; bob@example.com",
-- "rfc822; carol@example.com"
-- ],
-- "action": "failed",
-- "status": "5.1.1",
-- "remote-mta": "dns; smtp.recipientdomain.com",
-- "diagnostic-code": "smtp; 550 5.1.1 User unknown",
-- "last-attempt-date": "Fri, 8 May 2025 14:23:42 -0400",
-- "action": "failed",
-- "status": "5.1.2",
-- "remote-mta": "dns; smtp.recipientdomain.com",
-- "diagnostic-code": "smtp; 550 5.1.2 Host unknown (Domain name not found)",
-- "last-attempt-date": "Fri, 8 May 2025 14:23:42 -0400",
-- "action": "failed",
-- "status": "5.7.1",
-- "remote-mta": "dns; smtp.recipientdomain.com",
-- "diagnostic-code": "smtp; 550 5.7.1 Relay access denied",
-- "last-attempt-date": "Fri, 8 May 2025 14:23:42 -0400"
-- }
-- Iterate over the final-recipients
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count OUT, 'final-recipient'
WHILE @i < @count
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
DECLARE @recipient nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @recipient OUT, 'final-recipient[i]'
PRINT @i + ': ' + @recipient
SELECT @i = @i + 1
END
-- -------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------
-- This is the contents of the dsn_example.eml used for this example:
-- From: postmaster@example.com
-- To: sender@example.com
-- Subject: Delivery Status Notification (Failure)
-- MIME-Version: 1.0
-- Content-Type: multipart/report; report-type=delivery-status;
-- boundary="boundary-string"
--
-- --boundary-string
-- Content-Type: text/plain; charset=us-ascii
--
-- This is an automatically generated Delivery Status Notification.
--
-- Delivery to the following recipients failed permanently:
--
-- alice@example.com
-- bob@example.com
-- carol@example.com
--
-- Technical details of permanent failure:
-- DNS Error: Domain name not found
--
-- --boundary-string
-- Content-Type: message/delivery-status
--
-- Reporting-MTA: dns; mail.example.com
-- Received-From-MTA: dns; smtp.example.com
-- Arrival-Date: Fri, 8 May 2025 14:23:42 -0400
--
-- Final-Recipient: rfc822; alice@example.com
-- Action: failed
-- Status: 5.1.1
-- Remote-MTA: dns; smtp.recipientdomain.com
-- Diagnostic-Code: smtp; 550 5.1.1 User unknown
-- Last-Attempt-Date: Fri, 8 May 2025 14:23:42 -0400
--
-- Final-Recipient: rfc822; bob@example.com
-- Action: failed
-- Status: 5.1.2
-- Remote-MTA: dns; smtp.recipientdomain.com
-- Diagnostic-Code: smtp; 550 5.1.2 Host unknown (Domain name not found)
-- Last-Attempt-Date: Fri, 8 May 2025 14:23:42 -0400
--
-- Final-Recipient: rfc822; carol@example.com
-- Action: failed
-- Status: 5.7.1
-- Remote-MTA: dns; smtp.recipientdomain.com
-- Diagnostic-Code: smtp; 550 5.7.1 Relay access denied
-- Last-Attempt-Date: Fri, 8 May 2025 14:23:42 -0400
--
-- --boundary-string
-- Content-Type: message/rfc822
--
-- Return-Path: <sender@example.com>
-- Received: from smtp.example.com (smtp.example.com [192.168.1.10])
-- by mail.example.com (Postfix) with ESMTP id 123456789
-- for <alice@example.com>, <bob@example.com>, <carol@example.com>;
-- Fri, 8 May 2025 14:23:42 -0400
-- Message-ID: <001a113d789f$ad34bc30$038e34b0$@example.com>
-- From: Sender Name <sender@example.com>
-- To: alice@example.com, bob@example.com, carol@example.com
-- Subject: Test Message
-- Date: Fri, 8 May 2025 14:20:00 -0400
-- Content-Type: text/plain; charset="us-ascii"
--
-- This is a test email message sent to multiple recipients.
-- --boundary-string--
--
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @json
END
GO