T-sql Web servcies

lankapala
lankapala used Ask the Experts™
on
Hi,
I need to run .wdsl web service from T-sql (write stored procedure).Any idea

See link below
http://ws.afd.co.uk/PCE_WebService.asmx?WSDL
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

Commented:
You should be able to do it similarly to this:
CREATE PROCEDURE GetAirportInfo
@AirportCode varchar(20)
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.net/airport.asmx/getAirportInformationByAirportCode?airportCode=' + @AirportCode,'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    Select @ResponseText
    Exec sp_OADestroy @Object
END

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Which SQL Server are you using SQL 2008  ?

For that i think you need to use SQL SERVER CLR or SSIS package.
For clr refer - https://blogs.msdn.microsoft.com/spike/2010/11/25/how-to-consume-a-web-service-from-within-sql-server-using-sql-clr/
For SSIS package - it is same as you call it in .NET code. here you need to use the script task.

Otherwise you can check the below code. TO consume a webservice you need 3 sps called sp_OACreate, sp_OAMethod and sp_OADestroy. I have created a stored procedure for you. If you want to pass the webservice name dynamically you can change it to parameters.

CREATE PROC CallWebService
AS
BEGIN

	Declare @Ob as Int, @ReturnData as Varchar(MAX);
	Exec sp_OACreate 'MSXML2.XMLHTTP', @Ob OUT;
	Exec sp_OAMethod @Ob, 'open', NULL, 'get', 'http://ws.afd.co.uk/PCE_WebService.asmx?WSDL','false'
	Exec sp_OAMethod @Ob, 'send'
	Exec sp_OAMethod @Ob, 'responseText', @ReturnData OUTPUT
	Select @ReturnData
	Exec sp_OADestroy @Ob

END

Open in new window

If you want to learn the entire code then you can follow from codeproject
https://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur

Author

Commented:
Hi, I tried above codes many times.Never worked.That's the reason i asking how to do that.
if you go to
http://ws.afd.co.uk/PCE_WebService.asmx?WSDL

i'm looking how to pass parameters.

PCEWebService.PostcodeEverywhere.AddressList(String strSerialNumber, String strPassword, String strUserID, String strPostcode, String strProperty, String strCountyType)
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
there are many ways to do so

select  the best for you
try this example
How to invoke a Web Service from a Stored Procedure
https://www.codeproject.com/Articles/428200/How-to-invoke-a-Web-Service-from-a-Stored-Procedur


check this one https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3f87619e-d98f-4f80-b0d5-86ebe055803a/how-to-pass-parameter-values-to-asmx-web-service-through-a-stored-procedure-in-sql-server-2008?forum=transactsql

DECLARE @url varchar(8000)
DECLARE @obj int
DECLARE @response varchar(8000)

SET @url = 'http://localhost/ServiceLayer/FetchVersion.asmx/getVersion?Id=Something'

exec sp_OACreate 'MSXML2.XMLHTTP', @obj OUT

exec sp_OAMethod @obj, 'open', NULL, 'get', @url, 'false'

exec sp_OAMethod @obj, 'send'
exec sp_OAGetProperty @obj, 'responseText', @response out

SELECT @response
exec sp_OADestroy @obj

Open in new window


try SSIS

 https://www.mssqltips.com/sqlservertip/3272/example-using-web-services-with-sql-server-integration-services/
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
>> I tried above codes many times.Never worked.That's the reason i asking how to do that.

asking again - Which version of SQL Server are you using?

Author

Commented:
@Kyle Abrahams
I used same exactly like you used,But still not working see my code

http://ws.afd.co.uk/PCE_WebService.asmx/AddressList?strSerialNumber=1111&strPassword=444&strUserID=Test1&strPostcode=NE3%202NS&strProperty=&strCountyType=Tyne

This is working in the IE explore,But not the SQL server

and also i'm getting error message From  your code  +   (+ @AirportCode) Incorrect syntax near +

 Exec sp_OAMethod @Object, 'open', NULL, 'get', 'http://www.webservicex.net/airport.asmx/getAirportInformationByAirportCode?airportCode=' + @AirportCode,'false'

Author

Commented:
@Pawan Kumar
Sql Server 2008R2
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
can you pls share your entire code. Will fix.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
@Author -
Updated code for you. Pass proper parameters to the stored procedure and it should work for you.

CREATE PROC CallWebService
(
	 @strSerialNumber VARCHAR(50)
	,@strPassword VARCHAR(50)
	,@strUserID VARCHAR(50)
	,@strPostcode VARCHAR(50)
	,@strProperty VARCHAR(50)
	,@strCountyType VARCHAR(50)
)
AS
BEGIN

	Declare @Ob as Int, @ReturnData as Varchar(MAX);
	DECLARE @WebU AS VARCHAR(MAX) = 'http://ws.afd.co.uk/PCE_WebService.asmx/AddressList?strSerialNumber'
	SET @WebU = @WebU + @strSerialNumber + '&strPassword=' + @strPassword + '&strUserID=' + @strUserID + '&strPostcode=' + @strPostcode + '&strProperty=' + @strProperty + '&strCountyType=' + @strCountyType
	Exec sp_OACreate 'MSXML2.XMLHTTP', @Ob OUT;
	Exec sp_OAMethod @Ob, 'open', NULL, 'get', @WebU,'false'
	Exec sp_OAMethod @Ob, 'send'
	Exec sp_OAMethod @Ob, 'responseText', @ReturnData OUTPUT
	Select @ReturnData
	Exec sp_OADestroy @Ob

END

Open in new window



You need to call like below -

EXEC CallWebService '1111','444','Test1','NE3 2NS','','Tyne'

Open in new window


Before the you need to enable this option

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;

Open in new window

Author

Commented:
@Pawan Kumar:
Still NULL retun
But sameway it's working at IE browser
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Al@author

Do u know any parameter values where for which it will return value?

Commented:
Select @ReturnData?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I think is once you pass proper values of parameters then you will get proper response if it is there. My code is working fine.

EXEC CallWebService '1111','444','Test1','NE3 2NS','','Tyne'

Open in new window

Kyle AbrahamsSenior .Net Developer
Commented:
My apologies, I did this without a sql server and had modified the SQL manually.

Attached is the corrected code off of my example . . . you need to do the modifications first and then pass that to a function.  SQL doesn't like an exec where you are adding a plus.

EG:

    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);
	declare @AirportCode varchar(20) = 'JFK'
-- format the url 
	declare @URL varchar(8000) = 'http://www.webservicex.net/airport.asmx/getAirportInformationByAirportCode?airportCode=' + @AirportCode

    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
--  now pass the formatted URL
    Exec sp_OAMethod @Object, 'open', NULL, 'get', @url,'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    Select @ResponseText
    Exec sp_OADestroy @Object

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Question abandoned
Provided multiple solutions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial