Solved

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

Posted on 2010-08-27
5
1,106 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:bmdgi
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
ID: 33546566
try using a NOLOCK in your BCP query.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33546575
0
 
LVL 1

Author Comment

by:bmdgi
ID: 33547964
I Try, but now the file doesn't create...

Really strange....

Any other idea?
0
 
LVL 1

Author Comment

by:bmdgi
ID: 33592321
I the issue with the NOLOCK and I needed to add the -o for the Output Files.

Both Fix my issue.

0
 
LVL 1

Author Closing Comment

by:bmdgi
ID: 33592332
Need to add the -o and an output file with the NOLOCK

Thanks!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question