SQL Server Performance Forum – Threads Archive

Scheduled Jobs Failed to Run

I have a scheduled job which is set to run daily at 5 PM under the SQL Server Agent. It had been running fine since 2005 until it failed to do so 3 days ago. Upon opening up the Jobs there is a red X icon on it. Looking at the job history, I got this error log: Executed as user: SNETHQ05SYSTEM. …Start: Create Table TIMB_DAILY Step DTSRun OnError: Create Table TIMB_DAILY Step, Error = -2147217900 (80040E14) Error string: There is already an object named ‘TIMB_DAILY’ in the database. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 2714 (A9A) Error string: There is already an object named ‘TIMB_DAILY’ in the database. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Create Table TIMB_DAILY Step DTSRun OnStart: Copy Data from SNET_REPORT_TIMB_DAILY to TIMB_DAILY Step DTSRun OnProgress: Copy Data from SNET_REPORT_TIMB_DAILY to TIMB_DAILY Step; 155 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 155 DTSRun OnFinish: Copy Data from SNET_REPORT_TIMB_DAILY to TIMB_DAILY … Process Exit Code 1. The step failed. I have tried restarting the SQL Server Agent but the problem still persists. Any idea what might have gone wrong here?
The strange thing is that when I tried to manually execute the local package TIMB_DAILY, everything went smoothly. Data are all downloaded without any problem whatsoever. So I am sure that there is no problem with the package at all.

Any changes to the user account for SQLAgent service?
Check password hasn’t been changed over last week or 3 days ago.
Also enable the DTS package logging for more information onthe step error. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

There hasn’t been any changes to the SQLAgent service as well as the Database and/or tables. No changing of password whatsoever. It has been left running like that since 2006. I have package logging enabled, please find below the log message: Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:There is already an object named ‘TIMB_DAILY’ in the database.
Step Error code: 80040E14
Step Error Help File:
Step Error Help Context ID:0

So check the code and see if you are trying to create the object without dropping it or try to avoid creating within your DTS package code.
Also you can edit the DTS package and specify not to create object while the transformation. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

The job was set to execute the package at 5:05 PM. The piece of codes inside the package is as follow: CREATE TABLE [TIMB_DAILY] (
[MAWB] varchar (16) NULL,
[HAWB] varchar (16) NULL,
[COMPANYNAME] varchar (50) NOT NULL,
[ORIGIN] varchar (3) NULL,
[CITY] varchar (30) NOT NULL,
[DEST] varchar (3) NULL,
[CWT] decimal (15,2) NOT NULL,
[CTNS] decimal (10,2) NULL,
[VOL] decimal (28,2) NULL,
[POID] varchar (4000) NULL,
[ETD] datetime NULL,
[ETA] datetime NULL,
[CLOSINGDATE] datetime NULL
) However, there is another package which is set to run at 3:00 AM to drop the table. The code is as below: drop table timb_daily;
Looking at this, there shouldn’t be a problem executing the job right?

What if that job is not working properly? Why not include the piece of code before this 0500 job: <pre id="code">if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TIMB_DAILY]’) drop table [dbo].[TIMB_DAILY] GO …………</pre id="code"> Better to cut dependancies between the jobs and control the process with 1piece of code [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Also if there are any users connected to this table during 0300 that job might have been failing as the object is in use. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

]]>