| Author |
Topic |
|
marsulein
Starting Member
11 Posts |
 Posted - 2007-06-06 : 22:35:53
|
| 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: SNETHQ05\SYSTEM. ...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. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-07 : 00:46:12
|
is there a table called TIMB_DAILY in your database? the step is failing because it's trying to create such a table but one already exists by that name.In general it's not a good idea to be creating and destroying objects at runtime (other than temp tables of course)
elsasoft.org |
 |
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-07 : 03:38:35
|
| Yeah, the table TIMB_DAILY is there in the database. Problem is, it has been running fine that way since 2005. Wonder what went wrong all of a sudden. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-08 : 01:30:01
|
| Enable package logging so you can get details. |
|
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-08 : 01:37:12
|
| I have enabled package logging but how do I retrieve the information? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-08 : 01:48:51
|
| Right click on the packge and view log. |
|
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-08 : 01:55:38
|
| The error log in fact is very simple. Just like what jezemine said, there is already a table named TIMB_DAILYStep Error Source: Microsoft OLE DB Provider for SQL ServerStep Error Description:There is already an object named 'TIMB_DAILY' in the database.Step Error code: 80040E14Step Error Help File:Step Error Help Context ID:0The thing is why was it running smoothly previously. There is no changes done to the schedule nor the package in any way. |
|
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-08 : 05:15:31
|
| I found out that even though the job failed to run, data are being imported into the table TIMB_DAILY. As a result, there are duplicated data. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-08 : 13:29:49
|
| Did you specify create table in transformation page? Switch to append rows. |
|
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-10 : 21:05:19
|
| Yup. Following is the SQL command specified in the package: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) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-10 : 23:15:05
|
| You may take out that step. |
|
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-11 : 02:37:51
|
| Instead of removing the table creation step, is there any way I can do it in such a way that it only creates table when the TIMB_DAILY table does not exist? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-11 : 18:17:02
|
| Then you need to check with 'if exists ...' before creating table in the step. |
|
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-11 : 22:27:59
|
| I went to the DTS -> Local Packages -> TIMB_DAILY and do a right click and select "Schedule Package". On the top there is an auto generated field called "Job Name" and it is showing TIMB_DAILY 3. I suspect that there is a TIMB_DAILY 2 running in the background (scheduled previously). However, how do I view all the scheduled jobs? I can't seem to find them. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-12 : 12:15:09
|
| Check that in management -> sql server agent -> jobs. |
|
|
|
marsulein
Starting Member
11 Posts |
Posted - 2007-06-12 : 21:58:24
|
| I didnt know that when you do:"DTS -> Local Packages -> TIMB_DAILY and do a right click and select Schedule Package"You will get to see it in the"management -> sql server agent -> jobs"I'll try to schedule a new job doing the same thing and see how it goes. Thanks for now rmiao. |
|
|
|
|
Schedule Jobs Failed to Run