Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-05 : 08:06:37
|
| Joel writes "I would like to write data to a text file when a row is inserted or updated in a SQL table.I created a trigger (insert,update) on the table that issues an master..xp_cmdshell statement - I have tried both bcp and osql, but the trigger never returns.The code is similar to this:create trigger Testtable_Insert on Testtablefor insert,update as DECLARE @FileName varchar(50), @bcpCommand varchar(2000)insert CUSTOMERS select CUSTNMBR,CUSTNAME, CUSTCLAS ,CPRCSTNM,CNTCPRSN from inserted--Method oneSET @FileName = 'c:\test.csv'SET @bcpCommand = 'bcp "SELECT CUSTNMBR, CUSTNAME from [DB]..[CUSTOMERS] " queryout "'SET @bcpCommand = @bcpCommand + @FileName + '" -S JOELE\AUST -U sa -P -c -t \,'EXEC master..xp_cmdshell @bcpCommand, no_output--Method 2EXEC master..xp_cmdshell 'osql /U sa /P /d TWO /S JOELE\AUST /Q "Select CUSTNMBR, CUSTNAME from [TWO]..[CUSTOMERS] " -o c:\test.txt'Control never returns from the trigger.Is there some way to get this to work?Is there a beter way to log changes made to a SQL table to a text file through a trigger?I need this work from the database level, not a client application level.Thanks" |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-04-05 : 09:01:54
|
| Rather than doing this in the trigger, you could Create an error for this with a low severity. Make sure the error gets logged. Then Create an alert. The alert would fire a Job which would do the right to file. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-05 : 09:05:10
|
Truthfully, I wouldn't run xp_cmdshell in a trigger. How about changing the trigger to: 1) use sp_start_job inside the trigger to fire the export job; or2) have a scheduled job run periodically to export the data to a text file (say every 5 minutes or so)I'd recommend a scheduled job over sp_start_job because of resource contention (like the lockups you're seeing now) If the table has very heavy INSERT activity, you could possibly overwrite a text file, or have an error while two INSERT operations try to write the same file at the same time.I'd also recommend using bcp for this, it automatically terminates when completed (osql does not close automatically by default, but there is a flag you can use to have it do so)To paraphrase Bill Murray: He sniped me!  Edited by - robvolk on 04/05/2002 09:06:16 |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-05 : 09:15:33
|
| Rob provides two great suggestions. One more that you may consider is instantiating a FileSystemObject and handle the streaming out to the file on your own and maybe inside the trigger. It depends what you mean by 'log changes'.Now if you goal here is to have an Audit History on your table, there are some other great techniques for that using database tables. Take a look hereJay<O> |
|
|
|
|
|
|
|
|
Log table changes to a text file using a trigger