BCP Command Freeze inside a SQL Trigger , SQL Server 2008 R2

Hi,

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
 
thanks
LVL 1
bmdgiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
carsRSTConnect With a Mentor Commented:
try using a NOLOCK in your BCP query.
0
 
carsRSTCommented:
0
 
bmdgiAuthor Commented:
I Try, but now the file doesn't create...

Really strange....

Any other idea?
0
 
bmdgiAuthor Commented:
I the issue with the NOLOCK and I needed to add the -o for the Output Files.

Both Fix my issue.

0
 
bmdgiAuthor Commented:
Need to add the -o and an output file with the NOLOCK

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.