Call IIQ APIs From SQL Server Stored Procedures

Badge +1

Anyone in here Call IIQ APIs From SQL Server Stored Procedures?

4 replies

Userlevel 5
Badge +4

@mikegamble - We’ve used Stored Procedures to call IIQ API’s before but I’m told by my team that it was a pain so we eventually moved everything over to Powershell.

Badge +1

Thanks for your reply!  Does your team have a script that actually worked with IIQ, similar to this not_IIQ_API example?


DECLARE @URL NVARCHAR(MAX) = 'http://localhost:8091/api/v1/employees/getemployees';
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
IF((Select @ResponseText) <> '')
DECLARE @json NVARCHAR(MAX) = (Select @ResponseText)
EmployeeName NVARCHAR(30) '$.employeeName',
Title NVARCHAR(50) '$.title',
BirthDate NVARCHAR(50) '$.birthDate',
HireDate NVARCHAR(50) '$.hireDate',
Address NVARCHAR(50) '$.address',
City NVARCHAR(50) '$.city',
Region NVARCHAR(50) '$.region',
PostalCode NVARCHAR(50) '$.postalCode',
Country NVARCHAR(50) '$.country',
HomePhone NVARCHAR(50) '$.homePhone'
DECLARE @ErroMsg NVARCHAR(30) = 'No data found.';
Print @ErroMsg;
Exec sp_OADestroy @Object
Userlevel 5
Badge +4

@mikegamble - Since we converted all of our scripts over to PowerShell we don’t have the exact scripts that we used with IIQ, however the method is similar to this if this helps.

SET @authHeader = 'ADD AUTH HEADER';
SET @contentType = 'application/json';
SET @postData = '{
"@type": "MessageCard",
"@context": "",
"themeColor": "92C83E",
"summary": "SUMMARY CHANGE ME",
"sections": [{
"activityTitle": "TITLE CHANGE ME",
"activitySubtitle": "SUBTITLE CHANGE ME",
"activityImage": "",
"facts": [{
"name": "changes: ",
"value": "''"
"markdown": "true"
"Text" : "''"
print @postdata
--Infinite Campus Log
--SET @url = 'your url'
SET @url = 'your url'
-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', 'application/json';
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;
-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;
-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);


Userlevel 5
Badge +4

@mikegamble - Do you happen to be a Microsoft district by chance?