从 SQL 代理作业调用 Web 服务不起作用

发布于 2024-09-14 19:21:48 字数 597 浏览 8 评论 0原文

我有一个 SQL 2000 DTS 包，计划从 SQL 2005 SQL 代理作业运行。在此 DTS 中，有一个 ActiveX 步骤，其中包含以下 VBScript 来调用 Web 服务。

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

手动运行此 DTS 时，我可以看到它运行良好并且能够调用 Web 服务。但是，当从 SQL 作业运行时，它不会调用 Web 服务。这甚至不会引发错误，但我可以从日志中看出 webservice 没有被调用。我尝试在以 SQLServiceAccount 身份登录时手动运行 DTS，效果很好。 SQL 作业所有者是“sa”

任何人都知道为什么这在从 SQL 作业运行时不起作用但在 DTS 中手动运行时却工作正常？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情绪 2024-09-21 19:21:48

我的解决方案是从存储过程调用Web服务，然后让SQl作业来运行它，而不是DTS包，

您可以像在SQL 2000中那样在t-sql中调用Web服务

  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

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

回复收藏 0 原文

~没有更多了~