T-sql Web servcies

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
LVL 2
lankapalaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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 ExpertCommented:
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
lankapalaAuthor 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)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

lankapalaAuthor Commented:
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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 ExpertCommented:
>> 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?
lankapalaAuthor 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'
lankapalaAuthor Commented:
@Pawan Kumar
Sql Server 2008R2
Pawan KumarDatabase ExpertCommented:
can you pls share your entire code. Will fix.
Pawan KumarDatabase ExpertCommented:
@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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lankapalaAuthor Commented:
@Pawan Kumar:
Still NULL retun
But sameway it's working at IE browser
Pawan KumarDatabase ExpertCommented:
Al@author

Do u know any parameter values where for which it will return value?
ukerandiCommented:
Select @ReturnData?
Pawan KumarDatabase ExpertCommented:
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 DeveloperCommented:
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 ExpertCommented:
Question abandoned
Provided multiple solutions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.