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 |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2009-04-29 : 05:05:36
|
| Identify Completion of BCP Hi GuysI have couple of BCP command running in my SP. Depending on the data size few might be fast and few might take some time. I want to track the completion of the bcp command. Is there a way I can be notified the completion of the last command. I tried to handle this problem by logging to table. Before start I login and at the end of command I updated the status. But the problem is before completion of the activity it executes the update statement.They has to be a way to handle it. RegardsKhalik========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-29 : 05:47:26
|
| Are your sp's calling bcp vie command shell?if so then you might want to switch to BULK INSERT instead. Same functionality but it's all in the sql.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-04-29 : 06:00:40
|
| thnaks Charlieusing xp_cmdshell to execute i have a custom column delimeter given. bulk inser is use to load the text file to table. in my case export the table data to txt file.what ur take which is better?========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-29 : 08:47:37
|
| Ah -- you are getting information OUT of the database?Sorry -- had my data import blinkers on!Not sure of the best way to do something like this (I'm pretty much always putting information INTO the database with bulk tools not taking it out)How about changeing the call so that it doesn't call BCP directly but does call a batch file? your batch file could do the BCP OUT command and then do a bcp in command with the time it finishedYou'd have to set up a logging table but that wouldn't be a pain.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-04-29 : 08:50:09
|
| Can be a good option but how do i do BCP in command?========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-29 : 09:04:45
|
Check out BCP in books online or in management studio help.Basically bcp can either be run with OUT or IN.if you use IN then you need to import data from a file. What you could do is set up a batch file like this:@ECHO OFF<< do the normal BCP out >>ECHO bcpFinished,%DATE% %TIME% > date.csv<< do a BCP in taking data from the date.csv file >> After the bcp out command finished then the bat file will write out a tiny csv file like bcpFinished,29/04/2009 14:01:48.42 Which you can import with the second bcp command.1 problem I can think of is if you have to do ad hoc bcp out's setting up some unique filename would be difficult. If you have only some set schedule of BCP commands to run then making up a batch file for each would be fairly trivial.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-04-29 : 09:16:21
|
| thanks Charliei have a very complex dynamic Qry building model with dynamic file name. implementing this will not be easy task. thanks for your input & suggestion. give me some thing to think on.I not sure if the batchfile will be Sequential the first command of bcp will still be processing and the second command of echo get executed.is there a way i can put more than one command from SQL like BCP and ECHO in one shot and it does Sequential========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
|
|
|
|
|
|
|
|
Identify Completion of BCP