0

I an a bit amateur and I need to have my webservice called in sp so that I can give it an input right in sp and obtain the output but my sql code makes an error :sp_OACreate has not yet been called successfully for this command batch. what is the solution? could anybody tell me simply? I use sqlserver2012 and my sp is like:

create procedure [dbo].[test2]
@paramd int,
@paramm int,
@paramy int
as
begin
  declare  @obj int
  declare @sUrl nvarchar(max)
  declare @response varchar(8000)
  declare @xml XML
  set @sUrl= 'http://localhost:31876/myage/WebService.asmx?op=converttodaysweb?day:'+convert(nvarchar,@paramd)+'month:'+convert(nvarchar,@paramm)+'year:'+convert(nvarchar,@paramy)
  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
n.e
  • 1
  • 1
  • Don't do this in a stored procedure. Look at SSIS it has the ability to call SOAP services etc. You are opening yourself to problems here. If there is a networking issue it will seriously affect performance. Also they are a security risk https://stackoverflow.com/questions/2698292/usage-of-sp-oacreate-sp-oamethod-etc-is-a-security-risk – Namphibian Nov 07 '17 at 20:47
  • thanks for suggestion, but since i'm not an expert and really need this project to be done, i prefer having it in this way, could you help me with this? – n.e Nov 08 '17 at 19:49

0 Answers0