Triggers, BCP and permissions

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What you should do is schedule a SQL job that writes its output to a text file.  You can put print statements at the top to format the file as a SMTP email.  Just tell the SQL job to save the output in the pickup folder with the extension of .eml, SMTP will send it out.

I definitely would recommend not to create external shells to the OS, they could hang.  Especially when you have such nice options as SQL jobs that write to a text file or the OSQL utility which also writes to a text file.  Run it with osql -? to learn more about it.
Hi fearlessfish

If you have to write a file from inside an SQL process use the filesystemobject.  However I do not recomend it, I am always cautious executing processes external to SQL Server as it is always a chance of hanging the process, even if the process is a SQL Server provided app or com object.  

Andy's suggestion of a scheduled job writing the file is a good one, what you will need to consider is the tradeoff between running the job too often and consuming uneccessasry resources on the server (a well designed job shouldn't have much impact though) and running it too infrequently and having too much time between the reccord being written and the job being executed and hence the email being sent.

You will also need a mechanism to record what records have had the information written to the .eml file which you may have been planning to omit using the trigger method (if you were going to assume that the trigger method guaranteed the file creation).

You might also consider SQL Mail to do the delivery of the emails directly to the recipient or perhaps you email the information directly to a single email account which the SMTP service could monitor (if possible) instead of the directory.

The code for the filesystemobject you would use as follows and would work inside a trigger.

declare @FileName varchar(255)
declare @doc varchar(8000)
DECLARE @FS int, @hr int, @file int

set @FileName = '\\ServerName\Foldername\TSQL_FSO.txt'  
SET @doc =
'Line 1
Line 2
Line 3

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @hr <> 0 select 'Error Occured creating FileSystemObject', @hr

--Open a file
execute @hr = sp_OAMethod @FS, 'CreateTextFile', @file OUT, @FileName
IF @hr <> 0 select 'Error Occured creating FileSystemObject', @hr

--Write Text1
execute @hr = sp_OAMethod @file, 'WriteLine', Null, @doc
IF @hr <> 0 select 'Error Occured Writing Line', @hr

execute @hr = sp_OAMethod @file, 'Close', Null
IF @hr <> 0 select 'Error Occured Closing File', @hr

EXECUTE @hr = sp_OADestroy @file
EXECUTE @hr = sp_OADestroy @FS

Cheers Sash

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fearlessfishAuthor Commented:
From what you guys are saying it seems that calling the OS from a trigger would be a bad idea.
So instead the trigger could write the email contents to a 'waiting to be sent' table, then I could write a job which ran in the background checking this table and creating the appropriate files.
Andy, I didn't know you could put OS calls in a SQL job (i've never written one before) - thanks for that.  
But can OS commands called in a job also hang ?   If so, is the best method then to use the FileSystemObject (either from the sp_OAxxx  calls or via VBScript) the best solution. Is this what you're are saying ?

The 'waiting to be sent' table is a good solution.

Using the FileSystemObject in either VB, VBScript, C whatever is a preferable solution to calling it from within SQL Server.

I won't repeat my comments about calling external processes as you have my opinion.  

Cheers Sash
fearlessfishAuthor Commented:
Guys, after much thought I've decided on a slicker solution. I've created an extended stored procedure which I can call from the trigger.
Thanks anyway.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.