SQL Server Performance Forum – Threads Archive

CreateProcessTask failing

I have a step (call it RunEXE) in a package (call it PackageA). RunEXE executes another Package (call it PackageB) passing two global variables. PackageB outputs some data to a text file then, using the CreateProcessTask, calls an executable on another server within the LAN. The path to the executable is specified using UNC. The data output is to the same location on the other server. Now can this can be working fine one moment and then the next (quite often the next day) it fails stating that the executable has returned a result code of 1 rather than 0. When this happens the executable isn’t performing any function (i.e. the first line of code in the exe creates a text file and that file doesn’t get created – if an error occurs creating the file then executable returns a result code of 2 not 1). If I rename the executable I get the expected error of "file not found". The only way I’ve found to get it working again is to open PackageB, double click on the CreateProcessTask, browse the executable, click OK and save the Package. This one is driving me mad.

Any error or information on DTS package log? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Step Error Source: Microsoft Data Transformation Services (DTS) Package Step Error Description:CreateProcessTask ‘Call Executable’: Process returned code 1, which does not match the specified SuccessReturnCode of 0. Step Error code: 80040496 Step Error Help File<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />qldts80.hlp Step Error Help Context ID:4900

I think the path for EXE file is same or not?
Try running the job directly from the server in the same manner. If you get the same error it is to do with the pathing to the EXE file. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

The path is definitely correct. It must be as when I delete the file the error changes to say "file not found". I put the file back and I the error changes back.

Are you running for the server or calling package from client? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Either running from client or starting from a job on the server. Both fail in the same fashion.

Last but not least, who is the package & job owner and its privileges on both the servers. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

a user (not using windows authentication) with sysadmin rights.

ANy possibility of revamping the package and re-initialize variables involved, as a test. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Certainly. If there’s any tests I can do that might help I’m more than happy to do them.

Good!, BTW what is the level of SQL, OS, SP & MDAC on both the machines? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

SQL 2000 SP3. Windows 2000 SP3. Mdac 2.7. The problem has happened again this morning so I’ve been able to do some additional tests. I’ve tested it running the package on the server and that fails. I’ve also tested running PackageB directly rather than from RunEXE in PackageA and that fails in the same fashion. To reiterate PackageB does this. 1) Modify parameters of steps using activex & global variables. 2) Data pump from local sql to text file on the other server within the LAN. 3) Call exe I’ve tested it my removing step 1) and that has no affect. I execute step 3 directly and it fails. Step 2 works fine. That suggests to me that it’s probably something to do with the network here. I think I’ll setup a test where the exe exists on the sql server rather than another server and see if I get the same problem. Unfortunately that could take a while as it’s intermittant. Oh well <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

THats good, things are getting heated up….
From the code point of view in ActiveX script, I recommend to use OPTION EXPLICIT which helps to trap any syntax or declaration errors as swapping of variables is seems to be involved (a thought).
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

If thought things made sense before then you were wrong <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I’ve move the exe to the sql server. (sql and agent are running under the same domain user account [call it Brian]). I’ve shared the folder and given Brian full control. If I logon to my PC as Brian I can execute the DTS fine with no problems. If I start the sql job which runs the DTS then it fails. Brian does not have a sql login but is a member of builtinadministrators which has sysadmin rights (i’ve tested this by creating a sql login from Brian and putting it directly into sysadmins just in case but that of course made no difference) When I run the DTS through a job profiler shows that it’s using the correct account of Brian execute the DTS. I changed the exe for an empty bat file and get the error of "General error -2147024703 (800700C1)" I’ve tried running the DTS using DTSRun from my client (dos prompt) and that fails in the same way. It just doesn’t make any sense.

Problem solved [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] When an executable is called in the fashion I was calling it (i.e. job scheduling a DTS with a step in it) the working directory is not set to the location of the executable. I’m not sure exactly where it is set to (and to be honest I don’t really care at this point) but this particular executable failed as it was attempting to read/write fails referentially to its working directory. Changed the exe to caculate its working directory properly (added this line "BOOL SetCurrentDirectory( LPCTSTR lpPathName <> // new directory name );") and it’s working.

]]>