Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
solaris
Starting Member
4 Posts |
 Posted - 2003-07-22 : 04:28:21
|
| Hi everyone,New to SQL Server and with a datawarehouse project starting next week, i'm getting some practice with DTS, scheduling, job creation... the works.I'm running XP Professional with SQL Server 2000 Service PAck 3 and i'm currently having some trouble with a scheduled DTS Package, as it most of the times succeeds and sometimes it fails.This failure usually (but not always) occurs when i'm inserting records into a table. I have a SQL Task that deletes records using 'DELETE FROM DIM_PRODUCT_TYPE' and, upon success, proceeds to the insert fase. Sometimes i get a successful result and sometimes i don't. The error message is as follows:Executed as user: xxxx\xxxxxx. ... OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error string: Error at Destination for Row number 8. Errors encountered so far in this task: 1. Error source: DTS Data Pump Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); 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: -2147467259 (80004005); Provider Error: 2627 (A43) Error string: Violation of PRIMARY KEY constraint 'PK_DIM_TIPO_PRODUTO'. Cannot insert duplicate key in object 'DIM_TIPO_PRODUTO'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnProgress: DTSStep_DTSDataPumpTask_2; 8... Process Exit Code 1. The step failed.From my point of view, its as if the table wasn't deleted in the first place.What really bothers me is the chaotic nature of this, as it can fail in another step of the process, and it can go all the way to the end, as it does most of the times.Any help on this subject would be greatly appreciated.Thanks! |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-22 : 04:49:28
|
| Okay a few things..Why are you deleting the records like this?Do you need to delete *all* the records in the table? If so useTRUNCATE TABLE yourtableIt will be much faster thanDELETE FROM yourtableSecondly, your error is because it is inserting a duplicate record. Where is the data being imported from? Do you know that it is all unique according to your keys?-------Moo. :) |
 |
|
|
solaris
Starting Member
4 Posts |
Posted - 2003-07-22 : 05:04:52
|
| If i'm not mistaken, i can't use truncate table due to FK/RI constraints.The data is unique, if it wasn't, then the DTS would *always* fail, and not just now and then.The step works like this:[DELETE FROM DIM_PRODUCT_TYPE] ----if success----> [INSERT RECORDS INTO DIM_PRODUCT_TYPE]So, given the error i get, it would mean that the deletion of the table wasn't successful, and so the job should abort *before* attempting to insert records into the table.Also, for me it is not a question of needing/not needing to delete all table records. Its a question of reliability.Edited by - solaris on 07/22/2003 05:10:30Edited by - solaris on 07/22/2003 05:14:56 |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-22 : 05:33:53
|
| Are triggers being used here to update another table?If so, does that other table end up with duplicates sometimes and the error messages relate to that table?-------Moo. :) |
|
|
|
solaris
Starting Member
4 Posts |
Posted - 2003-07-22 : 06:14:54
|
No triggers at all.Just a simple [DELETE RECORDS FROM TABLE THEN IF DELETE SUCCEDDS INSERT RECORDS INTO TABLE] DTS process.The owner of the job is a domain user, and lately the network seems to be a little unstable. Could an error derive from this, as unlikely as it seems? The error should be different, i know...  |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-22 : 06:35:45
|
| I would consider that unlikely. When our network goes wrong, it almost always results in a "general network error" message.What is confusing me here - and the reason I mentioned another table - is this. You say it does DELETE FROM DIM_PRODUCT_TYPEBut, your data is being inserted into [Cannot insert duplicate key in object] 'DIM_TIPO_PRODUTO'. -------Moo. :) |
|
|
|
solaris
Starting Member
4 Posts |
Posted - 2003-07-22 : 07:09:52
|
| DIM_TIPO_PRODUTO is Portuguese for DIM_PRODUCT_TYPE, i just translated so people would know it was a simple product dimension table.Sorry if i mislead you.Ok, lets call the troublesome schedule schedule 1.Meanwhile, i have another DTS schedule, lets call it schedule 2.Schedule 2 comprises a single DTS composed of several Execute Package Tasks.Both schedule 1 and schedule 2 execute the same packages, the difference being that schedule 1 is composed of 8 steps, each calling a DTS, and schedule 2 is composed of a single step, a package that calls the other packages.While troublesome schedule 1 occasionally fails, schedule 2 has always succeeded.Is there an advised limit number for steps? How may steps can a schedule have? I checked the steps order and everything is as pretended. |
|
|
|
|
|
|
|
|
Scheduled DTS job failing (now and then!)