(SQL Server) Generate a Unique Key for an Email

Demonstrates how to generate a unique key for an email.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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 @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/someEmail.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- The ComputeGlobalKey method computes a global unique key for an email that may be used as a 
    -- key for a relational database table (or anything else). 
    -- The key is created by a digest-MD5 hash of the concatenation of the following header fields: 
    -- Message-ID, Subject, From, Date, To. (The header fields are Q/B decoded if necessary, 
    -- converted to the utf-8 encoding, concatenated, and hashed using MD5.) 
    -- The 16-byte MD5 hash is returned as an encoded string (such as base64, url, hex, etc.)
    -- If the bFold argument is true, then the 16-byte MD5 hash is folded to 8 bytes with an XOR to produce a shorter key.

    DECLARE @bFold int
    SELECT @bFold = 0
    DECLARE @key nvarchar(4000)
    EXEC sp_OAMethod @email, 'ComputeGlobalKey2', @key OUT, 'hex', @bFold

    PRINT 'key: ' + @key

    EXEC @hr = sp_OADestroy @email


END
GO