BCP Trigger Hangs up Insert Statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » BCP Trigger Hangs up Insert Statement

BCP Trigger Hangs up Insert Statement

Rate Topic

Display Mode

Topic Options

Author

Message

holder.collin

Posted Wednesday, April 23, 2008 11:49 AM

Forum 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

SwePeso

Posted Wednesday, April 23, 2008 12:29 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:26 PM
Points: 2,359, Visits: 3,292

Hrrmm...

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

N 56°04'39.16"
E 12°55'05.25"

Post #489494

Michael Earl-395764

Posted Wednesday, April 23, 2008 12:46 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, 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

RBarryYoung

Posted Wednesday, April 23, 2008 2:18 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

Jeff Moden

Posted Friday, April 25, 2008 7:28 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 32,893, Visits: 26,765

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #491007

bijayanix24

Posted Thursday, March 18, 2010 1:02 AM

Grasshopper

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

RBarryYoung

Posted Tuesday, March 23, 2010 11:59 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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 »

Permissions