SQL Server

Get an Attachment as a Text String

Demonstrates the Chilkat Email.GetAttachmentString method, which returns the Nth attachment's data as text. The first argument is the zero-based attachment index and the second is the charset used to interpret the attachment bytes. This example reads a text attachment as utf-8.

Background: Attachments are stored as bytes, so turning one back into a string requires knowing its charset — the rule for mapping bytes to characters. Supplying the correct charset (often utf-8) yields readable text; the wrong one produces garbled characters. This method is meant for text attachments such as .txt, .csv, or .xml; binary attachments should be handled as raw data instead.

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
    --  Demonstrates the GetAttachmentString method, which returns the Nth attachment's data as
    --  text.  The first argument is the zero-based attachment index and the second is the charset used to interpret
    --  the attachment bytes.

    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_OASetProperty @email, 'Subject', 'Attachment as text'

    DECLARE @success int
    EXEC sp_OAMethod @email, 'AddStringAttachment', @success OUT, 'notes.txt', 'These are the notes stored in the attachment.'

    --  Get the first attachment (index 0) as text, interpreting the bytes as utf-8.
    DECLARE @content nvarchar(4000)
    EXEC sp_OAMethod @email, 'GetAttachmentString', @content OUT, 0, 'utf-8'

    PRINT 'Attachment 0 text: ' + @content

    EXEC @hr = sp_OADestroy @email


END
GO