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 |
|
steve_0710
Starting Member
3 Posts |
 Posted - 2004-04-06 : 11:55:10
|
| I'll start off with...I think my question is actually multiple questions. We have a DTS that copies data from an AS/400 to SQL Server 7.0 on a nightly basis. Works like a charm. I need to add some other processes to the DTS workflow and I am not sure what the best way to do this is. After the data is copied, I want to detach the database, delete the log file, reattach the database then run a WIN32 .exe. The log is growing very big and is not needed for data recovery.I have been playing around with "Execute Process Task", "ActiveX Script Task" and "Execute SQl Task", but have not been able to get this process to work. Any thoughts would be appreciated. This is the first complex DTS that I have created so please be specific.Thanks,Steve |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-06 : 12:33:32
|
| I would not use DTS for this. I would use it for the AS/400 part, but not for the rest that you described.If you don't need the transaction log for data recovery, then why not change your database to truncate log on checkpoint?Tara |
 |
|
|
steve_0710
Starting Member
3 Posts |
Posted - 2004-04-06 : 16:55:57
|
| Tara,I'm not sure how to truncate the log on checkpoint, but I will look into it.As far as running the .exe, I need to know that the data has been imported from the AS/400 before I can run the .exe. How can I accomplish that if not within the same workflow?Thanks,Steve |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-06 : 17:11:58
|
| Right click on your database in Enterprise Manager, go to properties. Go to the options tab. I think in SQL 7.0, there is a checkbox for truncate log on checkpoint. Check the box. You will lose the ability to restore to a point in time though but it doesn't sound like you need this anyway.Tara |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-06 : 18:50:36
|
| Tara, what is wrong with DTS for this ? Sounds perfect for the job to me.Steve, as for the other things, sounds like you are on the right track. Use the Execute Process task to run your EXE. You could use execute sql task to detach your database but I agree with Tara that this isn't the best approach.Maybe if you can be more specific about what isn't working we can give a more specific answer.Damian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-06 : 18:54:53
|
| This part:"After the data is copied, I want to detach the database, delete the log file, reattach the database"should be fixed by modifying the database option since data recovery is not a requirement. These steps just aren't needed and are just going to slow the process down. Truncate log on checkpoint should be set on this database.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-06 : 19:18:49
|
| It's also provides less points of failure. I don't like detaching and reattaching things if I can keep from it. Processes become more susceptible to failure with more points of movement. Tara's solution solves it with one shot.Also, make sure your database recovery mode is set to simple if you don't need the tlog for recovery.One other point, you would still be using DTS for the overall process.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
DTS Workflow