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 |
|
jubilanttiger
Starting Member
19 Posts |
 Posted - 2006-02-05 : 17:43:12
|
| Hi There,I have around 10 DTS jobs which run every night. These jobs basically copy data from views of a source DB to tables in a target DB. These jobs have been failing quite frequently in the recent past. I have checked and there are no contraint violations (like primary keys) in the target DB. Most of the times I just start the job again and it runs successfully.The DTS job history does not give me any details and it says it exits by code -1. I read online and have enabled SQL logging for the DTS jobs today.I want some suggestions if there are any best practices while creating these jobs. And can you suggest what are the common reasons why DTS jobs fail?Thanks a lot for your reply! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-05 : 17:57:57
|
| Why DTS?For something like this I would either do a select on a linked server or more likely bcp the data out then bcp it in. Means you can do the extract and insert at different times and also repeat the insert without needing the source system.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jubilanttiger
Starting Member
19 Posts |
Posted - 2006-02-06 : 00:25:13
|
| Nr thanks for your reply!I dont think that a bcp is suitable since i am not copying the tables from the source DB as is, but I am creating views by applying business logic on the source tables and creating denormalized views. The data from the views is then copied over to the destination DB. If even then you think that bcp is suitable can you elaborate on your idea and the reasons you would choose bcp and how you would go about implementing it!Thanks a lot for your reply! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-06 : 00:28:55
|
| You can bcp from views or to give even more flexibility from stored procedures.The reason is that bcp is simpler and for the reasons I gave above.You probably wouldn't find the problems you are having now - or if you did it would be obvious where the problem lies.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
DTS job failure !!! - Plz help