Can you call a webservice from TSQL code?

call webservice from sql server 2016
sql server stored procedure call web api
call webservice from ssis
sql call api json
sql server call webhook
web service to execute ssis package
call google api from sql server
call web service in sql server 2008

Is there a way to call out from a TSQL stored procedure or function to a webservice?

Yes , you can create like this

CREATE PROCEDURE CALLWEBSERVICE(@Para1 ,@Para2)
AS
BEGIN
    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);

    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get', 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT','false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    Select @ResponseText
    Exec sp_OADestroy @Object
END

How To Call A Web Service From SQL Server, In this blog, I will explain how to call web services through a stored procedure in the SQL database. Call a webservice from TSQL (Stored Procedure) using MSXML. SQL code for WebService in T-SQL. Call a webservice from TSQL (Stored Procedure) using MSXML.

Sure you can, but this is a terrible idea.

As web-service calls may take arbitrary amounts of time, and randomly fail, depending on how many games of counterstrike are being played on your network at the time, you can't tell how long this is going to take.

At the bare minimum you're looking at probably half a second by the time it builds the XML, sends the HTTP request to the remote server, which then has to parse the XML and send a response back.

  1. Whichever application did the INSERT INTO BLAH query which caused the web-service to fire is going to have to wait for it to finish. Unless this is something that only happens in the background like a daily scheduled task, your app's performance is going to bomb

  2. The web service-invoking code runs inside SQL server, and uses up it's resources. As it's going to take a long time to wait for the HTTP request, you'll end up using up a lot of resources, which will again hurt the performance of your server.

Calling Web Service From SQL Server Using SQL CLR, In this blog, you will learn about how to call a Web Service from SQL By using CLR, we can run and manage the code inside the SQL Server. One of the problems you might run into when building applications that consume web services is how you can use web services data within a SQL Server instance. One of the reasons you might want to do this is so you can join a record set that is returned from a web service with one of your SQL Server tables.

Not in T-SQL code itself, but with SQL Server 2005 and above, they've enabled the ability to write CLR stored procedures, which are essentially functions in .NET code and then expose them as stored procedures for consumption. You have most of the .NET framework at your fingertips for this, so I can see consumption of a web service possible through this.

It is a little lengthy to discuss in detail here, but here's a link to an MSDN article on the topic.

How to invoke a Web Service from a Stored Procedure, Learning how to make a call to a Web Service from a Stored Procedure. stored procedure that will have all the code to invoke the Web Services we is AdventureWorks, which is the basis of test data that brings SQL Server. I am trying to build a stored procedure in TSQL to call a webservice. I've done this before in Oracle, but it seems like it's not so easy in MSSQL. There are of course many reasons not to do this in a stored proc, but since this procedure is only to be used in a daily batch, performance is not too much of a issue.

I would not do this for heavy traffic or mission critical stuff, HOWEVER, if you do NOT need to receive feedback from a service, then it is actually a great thing to do.

Here is an example of what I have done.

  1. Triggers Insert and Update on a Table
  2. Trigger called Stored Proc that is passes the JSON data of the transaction to a Web Api Endpoint that then Inserts into a MongoDB in AWS.

Don't do old XML

JSON

EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'POST', 'http://server/api/method', 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
DECLARE @len INT = len(@requestBody) 

Full example:

Alter Procedure yoursprocname

 @WavName varchar(50),
 @Dnis char(4) 

    AS
BEGIN

    SET NOCOUNT ON;


DECLARE @Object INT;
DECLARE @Status INT;


DECLARE @requestBody NVARCHAR(MAX) = '{
"WavName": "{WavName}",
"Dnis": "{Dnis}"
}'


SET @requestBody = REPLACE(@requestBody, '{WavName}', @WavName)
SET @requestBody = REPLACE(@requestBody, '{Dnis}', @Dnis)


EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'POST',  'http://server/api/method', 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
DECLARE @len INT = len(@requestBody) 
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
EXEC sp_OAMethod @Object, 'send', null, @requestBody
EXEC sp_OAGetProperty @Object, 'Status', @Status OUT
EXEC sp_OADestroy @Object

Calling a Web Service from within SQL Server, If you need to write a T-SQL statement to join some web service you can now encapsulate a call to a web service within the code of a stored  Calling a Web Service from T-SQL (Stored Procedure) using MSXML. May 08, 2013 11:56 AM. | zoggling | LINK. We are looking to send XML requests and receive responses back via XML to/from internal and external web services, and would like to do this via stored procedures in SQL Server. The following link is one way to achieve this:

In earlier versions of Sql, you could use either an extended stored proc or xp_cmdshell to shell out and call a webservice.

Not that either of these sound like a decent architecture - but sometimes you have to do crazy stuff.

SQL Server SOAP Web Service (HTTP POST), Demonstrates how to call PostXml to call a SOAP web service. County by Postcode(First Section of Post Code) -- The SOAP XML will look like this: -- <?xml​  In this tip, we will learn how to work with Web Services using SQL Server Integration Services (SSIS). We will use a simple example to call a web service from SSIS and store the results in a file. Solution . A web service is a method over the network used to share information between applications.

I want to call a webservice from stored procedure. I am using SQL , I want to call a webservice from stored procedure. Below is my code. put a size after nvarchar - otherwise you will get the SQL Default  Proceed to create our stored procedure that will have all the code to invoke the Web Services we have just created, as in all my articles database I use is AdventureWorks, which is the basis of test data that brings SQL Server. Step 4 . Then publish the web service on our IIS Web Server. Step 5

Call a webservice from TSQL (Stored Procedure) using MSXML , SQL code for WebService in T-SQL a table called RequestHeader you can go through them with this DECLARE @HeaderKey varchar(500),  Calling an API from asp.net web form is very easy. But can we call an API from Sql server stored-procedure. If yes then how can we call an API from a Sql server stored-procedure and how can we get API response. Please see a link for more details. Hi, this code is not working when trying to call an ASP.NET Web API endpoint.

Example using Web Services with SQL Server Integration Services, In this tip, we will learn how to work with Web Services using SSIS. We will use a simple example to call a web service from SSIS and store the results in a file. back to fill the rest of my query requirements in SQL Server? Today I’ll show how you can consume a Web Service from within SQL Server using the SQL Server CLR. DISCLAIMER: This is just intended as proof of concept. I do feel that if you wish to consume web services from SQL Server, then you should probably put this code in an assembly outside SQL

Comments
  • Agreed that it's not something want to do, however sometimes you have to do things you don't ideally like to do. In this case I ended up re-writing the web service function as a sql function to accomplish the same task. Thanks for all the links and suggestions.
  • How to call https service?
  • Don't forget to re-configure MSSQL to enable calling out to a webservice. See my answer below. I got an error 'SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.'
  • +1 for "depending on how many games of counterstrike are being played on your network at the time" and for the good answer. ;)
  • If you're going to say that it can be done, you should explain how.
  • The responder below put it so well (after providing some useful leads): "Not that either of these sound like a decent architecture - but sometimes you have to do crazy shit".
  • I can also add a more recent (may 2009) article on this subject: databasejournal.com/features/mssql/article.php/3821271/…
  • Thanks for the example. Would you also include Exec sp_OADestroy @Object as in @kiran.Backwad 's example
  • @TheEmirOfGroofunkistan -- ok there is post of full code
  • Some people moved onto CLR - This works, so I just never bothered with replacing sp_OA* calls
  • I use this script but nothing sent to webservice . why !?
  • Something to keep in mind, using embedded CLR tends to perform MUCH, MUCH, MUCH better than using OLE objects. I had some small CLR functions, which for client reasons had to be converted to call OLE instead. Once that happened, it took a MASSIVE performance hit. CLR functions that returned, for all intents and purposes, instantly, suddenly took nearly a second to return per call.