Solved

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

Posted on 2010-08-27
5
1,146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 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