2

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?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
MakkyNZ
  • 2,215
  • 5
  • 33
  • 53

1 Answers1

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
MakkyNZ
  • 2,215
  • 5
  • 33
  • 53