<p>Is there a way to call out from a TSQL stored procedure or function to a webservice?</p>

[{'answer_id': 33455, 'author': 'Dillie-O', 'author_id': 71, 'author_profile': 'https://Stackoverflow.com/users/71', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>It is a little lengthy to discuss in detail here, but here\'s a link to an <a href="http://msdn.microsoft.com/en-us/library/ms131094.aspx" rel="noreferrer">MSDN article</a> on the topic.</p>\n'}, {'answer_id': 33799, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 2, 'selected': False, 'text': '<p>In earlier versions of Sql, you could use either an extended stored proc or xp_cmdshell to shell out and call a webservice.</p>\n\n<p>Not that either of these sound like a decent architecture - but sometimes you have to do crazy stuff.</p>\n'}, {'answer_id': 33884, 'author': 'Orion Edwards', 'author_id': 234, 'author_profile': 'https://Stackoverflow.com/users/234', 'pm_score': 5, 'selected': False, 'text': "<p>Sure you <em>can</em>, but this is a terrible idea. </p>\n\n<p>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. </p>\n\n<p>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.</p>\n\n<ol>\n<li><p>Whichever application did the <code>INSERT INTO BLAH</code> 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</p></li>\n<li><p>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.</p></li>\n</ol>\n"}, {'answer_id': 1947732, 'author': 'Brian', 'author_id': 237021, 'author_profile': 'https://Stackoverflow.com/users/237021', 'pm_score': 2, 'selected': False, 'text': "<p>You can do it with the embedded VB objects.</p>\n\n<p>First you create one VB object of type 'MSXML2.XMLHttp', and you use this one object for all of your queries (if you recreate it each time expect a heavy performance penalty).</p>\n\n<p>Then you feed that object, some parameters, into a stored procedure that invokes sp_OAMethod on the object.</p>\n\n<p>Sorry for the inprecise example, but a quick google search should reveal how the vb-script method is done.</p>\n\n<p>--</p>\n\n<p>But the CLR version is much....MUCH easier.\nThe problem with invoking webservices is that they cannot keep pace with the DB engine. You'll get lots of errors where it just cannot keep up.</p>\n\n<p>And remember, web SERVICES require a new connection each time. Multiplicity comes into play. You don't want to open 5000 socket connections to service a function call on a table. Thats looney!</p>\n\n<p>In that case you'd have to create a custom aggregate function, and use THAT as an argument to pass to your webservice, which would return a result set...then you'd have to collate that. Its really an awkward way of getting data.</p>\n"}, {'answer_id': 1955255, 'author': 'bander', 'author_id': 237897, 'author_profile': 'https://Stackoverflow.com/users/237897', 'pm_score': 1, 'selected': False, 'text': '<p>If you\'re working with sql 2000 compatibility levels and cannot do clr integration, see <a href="http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)-using-MSXML.aspx" rel="nofollow noreferrer">http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)-using-MSXML.aspx</a></p>\n'}, {'answer_id': 30211736, 'author': 'Kiran.Bakwad', 'author_id': 2940450, 'author_profile': 'https://Stackoverflow.com/users/2940450', 'pm_score': 6, 'selected': True, 'text': "<p>Yes , you can create like this</p>\n\n<pre><code>CREATE PROCEDURE CALLWEBSERVICE(@Para1 ,@Para2)\nAS\nBEGIN\n    Declare @Object as Int;\n    Declare @ResponseText as Varchar(8000);\n\n    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;\n    Exec sp_OAMethod @Object, 'open', NULL, 'get', 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT','false'\n    Exec sp_OAMethod @Object, 'send'\n    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT\n    Select @ResponseText\n    Exec sp_OADestroy @Object\nEND\n</code></pre>\n"}, {'answer_id': 43993270, 'author': 'Tom Stickel', 'author_id': 756246, 'author_profile': 'https://Stackoverflow.com/users/756246', 'pm_score': 3, 'selected': False, 'text': '<p>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. </p>\n\n<p>Here is an example of what I have done.</p>\n\n<ol>\n<li>Triggers Insert and Update on a Table </li>\n<li>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.  </li>\n</ol>\n\n<p>Don\'t do old XML </p>\n\n<p>JSON </p>\n\n<pre><code>EXEC sp_OACreate \'WinHttp.WinHttpRequest.5.1\', @Object OUT;\nEXEC sp_OAMethod @Object, \'Open\', NULL, \'POST\', \'http://server/api/method\', \'false\'\nEXEC sp_OAMethod @Object, \'setRequestHeader\', null, \'Content-Type\', \'application/json\'\nDECLARE @len INT = len(@requestBody) \n</code></pre>\n\n<p>Full example:</p>\n\n<pre><code>Alter Procedure yoursprocname\n\n @WavName varchar(50),\n @Dnis char(4) \n\n    AS\nBEGIN\n\n    SET NOCOUNT ON;\n\n\nDECLARE @Object INT;\nDECLARE @Status INT;\n\n\nDECLARE @requestBody NVARCHAR(MAX) = \'{\n"WavName": "{WavName}",\n"Dnis": "{Dnis}"\n}\'\n\n\nSET @requestBody = REPLACE(@requestBody, \'{WavName}\', @WavName)\nSET @requestBody = REPLACE(@requestBody, \'{Dnis}\', @Dnis)\n\n\nEXEC sp_OACreate \'WinHttp.WinHttpRequest.5.1\', @Object OUT;\nEXEC sp_OAMethod @Object, \'Open\', NULL, \'POST\',  \'http://server/api/method\', \'false\'\nEXEC sp_OAMethod @Object, \'setRequestHeader\', null, \'Content-Type\', \'application/json\'\nDECLARE @len INT = len(@requestBody) \nEXEC sp_OAMethod @Object, \'setRequestHeader\', null, \'Content-Length\', @len\nEXEC sp_OAMethod @Object, \'send\', null, @requestBody\nEXEC sp_OAGetProperty @Object, \'Status\', @Status OUT\nEXEC sp_OADestroy @Object\n</code></pre>\n'}, {'answer_id': 54149765, 'author': 'Guillermo Jimenez', 'author_id': 10349499, 'author_profile': 'https://Stackoverflow.com/users/10349499', 'pm_score': 1, 'selected': False, 'text': '<p>I been working for big/global companies around the world, using Oracle databases. We are consuming web services all time thru DB with store procedures and no issues, even those ones with heavy traffic. All of them for internal use, I mean with no access to internet, only inside the plant. I would recommend to use it but being really careful about how you design it</p>\n'}, {'answer_id': 59046429, 'author': 'Jan', 'author_id': 2386040, 'author_profile': 'https://Stackoverflow.com/users/2386040', 'pm_score': 2, 'selected': False, 'text': "<p>Here'a an example to get some data from a webservice. In this case parse a user agent string to JSON.</p>\n\n<pre><code>--first configure MSSQL to enable calling out to a webservice (1=true, 0=false)\nsp_configure 'show advanced options', 1;  \nGO  \nRECONFIGURE;  \nGO  \nsp_configure 'Ole Automation Procedures', 1;  \nGO  \nRECONFIGURE;  \nGO  \n\nCREATE PROCEDURE CallWebAPI_ParseUserAgent @UserAgent VARCHAR(512)\nAS\nBEGIN\n    SET NOCOUNT ON;\n\n    DECLARE @Object INT;\n    DECLARE @ResponseText AS VARCHAR(8000);\n    DECLARE @url VARCHAR(512)\n\n    SET @url = 'http://www.useragentstring.com/?getJSON=all&amp;uas=' + @UserAgent;\n\n    EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;\n    EXEC sp_OAMethod @Object, 'Open', NULL, 'GET', @url, 'false'\n    EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json'\n    EXEC sp_OAMethod @Object, 'send'\n    EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT\n    SELECT @ResponseText\n    EXEC sp_OADestroy @Object\nEND\n\n--example how to call the API\nCallWebAPI_ParseUserAgent 'Mozilla/5.0 (Windows NT 6.2; rv:53.0) Gecko/20100101 Firefox/53.0'\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/33449', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1874/']