Web service call inside sql server

Question

I have a web service with the following methods (C #):

[WebMethod] public string HelloWorld1(string a) { return "Hello World - " + a.ToString(); } [WebMethod] public string HelloWorld2() { return "Hello World"; } 

I am trying to use it in a sql-server procedure with the following code:

 ... -- URL 1 set @url = 'http://localhost/ws/ws1.asmx/HelloWorld2' -- URL 2 --set @url = 'http://localhost/ws/ws1.asmx/HelloWorld1?a=amama' EXEC msdb.dbo.sp_OACreate 'MSXML2.XMLHTTP', @OBJ OUT EXEC msdb.dbo.sp_OAMethod @OBJ, 'Open', NULL, 'post', @URL , false EXEC msdb.dbo.sp_OAMethod @OBJ, 'send' EXEC msdb.dbo.sp_OAGetProperty @OBJ, 'responseText', @RESPONSE OUT SELECT @RESPONSE [response] EXEC msdb.dbo.sp_OADestroy @OBJ 

When I use the first URL 1, I get the desired response. But when I use URL 2, the following error is displayed:

  System.InvalidOperationException: Request format is invalid . em System.Web.Services.Protocols.HttpServerProtocol.ReadParameters() em System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest() 

Could you tell me what is wrong?

Answer 1

When using sp_OAMethod use the GET method, and as an improvement, you can try to parameterize the stored procedure as follows:

 CREATE PROCEDURE [dbo].[sp_CallWebService] @Param varchar(20) = NULL AS DECLARE @obj INT DECLARE @sUrl VARCHAR(200) DECLARE @response VARCHAR(8000) SET @sUrl = 'http://localhost/ws/ws1.asmx/HelloWorld1?a='+ @Param +'' EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl , false EXEC sp_OAMethod @obj, 'send' EXEC sp_OAGetProperty @obj, 'responseText', @response OUT SELECT @response [response] EXEC sp_OADestroy @obj RETURN 

Then you can execute the stored procedure with the argument provided:

 EXEC [dbo].[sp_CallWebService] 'amana'