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
--harvinderharvinder,
> 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?
Bcp is running as a separate connection, not as a part of your
transaction. This is normal. It is being blocked by the connection
that opened the transaction. You cannot wrap a call to bcp in a
transaction.
Linda