Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Trigger Hangs up Insert Statement Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2008 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 28, 2012 7:40 AM
Points: 1, Visits: 21
Hi All,

I have an issue where I am trying to create a trigger that will output a single row to a text file on insert, based on a field in that insert. rowID is a primary key that increments itself, triggerfield is user entered data. If I create this trigger and then try to insert, the inset statement hangs and I have to kill and restart the instance. Does any one know if this has to do with record locks or if there is an easier way to accomplish exporting a single row on insert?

This is the trigger I have right now--

CREATE TRIGGER [dbo].[T_OUTPUT_FILE]
ON [DATABASE].[dbo].[TABLE]
AFTER INSERT
AS
IF (SELECT COUNT(*) FROM inserted WHERE [TRIGGERFIELD] = 5) = 1

BEGIN
SET NOCOUNT ON;

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000),
@Record varchar(10)

SET @Record = (Select rowID from inserted WHERE [TRIGGERFIELD] = 5)

SET @FileName = REPLACE('c:\Testing\DropFolder\row_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM database.dbo.table WHERE rowID = convert(int,'
SET @bcpCommand = @bcpCommand + @Record + ')" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U Username -P Password -c -S Server\Instance'

exec master..xp_cmdshell @bcpCommand

END
Post #489464
Posted Wednesday, April 23, 2008 12:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:23 AM
Points: 2,397, Visits: 3,411
Hrrmm...

What if there are more than one record with TriggerField = 5 ???



N 56°04'39.16"
E 12°55'05.25"
Post #489494
Posted Wednesday, April 23, 2008 12:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
You are probably somewhat deadlocking yourself. The BCP cannot access the record because it is exclusively locked from the insert statement. You could use a NOLOCK hint in the BCP command and read the dirty page.

Now, this looks like a bad idea to begin with. Calling xp_cmdshell from a trigger is probably not a good idea. If you MUST write a file out from a trigger, you would be best off getting it out of your transaction. An option could be Service Broker. I think you could create a CLR procedure and call to a web service.
Post #489514
Posted Wednesday, April 23, 2008 2:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
Agree with Michael: using BCP from a trigger is a bad idea, and re-querying data from another process synched with your trigger code (and that is what you are doing here) is a deadlock that the deadlock manager can never find, so you will hang forever.

Even WITH (NOLOCK) has problems with this and Service Broker is what I have used in the past to fix it.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #489582
Posted Friday, April 25, 2008 7:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 35,263, Visits: 31,749
Another problem other than the ones already stated is that even if all of this worked correctly, BCP destroys existing files and overwrites them.

I may have a work-around for this, but I need to know... why do you only want 1 of the inserted rows instead of all that have been inserted?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #491007
Posted Thursday, March 18, 2010 1:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 2:18 AM
Points: 15, Visits: 21
In SQL, after creation of table we insert data using INSERT keyword. Suppose we have to insert 10 records, we write 10 INSERT statement for inserting them. However, in SQL Server 20008 a new feature called Row constructor has been introduced which allows us to write a single INSERT statement for inserting more than one record at a time.


Cheers,
Eliza


Cheers,
Bijayani
Proud to be a part of Team Mindfire.

Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified.
Post #885285
Posted Tuesday, March 23, 2010 11:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
you'll probably get a better response if you start a new thread for your question.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #888701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse