December 3, 2001 at 3:44 pm
I would like a create an insert trigger that will, once fired, take the inserted field and append it to the end of a text file. The trigger part I know, the appending to a text file eludes me. I'm using sql2000.
Any suggestions?
- Vega
December 3, 2001 at 4:05 pm
This is dangerous to do in a trigger. The insert will not complete until the write does and that could be an issue from the OS.
From a trigger, you could build an xp_cmdshell command to add this. Somethign like "echo <new value> >> <filename>" where new value is the item from the trigger and the filename is the name of the file on the server. I'd think you'd want to batch these using some other process that will not hold up your insert if there is an error.
Steve Jones
December 3, 2001 at 7:21 pm
Agree not a good thing to do in a trigger.
Why not have the trigger insert into a table then have another scheduled task which does the output.
What are you doing with the file? Maybe you could have diefferent files for each export and include the datetime in the name - then you could use bcp which will be a lot simpler.
Cursors never.
DTS - only when needed and never to control.
December 4, 2001 at 6:20 am
Currently we have an application that places data into SQL and into a text file that is used by a system called 4th Shift. Currently our version of 4th Shift cannot directly import from SQL, so we have a text file. The insert into table, then output to file would be great except how do you keep SQL from overwriting the text file and just append to it?
- Vega
December 4, 2001 at 8:48 am
Use >> if you do it through a shell.
select @text = ColumnA from TableA
exec master..xp_cmdshell 'echo ' + @text + ' >> c:\test.txt'
Steve Jones
December 4, 2001 at 9:52 am
Or use file scripting object, it will let you append to an existing file.
Andy
December 4, 2001 at 11:02 am
If you want to use DTS, I'd use andy's suggestion of FSO.
Either way, I'd get it out of the trigger and use some other process to move data often.
Steve Jones
December 4, 2001 at 3:57 pm
Ok, Got the output file going good. I'm am now atempting to use @@Error if their is a problem writing to the file, (i.e. I make it read only 1/2 way through the write sequence)
SQL keeps writing the records to the table while the text file does not(cause it's read-only) I cant get @@Error != 0 to rollback the tran. Does windows/DOS return an error to sql when using the xp_cmdshell?
- Vega
December 4, 2001 at 4:29 pm
Yep. Look in BOL under xp_Cmdshell in the TSQL section, item D (in SQL2K). Zero indicates success. Or that there was no return value!
Andy
December 4, 2001 at 5:20 pm
You won't get much in error trapping from using xp_cmdshell. If you need the trap, use FSO.
Steve Jones
December 5, 2001 at 12:21 pm
What you are most likely going to have to do is
DECLARE @result int
EXEC @result = master.dbo.xp_cmdshell '<command line>'
or something similar and test @result. That's the only way to get the value back from DOS. Remember, so far as SQL Server is concerned, xp_cmdshell ran successfully, therefore @@ERROR = 0.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
December 8, 2002 at 6:42 pm
Agreed that the trigger is not a good idea to use to write to the system files. What we did when we had a similar situation was this: the trigger stored the row's key in a separate "to be written" table. We had a task than ran once per minute and copied the rows in the "to be copied" table out, then cleared it of the ones copied. It wasn't real time but very close to near time.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply