activecampaign-sql-ole-automation-example.sql

-- Enable OLE Automation

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

-- Create a Stored Procedure

-- Note: Replace {yourHostName} and {yourApiKey} with your values.

DROP PROCEDURE uspTestAC 
GO;

CREATE PROCEDURE uspTestAC AS
BEGIN
    DECLARE @iObj int;
	DECLARE @sURL nvarchar(255);
    DECLARE @sToken nvarchar(255);
	DECLARE @sResponseText nvarchar(4000);
	DECLARE @ret int;
	DECLARE @status NVARCHAR(32);
	DECLARE @statusText NVARCHAR(32);

    SET @sUrl = 'https://{yourHostName}.api-us1.com/api/3/campaigns'
    SET @sToken = '{yourApiKey}'

	EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @iObj OUT;
	IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

    EXEC @ret = sp_OAMethod @iObj, 'open', NULL, 'get', @sUrl, 'false'
    EXEC @ret = sp_OAMethod @iObj, 'SetRequestHeader', null, 'Api-Token', @sToken
    EXEC @ret = sp_OAMethod @iObj, 'SetRequestHeader', null, 'Accept', 'application/json'
    EXEC @ret = sp_OAMethod @iObj, 'SetRequestHeader', null, 'Content-Type', 'application/json'
    EXEC @ret = sp_OAMethod @iObj, 'Send', null
	IF @ret <> 0 RAISERROR('Error sending request.', 10, 1);

	EXEC @ret = sp_OAGetProperty @iObj, 'status', @status OUT;
	EXEC @ret = sp_OAGetProperty @iObj, 'statusText', @statusText OUT;
	EXEC @ret = sp_OAGetProperty @iObj, 'responseText', @sResponseText OUT;

	IF @ret <> 0 RAISERROR('Error getting response text.', 10, 1);

	PRINT 'Status: ' + @status + ' (' + @statusText + ')';
	PRINT 'Response text: ' + ISNULL(@sResponseText, 'NULL');

	IF((SELECT @sResponseText) <> '')
	BEGIN
		 DECLARE @json NVARCHAR(MAX) = (SELECT @sResponseText)
		 SELECT *
		 FROM OPENJSON(@json)
		      WITH (
		             CampaignName NVARCHAR(200) '$.campaign.name',
		             CampaignType NVARCHAR(30) '$.campaign.type'
		           );
	END
	ELSE
	BEGIN
		 DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
		 Print @ErroMsg;
	END

    EXEC sp_OADestroy @iObj
END
GO;

-- Call Stored Procedure

EXEC uspTestAC;