BCP Trigger Hangs up Insert Statement

Question

Post reply

BCP Trigger Hangs up Insert Statement

holder.collin

SSC Veteran

Points: 205
More actions
April 23, 2008 at 11:49 am

#93237

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].
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
November 20, 2017 at 10:11 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Hrrmm...

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bijayanix24 SSC-Addicted Points: 447 More actions · Answer 5

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[/url]: India's Only Company to be both Apple Premier & Microsoft Gold certified.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

you'll probably get a better response if you start a new thread for your question.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]