Retrieve campaign from ms sql OLEAutomation

Ask a question
1

using code
Set @sUrl = ‘https://account1611168746.api-us1.com/api/3/campaigns’
Set @sHeaders = ‘Api-Token …’
Exec master…sp_OACreate ‘MSXML2.XMLHTTP’, @iObj OUT
Exec master…sp_OAMethod @iObj, ‘Open’, NULL, ‘GET’, @sUrl, ‘false’
Exec master…sp_OAMethod @iObj, ‘setRequestHeader’, null, ‘Authorization’, @sHeaders
Exec master…sp_OAMethod @iObj, ‘send’
Exec master…sp_OADestroy @iObj

result error: The download of the specified resource has failed.
what is wrong?, any idea
thanks

2

Hey!

I updated your post to remove your API token. You may want to create a new token just to be sure no one does anything with your account you don’t want them to :wink:

It’s been a while since I’ve done any OLEAutomation in SQL. I’ll try to set up a test to reproduce to see if I can come up with any suggestions for you.

3

Good news and bad news. The good news is it’s possible to use OLE Automation to make a call to an ActiveCampaign API. The bad news is OLE Automation is limited to 4000 characters (nvarchar(4000)), so if a response is more than 4K, the response will be null. Most API requests to retrieve a collection of things, like contacts or campaigns, will easily exceed the 4K limit.

If you really want to be able to make API calls from SQL Server, a better alternative would be to use SQL CLR to build a stored procedure in Visual Studio and use the HttpClient to make those API calls.

4

thanks,but the limit length is not cause error, fortunately the length result is short, i found example to use automation but not work

5

Here is a link to a SQL script of the steps I took to get a successful OLE Automation example working. For context, I am running SQL Server 2016 on Windows Server 2012.