Triggers, BCP and permissions

Posted on 2004-11-14
Medium Priority
Last Modified: 2011-04-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.

Question by:fearlessfish
  • 2
  • 2

Assisted Solution

andy232 earned 600 total points
ID: 12579519
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.

Accepted Solution

SashP earned 900 total points
ID: 12579655
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

Author Comment

ID: 12579841
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 ?


Expert Comment

ID: 12579884
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

Author Comment

ID: 12584814
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question