Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rpallas
Starting Member
5 Posts |
 Posted - 2006-08-04 : 11:38:04
|
| Hi, I have quite an odd issue with a DTS package. It runs fine if i run it from Enterprise Manager manually but when i schedule it to run overnight, it fails. However... as far as i can tell, it doesn't really fail at all, it just says that it failed (in EM).The script is simple:****************************************************************Function Main() Dim oXmlHttp Set oXmlHttp = CreateObject("MSXML2.ServerXMLHTTP.3.0") oXmlHttp.open "POST", "http://path_to_a_web_service.asmx/web_service", False oXmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" oXmlHttp.send Set oXmlHttp = Nothing Main = DTSTaskExecResult_SuccessEnd Function****************************************************************Effectively all it does is execute a web service...once every night. The reason i know that it is not actually failing is because that web service is getting called and the code contained within it is executing correctly.The error message from SQL Server Agent reads as follows:****************************************************************Executed as user: SERVERNAME\SYSTEM. ...tart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 0 Error Source= msxml3.dll Error Description: The operation timed out Error on Line 9 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= msxml3.dll Error Description: The operation timed out Error on Line 9 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: The operation timed out Error source: msxml3.dll Help file: Help context: 0 DTSRun OnFinish: DTSStep_D... Process Exit Code 1. The step failed.****************************************************************My problem is that we intended to use MSSQL Server in order to notify us if there was any problems with executing the code...however...i can't do this if it always thinks that the package is failing.Does anyone know what might be causing this anomaly?Kind RegardsRobbie-Rob- |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-04 : 11:56:55
|
| What happens if you log on to the server using the sql server service login and run the package in the designer?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rpallas
Starting Member
5 Posts |
Posted - 2006-08-07 : 09:27:01
|
| SOrry for delay, been away for the weekend. MSSQLServer is set to log on as a local system account for this server and i am unable to log onto the machine like that. I have an admin account use to log onto the machine?-Rob- |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-07 : 09:57:36
|
| Probablt because the package is run as the local system account which doesn't have permission outside the machine.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rpallas
Starting Member
5 Posts |
Posted - 2006-08-07 : 10:16:07
|
| Yeah, i thought that originally, but i've been testing it this morning, and i've worked out that it only fails sometimes when its scheduled. For example, i scheduled it to run in 5 minutes time and it ran ok with no errors, then i did it again 5 minutes ahead and it failed. I'm trying to find a pattern of some sort that will explain why sometimes it fails and sometimes it succeeds. But like i say, the code is always executed..regardless of the scheduled job failing or succeding. Which to me indicates that it does have access to everything it needs access to.Also, given that the script only posts a httpRequest, it shouldn't really need any access to anything else...right?-Rob- |
|
|
|
|
|
|
|
|
DTS with ActiveX Script Fails when scheduled