Calling web service from SQL Agent Job not working

Question

I have a a SQL 2000 DTS package that is scheduled to run from a SQL 2005 SQL Agent job. In this DTS there is a ActiveX step that has the following VBScript to call a webservice.

Dim http: set http = CreateObject("MSXML2.ServerXMLHTTP.6.0") 
http.setProxy 2, "http://123.45.67.89:8080" 
http.open "GET", "http://mywebservices.com/MyWebMethod?Param1=value1", false 
http.setProxyCredentials "SQLServiceAccount", ""
http.send

When running this DTS manually i can see that it runs fine and is able to call the webservice. However when running from a SQL job it doesn't call the webservice. This doesn't even throw an error, but I can tell from our logs that the webserivce wasn't called. I have tried running the DTS manually while logged in as the SQLServiceAccount and this works fine. The SQL Job owner is 'sa'

Anyone have an ideas why this doesn't work when running from a SQL Job but works OK when run manually in the DTS?

Answer 1

Hi MakkyNZ,

I am facing the same issue. I am calling the Web Service from my SSIS package using ScriptComponent. I am using the SQl Server Agent to run the package. But same on my side the job runs fine but the WebService is not hit.

If you got any solution to this problem can you send response to "[email protected]".

Answer 2

My solution to this was to call the web service from a stored procedure and just get the SQl job to run this rather than the DTS package

you can call a webservice in t-sql like this in SQL 2000

  exec sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @obj OUT
  exec sp_OAMethod @obj, 'Open', NULL, 'GET','http://mydomain.com/mywebservicesite/default.asmxL', false
  exec sp_OAMethod @obj, 'send'
  exec sp_OAGetProperty @obj, 'responseText', @response OUT
  exec sp_OADestroy @obj