May 10, 2002 at 1:10 pm
Nelson,
My team is having the same issue as you.
We have DTS packages scheduled through Agent. They will run through EM or dtsrun.exe but will not run when scheduled with Agent.
We get the same error as you. If we view job history, we get additional details about the problem saying " The process could not be created for STep 1 of job Reason: The system could not find the environment option that was entered."
This problem actually started when we applied service pack 2. The packages ran through Agent fine with no service pack and with service pack 1.
Do you have service pack 2?
We also have SQL 2000 and NT 4.0 SP 6
May 13, 2002 at 2:05 am
This is most likely the effect of a well-known issue in SQL Server 2000 Editions.
There is a fix for this is SP1 and upwards.
Install the latest SP and it should work fine, i've had the exact same problem myself..
Regards,
Anders / Sweden
May 13, 2002 at 3:28 pm
Hi,,
I am getting similar error message.. Works fine with manual run but job fails.
I have SQL7.0 SP2 in server as well as my workstation. I read about article that I can find in this discussion but didnt help.
So I am thinking, if I know what does system account have permission on local machine, I can duplicate these permissions in local machine to ruin this package..
Any idea..
I know three permissions.
1. Logon as a service
2. Logon as locally
3. Act as part of the operating system.
Even though I put domain user acct into local admin group, job still fails..
Thx in advance..
Jay
May 14, 2002 at 12:45 pm
Just a few questions to help understand the problem:
Can you provide more information on what the DTS is doing, such as what is the source (file, SQL Server, etc.), what is the destination (file, SQL Server, etc.), are you using a standard data transformation or a SQL Server object copy?
When you scheduled the job, did you create your own job manually, or did you right-click on the DTS package in Enterprise Manager and select 'Schedule...' from the popup menu?
James C Loesch
Edited by - jloesch on 05/14/2002 12:46:37 PM
Edited by - jloesch on 05/14/2002 12:47:41 PM
Edited by - jloesch on 05/14/2002 12:48:46 PM
James C Loesch
May 14, 2002 at 12:55 pm
OK..
1. Source is SQL Server which is SQL7.0 SP2.
Destination is also SQL server 7.0 SP2.
I created Tb in destination and insert data into that tbl.
2. I used right-click in EM to create schedule..
Hope this will help..
Thx
Jay
May 14, 2002 at 1:10 pm
Well, we solved our problem. We can't believe it was something this simple, but the agent starting running the DTS jobs fine after we rebooted after applying SQL Server 2000 Service Pack 2!!!
May 14, 2002 at 1:52 pm
Another question:
What exactly is the error message?
You may need to look at the step details in the job history to get the error information. If that doesn't help, checking the package log might help, if you have logging for that package turned on. If you turn it on, make sure you know which SQL Server the log is saved to. I usually specify the server by name, so I know for sure, instead of going with the default of local. If there is a log entry, then the package failed. If there is no log entry for the time that it was supposed to run, then the job wasn't able to access the package for some reason.
James C Loesch
James C Loesch
May 14, 2002 at 2:12 pm
Hi, James
Thx for advise.. Actually history error didnt help to do troubleshooting...So I set up the schedule again and I got error message saying that This job is invoked by MYID. Last step was run is 1...
That doesnt help, does it?
Hmnnn...
Jay
May 14, 2002 at 2:19 pm
That's not really an error message. Did you check the box at the top of the job history dialog labelled 'Show Step Details'? You will then get two lines for each run of the job if the job has only one step. The first one will be for the entire job run, and the second will be for the step. The information for the step should provide more details.
Do you have logging turned on for the package?
James C Loesch
James C Loesch
May 15, 2002 at 8:52 am
Hi, James
Here is an error message that I got from 'step detail'
What do U think..
Looks like package doesnt make connection..
Jay
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Delete from Table [wwwapps].[dbo].[TblEmployeeData] Step DTSRun OnError: Delete from Table [wwwapps].[dbo].[TblEmployeeData] Step, Error = -2147217887 (80040E21) Error string: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Delete from Table [wwwapps].[dbo].[TblEmployeeData] Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.
May 15, 2002 at 11:36 am
It looks like the package ran, but got an error. To find out what exactly the error is, you'll want to turn on Package Logging. If you open the package in DTS Designer, and then bring up the package properties; next to the Global Variables tab you'll find a tab for Logging. There you'll find the options for logging the package execution.
Once again, I recommend specifying the name for the SQL Server to save the log to. If you select local, it will save it to the machine that the package is run on, which can change. I also recommend specifying the server that you save the package to. That will make it easier to find the log.
You will also want to periodically clear out the logs, which you can do through the same dialog that you use to view them. I intend to find a way to automate the removal of log entries that are of a certain age. I just haven't had the time to do so yet.
After you turn on logging, make sure you save the package. Then, after it has been executed, you can right-click on package in Enterprise Manager, and select package logs. If you selected to save the logs to the same server as the package, you should see a dialog listing the logs for that package, grouped by package version. Double click on the log entry that you want to examine, and you will see a list of all steps that executed. If a step failed, it will have a red x next to it. Double-clicking on that step will bring up a message box with the error information displayed.
In general, I have found the package logs to be the most useful in troubleshooting DTS packages. The job history in the agent jobs doesn't give you much information that isn't specific to the job itself.
For others reading this thinking that this should be done in private, I should have stated earlier that I chose to continue on here in order to benefit as many people as possible. Most of my questions can be used to diagnose many other problems.
Jay, hopefully using the logs will give you more information. NelsonVere, following through the steps that we have so far should also help you narrow your search down. I hope all this helps.
James C Loesch
James C Loesch
May 21, 2002 at 3:56 am
I have encountered problem before with Oracle connections. In Enterprise Manager it pick up the right driver etc. Enterprise Manager has the hidden power to pick up the right one while if you run it as a job, it looks up the driver in cache or something. I restarted the server and it works.
The University of Auckland
The University of Auckland
May 27, 2002 at 6:42 am
Well, I'm still having the same situation, so I decided to run the jobs using Windows' Scheduled Tasks. I use dtsrun with the standard name of the package and now they all are working fine. I'm running SQL 2000 SP2.
May 28, 2002 at 10:03 am
I basically have the same issue with one of my packages. It runs fine in EM but never completes as a job, scheduled from this package (not fails though).
So, I cannot see a log file because it never ends (stays in executing mode for hours).
I'm running SQL7, sp3. Package is created on the same server as a job. Job scheduled from the package using schedule package option.
It looks for me as a part of the same problem.
Any ideas?
Sincerely,
Alex
May 28, 2002 at 11:52 am
If you have package logging turned on for a DTS Package, then anytime a step in the package executes, there will be an entry added to the log. I've used this feature to watch the progress of a running package. Unfortunatly, the names given to the steps are those generated by the DTS Designer. I've found a way to change the names, but it would take a bit of explanation, which I can provide if someone's really interested.
To be honest, I'm using SQL 2000; but I'm pretty sure the logging works the same in SQL 7.
James C Loesch
James C Loesch
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply