BCP Command Freeze inside a SQL Trigger , SQL Server 2008 R2
Posted on 2010-08-27
I create a trigger, to insert data into a temp table and after extract those data with a bcp command, and cleanup the table after.
Everything work fine in part, but in the trigger itself. Everything freeze when I insert data and this seems to be causes by the BCP command, because the file is created, but nothing is inside. I need to restart SQL server.
Any idea ?
This is a simplify version of the trigger :
insert into Triggers.dbo.TEMP (ITEM,[DESC],SHIUNIT,QTYSHIPPED, UNITCONV, LOCATION,transactiondatetime) Select ITEM,[DESC],SHIUNIT,QTYSHIPPED, UNITCONV, LOCATION, @transactiondatetime from inserted
set @Query = 'bcp "SELECT ITEM,[DESC],SHIUNIT,QTYSHIPPED, UNITCONV, LOCATION,transactiondatetime FROM Triggers.dbo.temp" queryout'
set @Query = @Query + ' "e:\temp.log" ' + '-U sa -P xxx -c -S' + ' localhost'
delete from Triggers.dbo. temp where transactiondatetime = @transactiondatetime
exec master..xp_cmdshell @Query