SQL Server Performance Forum – Threads Archive

bcp and transaction problem

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 OFF Why 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
–harvinder

---

Hi, You don’t need to do bcp out within a transaction,
The transaction log keeps the changes only. Oryan

---

]]>