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 |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2003-12-01 : 15:53:07
|
| Hi,We are creating a procedure in which we use bcp(xp_cmdshell) to archive out the data for particular month. When i execute procedure without begin tran/commit tran it works fine and bcp out the data from table to hard disk. But when i try to use transaction handling using begin tran i see it to be waiting and dbcc inputbuffer of waiting process shows:SET FMTONLY ON select * from db..bcptable SET FMTONLY OFFWhy sql server is starting seperate process within procedure ...IS there any special way to handle transaction when using bcp and xp_cmdshell in the procedure?This is the only procedure running on machine.Thanks--rubs |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-01 : 16:03:16
|
| Why not use BULK INSERT instead so that you don't need to use xp_cmdshell? BULK INSERT is faster anyway.Tara |
 |
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2003-12-01 : 16:04:00
|
| bulk insert is only used for data in..we are doing data out.... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-01 : 16:09:49
|
| Oops, missed that part in your thread. I'll see if I can find your answer.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-01 : 16:12:43
|
| Using transactions is for data modifications. With bcp out, you aren't making any data modifications. Why use a transaction? Can't you bcp the data out, then start the transaction then delete the data or whatever is done in the source table, then end the transaction? Do you need to verify that the file was created successfully? Can't that be done with the return codes of xp_cmdshell?Tara |
|
|
|
|
|
|
bcp and transaction problem