August 29, 2002 at 5:43 am
I have two jobs running on separate servers that complete with no problems about 80% of the time. The other 20% of the time I get a failed message (from the job history) that is similar to the one below. It gives no idication as to what the problem really is.
There are 3 steps to the job:
1) Pushes data from SQL Server 2k into text files (Always succeeds).
2) Updates data to an access database (always succeeds).
3 Deletes, updates, and inserts into tables on 2 diferent .mdb's and this is the one that typically gives me this failed message in job history
Message:
(Executed as user: SECSOURCE\admin_. ...tart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_5 DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1127 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1127 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_... Process Exit Code 4. The step failed.)
Does anyone have any idea what this is trying to tell me and why it happens? If you have questions please send them and I will do my best to answer them.
Thanks in advance.
August 29, 2002 at 7:20 am
What happens when you run the DTS Package manually after such a job failure?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 29, 2002 at 7:31 am
A couple of things here.
1) Open your DTS package and open each item and transformation in the package. They all have a description item in them that you can set to something that makes sense but also is unique to help pinpoint issues.
2) On the tool bar click Package/Properties. The new dialog that comes up will have a loggin tag (you did say SQL 2000), setup an error file or setup logging to output more data.
These should help you figure out which step is the issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 29, 2002 at 8:52 am
quote:
What happens when you run the DTS Package manually after such a job failure?K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
August 29, 2002 at 3:10 pm
quote:
When run manually it succeeds.What happens when you run the DTS Package manually after such a job failure?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
[/quote]
September 11, 2002 at 7:04 am
O.K. I had the package log and here are the errors of have encountered. Any advice on how to handle these would be greatly appreciated:
ERROR 1
Step 'DTSStep_DTSExecuteSQLTask_7' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Could not save; currently locked by another user.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003046
Error 2
Step 'DTSStep_DTSDataPumpTask_3' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified.
(Microsoft JET Database Engine (80004005): Could not save; currently locked by another user.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0
Thanks for the help.
quote:
A couple of things here.1) Open your DTS package and open each item and transformation in the package. They all have a description item in them that you can set to something that makes sense but also is unique to help pinpoint issues.
2) On the tool bar click Package/Properties. The new dialog that comes up will have a loggin tag (you did say SQL 2000), setup an error file or setup logging to output more data.
These should help you figure out which step is the issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 11, 2002 at 8:21 am
This looks to be the problem:
quote:
Step Error Description:Could not save; currently locked by another user.
You said you are updating Access, right? If someone's using the .MDB and has the row(s) locked you want to modify, you'll get this error, and not just through DTS. Are you sharing access to the.MDB file (non-exclusive)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 11, 2002 at 9:02 am
Yes, there are a lot of people utilizing the .mdb, however this package runs at a time in which no one should be logged in. Is there a way, utilizing sql server, to check first to see if anyone is still logged into that .mdb and then killing their login prior to running the package?
Thanks
quote:
This looks to be the problem:quote:
Step Error Description:Could not save; currently locked by another user.You said you are updating Access, right? If someone's using the .MDB and has the row(s) locked you want to modify, you'll get this error, and not just through DTS. Are you sharing access to the.MDB file (non-exclusive)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply