SQLTeam.com | Weblogs | Forums

I want to call a webservice from stored procedure. I am using SQL server 2008.Below is my code.Please guide me in solving the issue

CREATE PROCEDURE [dbo].[get_callWebService]
@userKey VARCHAR(200) =NULL OUTPUT,
@pass varchar(1000) = NULL OUTPUT

AS
BEGIN
DECLARE @obj INT
DECLARE @sUrl VARCHAR(200)
DECLARE @response VARCHAR(8000)
DECLARE @xml XML

SET @sUrl = 'http://www.geleservices.com/cab/asmx/login.asmx?op=AuthenticateUser?eMailId:'+@userKey+'password:'+@pass
EXEC sys.sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sys.sp_OAMethod @obj,'Open', NULL ,'GET', @sUrl, false
EXEC sys.sp_OAMethod @Obj ,send ,NULL ,''

EXEC sys.sp_OAGetProperty @obj, 'responseXML.xml', @response OUT

SELECT @response [response]
EXEC sys.sp_OADestroy @obj
RETURN
END

What problem do you have? Error message? (If so what does it say) Just doesn't work? Something else? otherwise I'm just guessing what your problem might be ...

'password:'

should maybe be

'?password:'

??

it doesnt return me anything...m passing emailId and password...using the email id and password i need to call my webservice and return the data accordingly.

I have tried the same way to invoke a webservice in a sql and i gain nul. Could anybody tell me what is the problem?
begin
set @sUrl='http://localhost:6745/WebService.asmx?op=converttodaysweb'+'&day='+convert(nvarchar,@paramd)+'&month:'+convert(nvarchar,@paramm)+'&year:'+convert(nvarchar,@paramy)+''
exec sp_OACreate 'MSXML2.XMLHTTP', @obj OUT
exec sys.sp_OAMethod @obj,'Open',null,'GET',@sUrl,false
exec sys.sp_OAMethod @obj,'send',null,''
exec sys.sp_OAGetProperty @obj,'responseXML.xml',@response OUT
SELECT @response [response]
exec sys.sp_OADestroy @obj

I don't know the answer to your problem but you should NEVER use this default construction:

put a size after nvarchar - otherwise you will get the SQL Default - which can be ONE :frowning:

At the least add a PRINT @sUrl or similar, during debugging so that you can see that your string concatenation is correct.