Using the Chilkat ActiveX in SQL Server

call Chilkat from T-SQL stored procedures with sp_OACreate, sp_OAMethod, and sp_OAGetProperty

SQL Server on Windows · OLE Automation procedures · Chilkat ActiveX v11

SQL Server's OLE Automation procedures let T-SQL create COM objects and call their methods and properties. With the Chilkat ActiveX installed on the database server, a stored procedure can call a REST API, send email, transfer files with SFTP, sign or encrypt data, parse JSON and XML, and use the rest of the Chilkat API. Chilkat runs inside the SQL Server process (sqlservr.exe), on the server itself.

On this page: Install Enable OLE Automation First procedure JSON as rows String & binary limits T-SQL tips Security Troubleshooting

Setup

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

Examples

Your first stored procedure: unlock Chilkat and fetch a URL

Every sp_OA call follows the same pattern: sp_OACreate returns an object token, sp_OAMethod and sp_OAGetProperty use it, and sp_OADestroy releases it. Chilkat must be unlocked with UnlockBundle; unlocking lasts for the life of the SQL Server process, and calling it again is harmless, so each procedure can simply unlock first. During the 30-day trial any string works; after purchase, use your license code.

CREATE OR ALTER PROCEDURE dbo.ChilkatHello
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @hr int, @success int, @glob int, @http int;
    DECLARE @text nvarchar(4000), @err nvarchar(4000);

    EXEC @hr = sp_OACreate 'Chilkat.Global', @glob OUT;
    IF @hr <> 0
    BEGIN
        PRINT 'Could not create Chilkat.Global. HRESULT: 0x' + CONVERT(varchar(8), CONVERT(binary(4), @hr), 2);
        RETURN;
    END
    EXEC sp_OAMethod @glob, 'UnlockBundle', @success OUT, 'Anything for 30-day trial';
    IF @success <> 1
    BEGIN
        EXEC sp_OAGetProperty @glob, 'LastErrorText', @err OUT;
        PRINT @err;
        EXEC sp_OADestroy @glob;
        RETURN;
    END
    EXEC sp_OADestroy @glob;

    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT;
    EXEC sp_OAMethod @http, 'QuickGetStr', @text OUT, 'https://www.chilkatsoft.com/helloworld.txt';
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @success OUT;
    IF @success <> 1
    BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @err OUT;
        PRINT @err;
    END
    ELSE
        PRINT @text;

    EXEC sp_OADestroy @http;
END
GO

EXEC dbo.ChilkatHello;

The Messages tab shows Hello World!. The ProgID is Chilkat. plus the class name, for example Chilkat.JsonObject or Chilkat.Crypt2. Methods without a return value are called with NULL in place of the output variable: EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Accept', 'application/json';

Call a web service and return JSON as rows

Real responses are often longer than 4,000 characters, the most an sp_OAMethod output variable can reliably hold. This procedure turns on Chilkat's KeepStringResult, reads the full response from LastStringResult into an nvarchar(max), and returns the JSON members as a result set with OPENJSON (SQL Server 2016 and later, database compatibility level 130 or higher):

CREATE OR ALTER PROCEDURE dbo.ChilkatFruit
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @hr int, @success int, @glob int, @http int;
    DECLARE @dummy nvarchar(4000);
    DECLARE @errText TABLE (msg nvarchar(max));
    DECLARE @body TABLE (json nvarchar(max));

    EXEC @hr = sp_OACreate 'Chilkat.Global', @glob OUT;
    IF @hr <> 0 BEGIN RAISERROR('Could not create Chilkat.Global.', 16, 1); RETURN; END
    EXEC sp_OAMethod @glob, 'UnlockBundle', @success OUT, 'Anything for 30-day trial';
    EXEC sp_OASetProperty @glob, 'KeepStringResult', 1;   -- keep full string results in LastStringResult
    EXEC sp_OADestroy @glob;
    IF @success <> 1 BEGIN RAISERROR('Chilkat could not be unlocked.', 16, 1); RETURN; END

    -- {"apple":"red", "lime":"green", "banana":"yellow", ...}
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT;
    EXEC sp_OASetProperty @http, 'ConnectTimeout', 10;
    EXEC sp_OASetProperty @http, 'ReadTimeout', 30;
    EXEC sp_OAMethod @http, 'QuickGetStr', @dummy OUT, 'https://www.chilkatsoft.com/exampledata/sample.json';
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @success OUT;
    IF @success <> 1
    BEGIN
        INSERT INTO @errText EXEC sp_OAGetProperty @http, 'LastErrorText';
        SELECT msg AS LastErrorText FROM @errText;
        EXEC sp_OADestroy @http;
        RETURN;
    END

    INSERT INTO @body EXEC sp_OAGetProperty @http, 'LastStringResult';
    EXEC sp_OADestroy @http;

    DECLARE @json nvarchar(max) = (SELECT json FROM @body);
    SELECT [key] AS fruit, [value] AS color FROM OPENJSON(@json);
END

Reading LastErrorText the same way, through a table variable, avoids cutting off a long error report.

String and binary limits of the sp_OA procedures

These limits belong to SQL Server's OLE Automation procedures, not to Chilkat, and Chilkat has workarounds for each:

Things to know when calling Chilkat from T-SQL

Security

• Enabling Ole Automation Procedures lets code with permission to run the sp_OA procedures create COM objects inside the SQL Server process. Some security baselines require the option to be off, so agree on it with whoever manages the server.
• By default only members of sysadmin can run the sp_OA procedures. Rather than granting EXECUTE on them to application users, put the Chilkat calls in stored procedures that you control.
• Keep secrets such as passwords, private keys, and your Chilkat unlock code out of code that other users can read, and restrict who can alter those procedures.

Troubleshooting

Next Steps

• 📖 Chilkat SQL Server Reference Documentation — every class, method, and property
• 💻 Chilkat SQL Server examples — thousands of ready-to-run examples
• 📦 Chilkat ActiveX downloads (MSI installers and ZIP files)
• ✉ Contact Chilkat support

SQL Server is a trademark of the Microsoft group of companies.