Triggers, BCP and permissions

Posted on 2004-11-14
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
    LVL 6

    Assisted Solution

    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.
    LVL 8

    Accepted Solution

    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

    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 ?

    LVL 8

    Expert Comment

    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

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    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.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now