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 |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2002-05-15 : 15:48:11
|
| I am at the end of my rope (considering an early happy hour appearance).I am creating a downloadable Access Database that pumps certain data out of my Sql Server for some clients. I have a DTS Process. 1. The first task is a script. It moves an empty "structured" Access database to a specific folder. 2. On Completion, several inserts are run from the sql server to the access db. The steps are all processed in workflow order (one at a time, but their order has no significance). 3. On the completion of the final step, I have another script task that copies this newly "filled" db to a folder where they have download access. The issue is this. The 3rd step occurs, but the download access folder has a copy of the "Pre-filled" database (empty). If i put a msgbox call in the active process (anywhere in the script), the filled version of the database is moved.How can I make this work without a msgbox call (or better yet, what is going on?) Its almost like its not committing or something until after the process is complete.Thanks for anything, this has been a long battle._________________________Death is one day closer |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-15 : 16:01:04
|
| create a jobremove the 'move file' step from dtsjob step one, call dtsjob step two, move fileIt sounds like you need to work around some implicit transaction . . . it's wierd that a msgbox will commit the trans. Will and empty ActiveX script task commit it too?Hope one of these two helps . . .<O> |
 |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2002-05-15 : 16:40:04
|
I separated the dts for processingand the "moving" dts. I used an active task to call the "Move" DTS.No dice. I put a msgbox call just before the package execute (in the main process dts) and it worked like its supposed to, but no msgbox, no correct process. (ugh).Any other ideas?quote:
create a jobremove the 'move file' step from dtsjob step one, call dtsjob step two, move fileIt sounds like you need to work around some implicit transaction . . . it's wierd that a msgbox will commit the trans. Will and empty ActiveX script task commit it too?Hope one of these two helps . . .<O>
_________________________Beer is healthy, I read it on the internet. It must be true! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 16:57:26
|
| This could be a file/path permissions issue, but I can't guarantee it.There's one way to test and be sure: modify the step to "move" the Access file to the work folder, to an explicit copy and DELETE file (if it's not already being done that way) It's probably a good idea to do these as two completely separate steps too, with an On Success before, between, and after them. I'll betcha the file won't be deleted, and with luck it will fail the step and you can spot it right away.I'd also recommend against using the FileSystemObject (if you're using it) for this part; try using DOS commands, either directly or through xp_cmdshell. It at least gives you another avenue in case FileSystemObject still doesn't behave; it will either work through DOS or eliminate file permissions as the culprit.Try this both as a separate job step, and an a DTS step within the package. |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2002-05-15 : 17:48:44
|
Hmm.Here is what I did.I created 4 batch files (all in one DTS Package).1. deletes .mdb in "filling area" (works)On Completion2. moves empty .mdb to "filling area" (works)3. on success-> sql processes run (works)4. completion of final sql process5. batch deletes "download" .mdb (works)6. on completion7. batch moves from filling area to download area. (broken)I have deleted the access db in various spots and run the parts of the dts, it seems like the only problem I am having is that the final copy isn't getting the "committed" form of the access db, its getting the "empty" version, but if I put in an msgbox call, I get the "filled version". Is there any other options? (I run the batch files through the execute process task)Sql Server 7 sp4.quote:
This could be a file/path permissions issue, but I can't guarantee it.There's one way to test and be sure: modify the step to "move" the Access file to the work folder, to an explicit copy and DELETE file (if it's not already being done that way) It's probably a good idea to do these as two completely separate steps too, with an On Success before, between, and after them. I'll betcha the file won't be deleted, and with luck it will fail the step and you can spot it right away.I'd also recommend against using the FileSystemObject (if you're using it) for this part; try using DOS commands, either directly or through xp_cmdshell. It at least gives you another avenue in case FileSystemObject still doesn't behave; it will either work through DOS or eliminate file permissions as the culprit.Try this both as a separate job step, and an a DTS step within the package.
_________________________Beer is healthy, I read it on the internet. It must be true!Edited by - vivaldi on 05/15/2002 17:51:55 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 18:00:36
|
| So it can definitely be deleted from the download folder? I'm REALLY at a loss.Have you tried copying the filled version to the download folder under a different file name? If that works, how about doing the copy that way, and then renaming the file?I don't suppose it's possible to simply DTS the data to an Access database already in the download folder? |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2002-05-15 : 18:21:00
|
| I could do the dts to the download folderbut,I figured there would be some "concurrency" issues with people downloading the db and the dts running every hour.I separated everything up to the final delete/copy part into separate dts processes, i run one, then the other. It works fine there. I can do any of the copies and deletes running directly through the command line (or double-click on it in Windows Explorer). All the DOS batches run good.I personally (not that I am a sql god, like yourself) think that either Sql Server has separation anxiety (not committing the transaction) or Access is taking its merry time finishing up its transactions. I can try your other method, but I don't see the naming being an issue, cause i can have no .mdb in the download folder, do the copy and still get the "pre-filled" version. Is it Friday Yet?_________________________Beer is healthy, I read it on the internet. It must be true! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 18:37:43
|
quote:
I can try your other method
For God's sake, if you've got it working, DON'T TOUCH IT!  |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2002-05-15 : 18:45:59
|
| WellIt works if I manually run both processes separate.I will have to schedule the dts's staggered.but it seems like bad programming. (business as usual).I really wanted this thing to be contained in one dts but I guess multiple schedules will have to do until i figure out issue.Why should I use dos for file stuff versus file systemobject?I haven't read anything to the tune of that before.Thanks for the help. on my way to a big glass of http://www.capital-brewery.com/kloster.htmauf wiedersehen_________________________Beer is healthy, I read it on the internet. It must be true! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-15 : 19:13:18
|
| It's not that you can't use the FileSystemObject, or that it doesn't work, it's that it has added layers of processing (COM interface) that command line operations don't. In fact, I don't think the command line file operations even use Windows API calls. Basically you get the lowest possible level of processing overhead (none!) and there's no chance for some other process to interfere with it. So in case there's some weird COM issue that causes the FileSystemObject to hose up, you can work around it with the command line.Second, what about putting the file operations in a REGULAR job step, not a DTS step? The first file copy would be step 1, the DTS package is step 2, and the final file copy step 3. You can still apply OnSuccess/On Failure actions to each step, so if the DTS fails, the following steps will abort. This will completely separate them from any DTS transaction, and you won't need 2 jobs on an offset schedule. |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2002-05-15 : 21:57:29
|
umm.thanks for the information on FSO, it makes more sense now.as for the other part.job steps? I will admit i don't know much sql server, just what I have come across (I am waaay more into web development/management) but I am not sure what you are meaning by job steps and not multiple dts steps. I will do some reading, and return if i am confused.Thanks for the insight,quote:
It's not that you can't use the FileSystemObject, or that it doesn't work, it's that it has added layers of processing (COM interface) that command line operations don't. In fact, I don't think the command line file operations even use Windows API calls. Basically you get the lowest possible level of processing overhead (none!) and there's no chance for some other process to interfere with it. So in case there's some weird COM issue that causes the FileSystemObject to hose up, you can work around it with the command line.Second, what about putting the file operations in a REGULAR job step, not a DTS step? The first file copy would be step 1, the DTS package is step 2, and the final file copy step 3. You can still apply OnSuccess/On Failure actions to each step, so if the DTS fails, the following steps will abort. This will completely separate them from any DTS transaction, and you won't need 2 jobs on an offset schedule.
_________________________Beer is healthy, I read it on the internet. It must be true! |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-05-16 : 11:25:47
|
| Did you happen to watch the directory to see if an .ldb file was being created at any given time? Maybe you could loop and test for it's existance before moving the .mdb file. As far as the jobs go, after you schedule your dts package, you can modify the job steps to do extra things. You could have a CmdExec job step which does the move after the dts package has successfully executed. This extra step might also buy you the time you need.Capital Brewery! That means you're in Madison.... I'm an east sider. Mind if I ask where you work?Jeff BanschbachConsultant, MCDBA |
|
|
|
|
|
|
|
|
DTS Access Issue