Table of Contents
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
Import SQL Database in SSMS from Local Machine
In this article, We learn how to Import SQL Database in SSMS in Local Machine. I will show how to do it, and just follow […]
Export(Backup) SQL Database in SSMS in Local Machine
In this article, We learn how to export(Backup) SQL Database in SSMS in Local Machine. I will show how to do it, and just follow […]
Angular CRUD with .NET Core API
In this article, We create a full tutorial of Angular CRUD with .NET Core API. I will show how to do it, and just follow […]
Create Table in MSSQL using T-SQL Command/Statement
In this blog, I will show how to create Table in MS SQL using T-SQL command. explain how its works and why we need to […]
Use UNION Operator in SQL
In this blog, I will show how to use and where to use UNION in SQL. Explain How its work and why we need to use […]
Hello! This is kind of off topic but I need some guidance from an established blog. Is it hard to set up your own blog? I’m not very techincal but I can figure things out pretty fast. I’m thinking about creating my own but I’m not sure where to start. Do you have any points or suggestions? Thanks
great put up, very informative. I wonder why the opposite specialists of this sector don’t understand this. You should continue your writing. I’m sure, you’ve a great readers’ base already!
I think other web site proprietors should take this website as an model, very clean and wonderful user genial style and design, let alone the content. You are an expert in this topic!
The other day, while I was at work, my sister stole my iphone and tested to see if it can survive a thirty foot drop, just so she can be a youtube sensation. My iPad is now broken and she has 83 views. I know this is totally off topic but I had to share it with someone!
Can I just say what a reduction to search out somebody who truly knows what theyre speaking about on the internet. You definitely know the best way to deliver a difficulty to mild and make it important. Extra folks need to read this and perceive this aspect of the story. I cant consider youre not more widespread since you definitely have the gift.
Really enjoyed this blog post, can I set it up so I receive an update sent in an email when there is a fresh article?
One other issue is when you are in a situation where you would not have a cosigner then you may really want to try to wear out all of your financial aid options. You will find many grants or loans and other free college funding that will supply you with funds to aid with school expenses. Thx for the post.
Normally I do not read article on blogs, but I would like to say that this write-up very forced me to check out and do so! Your writing taste has been amazed me. Thank you, quite great article.
I have been exploring for a little for any high-quality articles or blog posts on this sort of area . Exploring in Yahoo I at last stumbled upon this website. Reading this info So i am happy to convey that I’ve a very good uncanny feeling I discovered exactly what I needed. I most certainly will make certain to do not forget this website and give it a look regularly.
Wow, awesome weblog format! How long have you ever been running a blog for? you made blogging look easy. The entire look of your website is magnificent, let alone the content material!
you may have a great weblog right here! would you prefer to make some invite posts on my weblog?
My brother suggested I might like this web site. He was once totally right. This put up truly made my day. You cann’t believe just how a lot time I had spent for this info! Thanks!
I wasn’t sure I could manage such a lengthy article at the beginning. Your style captured my attention. This was a great piece. Great Article Neil. This article is fantastic. Even though I only read it a couple of times in the past, I did not leave a comment. But, I felt this article merited being mentioned.
Thanks for finally writing about > Calling a Web Services or Rest API from T-SQL using MSXML – CodeLife
< Liked it!
each time i used to read smaller posts that
also clear their motive, and that is also happening with this piece of writing which I am reading now.
Hello, this weekend is nice for me, for the reason that this point
in time i am reading this wonderful educational paragraph here
at my house.
It’s remarkable to go to see this site and reading the views of all mates on the topic of this article, while I am also keen of getting experience.