(SQL Server) Convert a File from utf-8 to ANSI (such as Windows-1252)

This example is to satisfy a particular user's support question:

I have a file that contains this text:

	Original file text	Converted using notepad
Text	CAFÉ	CAFÉ
Hex	43 41 46 c3 89	43 41 46 c9

The utf-8 representation of the character É is the two bytes 0xC3 0x89. When Notepad is displaying the utf-8 file, it is intepreting the bytes as if they are ANSI (1 byte per char), and thus it is showing the ANSI char for 0xC3 (Ã) and the ANSI char for 0x89 (‰). After converting to ANSI, the É is represented by the single byte 0xC9.

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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @charset int
    -- Use "Chilkat_9_5_0.Charset" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Charset', @charset OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @charset, 'FromCharset', 'utf-8'
    EXEC sp_OASetProperty @charset, 'ToCharset', 'ANSI'

    -- We could alternatively be more specific and say "Windows-1252".
    -- The term "ANSI" means -- whatever character encoding is defined as the ANSI
    -- encoding for the computer.  In Poland, for example, it would be the single-byte-per-char
    -- used to represnt Eastern European language chars, which is Windows-1250.
    EXEC sp_OASetProperty @charset, 'ToCharset', 'Windows-1252'

    DECLARE @success int
    EXEC sp_OAMethod @charset, 'ConvertFile', @success OUT, 'qa_data/txt/cafeUtf8.txt', 'qa_output/cafeAnsi.txt'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @charset, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @charset
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @charset


END
GO