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

Transact-SQL
1

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

2

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:'

??

3

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.

4

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

5

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.