Using bcp through a trigger in sql server 2008

Question

The purpose of the trigger is exporting an specific data from a table with bcp after inserting data on that table, so I thought doing it in this way, I know that the trigger waits for bcp, that is waiting for a lock on the table to be released, but that lock is held until after the trigger, and for this reason it doesn't work. How can I do it? or do I need to add some function or something for that works?

I'm using SQL Server 2008.

ALTER TRIGGER [TRIGGER] on [TABLE] after INSERT AS BEGIN 
   DECLARE @CMD NVARCHAR(1000) 
   SET @CMD = 'cd.. && "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" "SELECT TOP 1 CODE FROM[TABLE] WITH (NOLOCK)   ORDER BY ID DESC" queryout "\\FOLDER\FOLDER\FILE.txt" -T -c -S "[SERVERNAME]"'
   EXEC master..XP_CMDSHELL @CMD 
END

tkeen · Answer 1 · 2017-03-09T17:43:48.600

0

Not sure if this would work but you could try changing to an INSTEAD OF trigger and do the INSERT and bcp within the trigger

I suspect this may circumvent the lock on the table.

further reading - http://www.c-sharpcorner.com/UploadFile/37db1d/creating-and-managing-triggers-in-sql-server-20052008/

edited Mar 09 '17 at 17:43

answered Mar 09 '17 at 17:21

tkeen

362
1
8

Thanks for your help, I changed to INSTEAD OF, but it's the same, when I execute the trigger, it hangs... – Myra López Andrade Mar 09 '17 at 18:08

score 0 · Accepted Answer · edited May 23 '17 at 12:09

Don't use bcp in a trigger. Even if you could get it to work it will slow down your database, probably to the point where it will not be usable. bcp is a command line utility and should be treated as one

I recommend that you use an SQL Server Agent to execute extra actions on a scheduled or triggered basis

You can also read this tutorial that will help you get started with Agents

If you do not have SQL Server Agent (Express does not include it), then you have a few other options:

Write your own Agent. Here is an example
Call a stored procedure after insert. This answer used that method to solve similar problem to the one you posted
Use a stored procedure to write data and process the export code
Use a scheduled task processes data on a schedule (this is where bcp can be used without killing server performance)

Hi, is SQL Server Agent available on SQL Server Express edition? — Myra López Andrade, Mar 13 '17 at 16:14

Using bcp through a trigger in sql server 2008

2 Answers2