SQL Server API POST calls - error in using SP_OACreate

Question

I am trying to setup a simple POST procedure through API but have been stumped on the first step - creating an object. I am running this on SQL Server 2012 SP4-GDR

I have confirmed that the OleAutomationEnabled is TRUE.

I am using SSMS with a user that is a member of the sysadmin role and have given explicit permission to sp_OACreate to that user. Something that has me baffled is that when looking at the properties for master.sys.OACreate there are no users or roles other than the explicit one I defined earlier.

Trying to create an object returns an integer other than zero meaning it failed but cannot find any information on the specific integer pointing to a solution.

This is my script and result to troubleshoot:

USE master

DECLARE @hr int
DECLARE @Object AS INT;

EXEC @hr = sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;

IF @Object = 0 
     SELECT 'sp_OACCreate success', @object
ELSE
     SELECT 'Error in sp_OACreate', @object AS [objectReturned], CONVERT(binary(4), @object) AS [Code]
END   -- 0 is success

EXEC sp_OADestroy @Object

Result:

(No column name)    objectReturned  Code
Error in sp_OACreate    16711422    0x00FEFEFE

Can someone point me into the right direction?

Answer 1

You should check the @hr variable for success/failure (0 is success) of sp_OACreate calls, since @Object will actually contain the handle to the created object.

I often use the following gist to point to how to do things when you want to post some data from SQL Server: Gist on how to use sp_OACreate for posting data