Triggers, BCP and permissions
Posted on 2004-11-14
I've been asked to create a uniquely named file in a specified folder for every insert made into a SQL 2000 database table.
Essentially this file will contain email information which will be picked up by an existing SMTP service.
I've looked at calling BCP from a trigger but I'm unsure how to set permissions up in SQL server or windows to make this work.
I've also read that this is a bad idea because the spawned process could hang (as I've discovered trying to get it to work). If the permissions are set up correctly would it be OK to do this ?
If not, does anyone have any suggestions on the best way to do this ? If possible I would like a solution with decent performance but that is also stable and reliable.