We need to write a trigger so that when rows inserted into a sql server table meeting certain conditions, they are then written to a windows OS flat file in CSV format.
Are there any commands short of running a xp_cmdshell that would allow us to do this?
Use Bulk Copy Program (BCP) to create CSV files. For example:
BCP master..sysobjects out c:\sysobjects.txt -c -t, -T –S<servername>
The basic format for the BCP command for creating a CSV file is as follows:
BCP out
The switches used here are:
-c Output in ASCII with the default field terminator (tab) and row terminator (crlf)"
-t override the field terminator with ","
-T use a trusted connection. -U & –P may be used for username/password
-S connect to this server to execute the command
Here's another example:
declare @sql varchar(8000)
select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql
Here's a description of how to use BCP: http://msdn.microsoft.com/en-us/library/ms162802.aspx
Any reason you wouldn't instead do a scheduled process with SSIS?
Depending on your transaction rate, I'd be hesistant to put this in a trigger since it means evaluations and possible export on every record entered. If you get a high frequency, you could shoot yourself in the foot...
Even if the transaction rate is fairly low, you could potentially still have problems like blocking/locking if the physical writes take long. You introduce several possible classes of errors (file i/o errors, write-fail means trigger fails means insert fails, etc.).
With a scheduled process you only take a hit on an infrequent basis and you don't potentially lock your table while the trigger is doing something external.