0

When I execute this procedure without a trigger, it all works fine

EXEC sys.XP_CMDSHELL 'bcp "select * from users FOR JSON AUTO;" queryout E:\temp\JsonExport.json -t, -c -S . -d learntrigger -T'

but if I have created the trigger and make an INSERT, then I get no response from the server and I have to end the command manually

CREATE TRIGGER usersJosnExportTrigger  
ON users
AFTER INSERT, UPDATE, DELETE   
AS  
    EXEC sys.XP_CMDSHELL 'bcp "select * from users FOR JSON AUTO;" queryout E:\temp\JsonExport.json -t, -c -S . -d learntrigger -T'
GO

This is the INSERT I use 

INSERT INTO users (username, email, number) values  ('maxmuster','muster@gmail.com','123456789'),

I don't understand why and I hope you can help me.

krlzlx
  • 5,752
  • 14
  • 47
  • 55
Alessandro Rhomberg
  • 127
  • 3
  • 11
  • 10
    Using `xp_cmdshell` in a trigger seems like a really bad idea in the first place. I wouldn't do this process as part of trigger at all. you would be better off designing a SP to do this, or creating a pooling table with the data to the extracted, and a regular running agent task. – Thom A Jun 04 '19 at 08:50
  • 1
    Did you make a typo in the title? You seem to say it works in both places – Caius Jard Jun 04 '19 at 08:57
  • 1
    The trigger runs in the context of the DML statement transaction that fired the trigger and the query run by BCP is a separate process, blocked by the uncommitted transaction. I suggest you heed the suggestion by @Larnu. – Dan Guzman Jun 04 '19 at 10:37

0 Answers0