Ok, let's start with the JAL job first. 1. Does the job have "Ignore Errors" option checked? 2. If no, do you use script-level error handling like OnErrorResumeNext or OnErrorGoTo? If either 1 or 2 are yes, turn them off and let the job run. If it produces any errors, please let us know. If both answers are "no", see the following items. 3. You said the job imports some data to Oracle, so I assume this is the place where the data isn't loaded (you didn't say where you don't get tables updated in SQL server (results of stored procedures) or in Oracle results of import or results of SQL*Plus script). If my assumption is wrong please stop reading here and provide more details. Right after the import line (or lines if you import multiple tables) insert 2 extra lines for each DatabaseImport for writing to the job log how many rows have been imported. Here is an example script DatabaseImport ( "table name", "file name", rows) Concat( rows, " rows imported from [file name] ", message) LogAddMessageEx( "INFO", @V"job_id", "@V"job_name"", message) Let the script run and see how many rows imported. If the number is zero the problem is file related (are files accessible at the time when the job is run? network access issues?), if it is not but there is no data imported then see the next step 4. Enable database tracing option (Tools/Options/ menu Log page). When then job runs on schedule (you must be present there) it will prompt for the trace file name. Simply click the OK button on the displayed message box and let it run. After completion open the Log Viewer (Tools/Log Viewer menu) and check the database trace log. Look for unusual behavior. 5. If everything looks ok check if the data is there. If it is there check one more time for other jobs, perhaps scheduled directly in your Oracle database or someplace else that run after your job and delete the imported data or do some kind of "clean up" process. : This job runs different connections and processes to different databases. I : have : played with the time it runs, and still the job will run and send a : notification : email, but no tables update. When I fire the job manually everything updates : as it should. : This job involves running SQL DTS command lines to pull data from SQL : database. Then it connects : to an Oracle database for a data import, runs a SQL Plus script, and then : disconnects. : Next step is to connect to SQL database and run 3 stored procedures. The : final step is to : run file backups. : All this is handled with JAL script. The connections hold the user login : information. We use an ODBC connection for the SQL connection using trusted : login, not : win autheticated, and we use Oracle 8.0.4 connection to connect to Oracle : using Asych and : Auto Commit options. : Like I said, the job runs perfectly when fired manually, but does not update : any records when : fired on schedule. However, no errors are returned, and the email : notification that the job : finished is sent. : We appreciate your help. Hope this information will help discover the : problem. : Thanks.
|