Delete error statement getting again n again...
-
Hello friends
..pls help me out..I have one job continuously scheduled to run..but what is happening last two or three weeks ..it is failing and giving this error..
DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217873 (80040E2F) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217873 (80040E2F); Provider Error: 3621 (E25) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217873 (80040E2F); Provider Error: 547 (223) Error string: DELETE statement conflicted with TABLE REFERENCE constraint 'FK_employeeHeader_storeemp'. The conflict occurred in database 'Emp-PORTAL', ta... Process Exit Code 1. The step failed.
but getting this error if I run the same job after say half hrs then it works fine no error nothing..some times it runs successfully for continues two or three days but next day..the same thing happens and after certain time when we run manually it works fine......
pls help me out whats wrong with this is any bug in DTS..we have Sql server 2000 with SP3....
Regards,
Papillon
-
This is not a DTS issue, it's aproblem with the DELETE statement trying to delete records that are still referenced by foreign keys in other tables.
You either need to implement cascading delete, or you need to only run the DELETE after having 1st checked that no related records exist.
-
I cant do cascading delete but how that is successful after second run of this?????
Executed as user: EMPSQLSRVR\SYSTEM. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process Exit Code 0. The step succeeded.
it runs fine now...
...see just before posting this was failed but now it worked fine..
Regards,
Papillon
-
It runs sometimes and not others because it is data dependant.
>>DELETE statement conflicted with TABLE REFERENCE constraint 'FK_employeeHeader_storeemp'
Depending on what table is at the foreign key side of FK_employeeHeader_storeemp and depending on what data is in that table at a given point in time, your DELETE may either succeed or fail.
Without the tables and their DDL, but assuming you have tables named "Employee" and "Store":
-- This can fail due to referential integrity
DELETE
FROM Employee
WHERE EmployeeKey = SomeValue
-- This won't fail due to referential integrity
DELETE e
FROM Employee As e
WHERE e.EmployeeKey = SomeValue
AND NOT EXISTS (
SELECT *
FROM Store As S
WHERE S.EmployeeKey = E.EmployeeKey
)
-
Hey..PW
i will explain whole scenario..i have emp table and store table data here PK is EmpID from Emp..Data is coming from other source in table Stg_Store..and moved in Store table but only those records are moved forward which having EmpID in Emp table..ok...after inserting successfully in store table i am setting flag (set it to 1) in store table...and i am deleting records from store table whose flag is 1..means there empid present in both emp and store..ok
then how that failed in certain interval of time???????
Regards,
Papillon
-
You haven't posted any table DDL or constraint definitions, nor the DELETE statement itself, so it's tough to say ..
>>and i am deleting records from store table whose flag is 1
So you're deletign from a table named Store ? What tables are dependant on Store ? Does Store have a Delete trigger defined and what does that trigger do ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply