SQL Server

Compute a Global Unique Key for an Email

Demonstrates the Chilkat Email.ComputeGlobalKey2 method, which computes a global unique key for the email — an MD5 digest formed from the message-id concatenated with other identifying fields. The first argument is the encoding of the returned key (such as hex or base64), and the second folds the key when true. This example loads an email and computes its key as hex.

Background: A "global key" is a compact fingerprint that identifies a specific message across systems. Because it is derived deterministically from the message's own headers, the same email yields the same key anywhere it is computed — ideal for de-duplicating messages, using as a database primary key, or detecting whether a message has already been processed.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the ComputeGlobalKey2 method, which computes a global unique key for the
    --  email (an MD5 digest of the message-id and other fields).  The first argument is the encoding of the
    --  returned key (such as "hex" or "base64"), and the second folds the key when true.

    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/message.eml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    --  Compute the global key as a lowercase hex string.
    DECLARE @key nvarchar(4000)
    EXEC sp_OAMethod @email, 'ComputeGlobalKey2', @key OUT, 'hex', 0

    PRINT 'Global key: ' + @key

    --  Note: The path "qa_data/..." is a relative local filesystem path,
    --  relative to the current working directory of the running application.

    EXEC @hr = sp_OADestroy @email


END
GO