In this blog, I will show step by step how to call a Web Service or Rest API in your T-SQL. explain how its works and why we need to do this. Let’s Go.

I. MSXML?

Microsoft XML Core Services (MSXML) are set of services that allow applications written in JScript, VBScript, and Microsoft development tools to build Windows-native XML-based applications.

Microsoft XML Core Services (MSXML) is a set of services that allow developers to build interoperable XML-centered applications on all platforms supporting XML 1.0.

MSXML is compatible with applications written by various development tools executed under the Windows operating platform, such as Visual Basic and JavaScript. MSXML provides multiple required application services, including document object model (DOM), Simple API for XML (SAX), XML data reduced schema (XDR), and schema object model (SOM).

II. T-SQL means?

T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing, and declared variables.

for more info and knowledge about MSSQL just visit this Link or HERE!

LETS’S PROCEED;

III QUERY SET-UP

Here is it,

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',  
                 '<API URL HERE >', -- API URL
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText  --
Exec sp_OADestroy @Object

In T-SQL query view

DECLARE

The DECLARE statement initializes a variable by assigning it a name and a data type. The variable name must start with the “@“sign. In this example, the data type of the "@Object" variable is int and “@ResponseText” is Varchar. Then inside the open and close bracket  “()” / Varchar(8000), is the size length of the declared capacity of “@ResponseText“.

you can change it depending on the required length. also, you can set it MAX

Declare @ResponseText as Varchar(MAX);

EXEC or EXECUTE

The Exec command is used to execute like Store Procedure or a SQL string passed to it. You can also use full command EXECUTE which is the same as EXEC.

Mostly exec is used to execute a command like Stored Procedure of a long T-SQL command.

Extended Stored Procedures

These are Extended Stored Procedures, and basically,

sp_OACreate is an extended Stored Procedure that initializes during the execution of creating the function. is like connecting database this is the equal command of open connection.

sp_OAMethod is an extended Stored Procedure that initializes a command to send a request like “GET, POST, PUT and etc.”.

sp_OADestroy is also an Extended Stored Procedure that initializes the closed command. same for close connection it’s initialized that command is done and finish or close connection.

Get Response of Web Service or Rest API

Select @ResponseText 

This part is returning the response of the web services or the rest api.

T-SQL Code

Declare @Object as int;
Declare @ResponseText as Varchar(MAX);

--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post', 
                 'https://api.javelupango.com/access/index.php?id=1', -- API URL
                 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText  --
Exec sp_OADestroy @Object

Expected Ouput

“SUCCESSS”

Concern

if you are facing like this error, please see the solution below.

Run this code to your T-SQL command.

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;

Related Topics

One Reply to “Calling a Web Services or Rest API from T-SQL using MSXML”

Leave a Reply

Your email address will not be published. Required fields are marked *