TAJUZZAMAN

Experienced Senior Software Engineer with a demonstrated history of working in the information technology and services industry. Skilled in .Net Software Development, MSSQL, Oracle Database, C#, PHP, Google Cloud Platform and Microsoft Azure. Strong engineering professional with a Bachelor’s Degree focused in Artificial Intelligence from Universiti Teknologi MARA Malaysia. Currently work as Lead Solution Architect and Director of new startup IT company named Aurora Cloud Works Sdn. Bhd.

Submit Data To WebAPI from MSSQL Database

Nowadays, web api is the suitable medium to do integration between system. Here i show one of the way to execute the WebAPI from MSSQL to update send the data.

I will use Stored Procedure to implement the execution.


Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
DECLARE @executeApiURL VARCHAR(MAX)
SET @executeApiURL = 'https://tajuzzaman.com/api/Sample.ashx?ApiKey=' + LTRIM(RTRIM(@cappmobileApiKey)) + '&Empno=' + @Empno
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @executeApiURL, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object

In some cases you may encounter this error message during execution.

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'

This issue happen because by default MSSQL default disable the function. To enable it run the statement below :


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

Thanks.