How To Call A Web Service From SQL Server

Talaviya Bhavdip

18 Comments

Join the conversation! Your thoughts help the community grow.

Janis Baunis4y ago
Thanks, great article!
Nitendra Kumar4y ago
I have Uri as https and I am getting certificate error. Please suggest
Vitor Correia5y ago
Its easier to publish a C# assembly and use it to consume webservice
Richard Vera5y ago
I need send a parameter, how i do.?
Clint Alan6y ago
0x80072F0D msxml3.dll The certificate authority is invalid or incorrect
Clint Alan6y ago
Do you have any video for this ? I always get this error:
Manoj vaish6y ago
I want to call web services within stored procedure and that web service will return json data and store into the table. PLease help
ahmad meithaluny6y ago
Thx pat languge arabic show ????? why?
Martin Festa6y ago
Muchas Gracias! Me sirvio perfecto para el consumo de una API REST con metodo POST. Excelente aporte.
James Smith6y ago
What if my requestBody length is greater than 8000? I tried changing to VARCHAR(MAX), but it returns 'FAILED'
- Sud V6y ago
  Hello James, did you get a solution to your problem? I'm have to do the same
Amit Mohanty7y ago
Nice and useful info.
Rajesh Panchal7y ago
Good example and liked the efforts you gave. By the way, do you know what if we would like to publish it on client machine? I mean above mentioned script will be required to execute on their database? If yes, what could be the easiest solution?
- Talaviya Bhavdip7y ago
  Hello, Rajesh Panchal just you need to change the URI (if you want to publish your web service or API in local then you need to publish in your static IP in IIS)and the script will run from them anywhere you want.
Laxmidhar Sahoo7y ago
Good Descption
Kaushik Dudhat7y ago
Nice blog Talaviya Bhavdip.
Nikunj Satasiya7y ago
Greate Bhavdipbhai... Your Every Blog is Very Helpful... Thanks For Sharing Your Knowledge with us.
Faisal Pathan7y ago
Nice topic and content

Introduction

In this blog, I have shown the process of calling web services through a stored procedure in SQL Server database. Also, I have explained how to call a stored procedure with a SOAP Envelope.

Step 1

Create a stored procedure in your SQL Server.

CREATE proc [dbo].[spHTTPRequest]
@URI varchar(2000) = 'http://localhost:55253/',
@methodName varchar(50) = 'Get',
@requestBody varchar(8000) = '',
@SoapAction varchar(255),
@UserName nvarchar(100), -- Domain\UserName or UserName
@Password nvarchar(100),
@responseText varchar(8000) output
as
SET NOCOUNT ON
IF @methodName = ''
BEGIN
select FailPoint = 'Method Name must be set'
return
END
set @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Create failed',
MedthodName = @methodName
goto destroy
return
END
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Open failed',
MedthodName = @methodName
goto destroy
return
END
-- set request headers
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
-- set soap action
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
declare @len int
set @len = len(@requestBody)
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
/*
-- if you have headers in a table called RequestHeader you can go through them with this
DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)
DECLARE RequestHeader CURSOR
LOCAL FAST_FORWARD
FOR
SELECT HeaderKey, HeaderValue
FROM RequestHeaders
WHERE Method = @methodName
OPEN RequestHeader
FETCH NEXT FROM RequestHeader
INTO @HeaderKey, @HeaderValue
WHILE @@FETCH_STATUS = 0
BEGIN
--select @HeaderKey, @HeaderValue, @methodName
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'SetRequestHeader failed',
MedthodName = @methodName
goto destroy
return
END
FETCH NEXT FROM RequestHeader
INTO @HeaderKey, @HeaderValue
END
CLOSE RequestHeader
DEALLOCATE RequestHeader
*/
-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'Send failed',
MedthodName = @methodName
goto destroy
return
END
declare @statusText varchar(1000), @status varchar(1000)
-- Get status text
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status, @statusText, @methodName
-- Get response text
exec sp_OAGetProperty @objectID, 'responseText', @responseText out
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
source = @source,
description = @desc,
FailPoint = 'ResponseText failed',
MedthodName = @methodName
goto destroy
return
END
destroy:
exec sp_OADestroy @objectID
SET NOCOUNT OFF
GO

The Stored Procedure takes the following parameters.

@URI: the URI of the web service
@MethodName: this would be ‘GET’ or ‘POST’
@RequestBody: this is the SOAP xml that you want to send
@SoapAction: this the operation that you want to call on your service
@UserName: NT UserName if your web service requires authentication
@Password: the password if using NT Authentication on the web service
@ResponseText: this is an out parameter that contains the response from the web service

Step 2

Make the setting in SQL for it.

Use master
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;

Step 3

Call the stored procedure (Here is a sample call to my service).

declare @xmlOut varchar(8000)
Declare @RequestText as varchar(8000);
set @RequestText=
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
<soapenv:Header/>
<soapenv:Body>
<tem:CreateOrder>
<tem:OrderRequest>
<tem:OrderId>200</tem:OrderId>
<tem:OrderName>something</tem:OrderName>
</tem:OrderRequest>
</tem:CreateOrder>
</soapenv:Body>
</soapenv:Envelope>'
exec spHTTPRequest
'http://localhost/testwebservices/helloworldservice.asmx',
'POST',
@RequestText,
'http://tempuri.org/CreateOrderForMe', -- this is your SOAPAction:
'', '', @xmlOut out
select @xmlOut

Make sure your SOAP action is correct. Copy this action from your services. It will show up when your service is RUN.

Conclusion

In this blog, I have explained how to call a web service from the stored procedure in SQL.