SQL Server

Example: Email.GetDsnInfo method

See more Email Object Examples

Demonstrates how to call the GetDsnInfo method. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    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

    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/sample_multipart_report.eml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    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

    -- Sample output:

    -- {
    --   "reporting-mta": "dns; Exchange2016.example.com",
    --   "final-recipient": [
    --     "herb.butterworth1247692846@gmail.com"
    --   ],
    --   "action": "failed",
    --   "status": "5.1.1",
    --   "remote-mta": "dns; mx.google.com",
    --   "x-supplementary-info": "<mx.google.com #5.1.1 smtp;550-5.1.1 The email account that you tried to reach does not exist. Please try 550-5.1.1 double-checking the recipient's email address for typos or 550-5.1.1 unnecessary spaces. Learn more at 550 5.1.1  https://support.google.com/mail/?p=NoSuchUser o8-20020a056870968800b001b55816bea9si2188132oaq.70 - gsmtp>",
    --   "x-display-name": "herb.butterworth1247692846@gmail.com"
    -- }

    -- Code for parsing the JSON:

    DECLARE @strVal nvarchar(4000)

    DECLARE @reporting_mta nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @reporting_mta OUT, 'reporting-mta'
    DECLARE @action nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @action OUT, 'action'
    DECLARE @status nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @status OUT, 'status'
    DECLARE @remote_mta nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @remote_mta OUT, 'remote-mta'
    DECLARE @x_supplementary_info nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @x_supplementary_info OUT, 'x-supplementary-info'
    DECLARE @x_display_name nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @x_display_name OUT, 'x-display-name'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count int
    EXEC sp_OAMethod @json, 'SizeOfArray', @count OUT, 'final-recipient'
    WHILE @i < @count
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @strVal OUT, 'final-recipient[i]'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @json


END
GO