Invoke a Web Service Method From A SQL Stored Procedure

No this wasn’t my idea. The company I am currently am contracting for has quite a bit of its infrastructure wrapped up in web services. I needed to invoke a method of one of these services in a stored procedure. So this is the story of how to do that….

First we need to modify the web.config file for your web service. This code should go under .

<webServices>
       <protocols>
         <add name="HttpGet"/>
         <add name="HttpPost"/>
       </protocols>
     </webServices>

Next we need to make sure your SQL Server will allow you to make this kind of call. You will need elevated permissions to run this script. Before you allow your SQL Server to access functions outside of SQL Server consider this. But since I had no choice in this situation in your SQL Server Management Studio query window paste the following:

exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'Ole Automation Procedures', 1 -- Enable
-- exec sp_configure 'Ole Automation Procedures', 0 -- Disable
go
reconfigure
go
exec sp_configure 'show advanced options', 0
go
reconfigure
go

Next we will want to write the stored procedure itself. Note in your web service address it is important to end the address with a question mark. (?).

USE [DATABASENAMEHERE]
GO

/****** Object:  StoredProcedure [dbo].[Web_Service_Invoke]     ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Kelly Martens>
-- Create date: <4/23/2018>
-- Description:    <Stored Procedure to invoke web service method>
-- =============================================
CREATE PROCEDURE [dbo].[Web_Service_Invoke]
     -- Add the parameters for the stored procedure here
    
AS
DECLARE @obj Integer
DECLARE @sUrl varchar(200)
DECLARE @response varchar(8000)
SET @sURL = 'http://webserviceaddress.asmx/MethodName?'
EXEC sp_OACreate 'MSXML2.ServerXMLhttp',@obj OUT
EXEC sp_OAMethod @obj,'Open',NULL,'GET',@sURL,false
EXEC sp_OAMethod @obj,'send'
EXEC sp_OAGetProperty @obj,'responsetext',@response OUT
SELECT @response [response]
EXEC sp_OADestroy @obj
RETURN

If in the event you have parameters you need to pass to the procedure do this:

Param1=’ + @value + '’

When  you call this stored procedure, the output of the method called, the HTML of the page, will be returned to you, looking something like this:

<?xml version="1.0" encoding="utf-8"?>  <string xmlns="http://tempuri.org/">COMPLETE</string>

Should your permissions not have been configured properly or there is an error in the web service itself, it will also let you know.

Finally let me emphasize again, this is NOT the optimal way to do this. You should be using a WCF service, using SQL CLR and not monkeying about with your permissions. But this is available if you need it.