Lauren Quantrell (la************ *@hotmail.com) writes:
I have created the following trigger:
CREATE TRIGGER [CreateFile] ON OutputTable
FOR INSERT
AS
Declare @filename nvarchar(35)
Declare @filecontents nvarchar(2000)
Declare @strcmdshell varchar(150)
SELECT @filecontents = OutputText FROM INSERTED
SELECT @filename = 'c:\' + OutputFileName FROM INSERTED
SELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filename
exec master..xp_cmds hell @strcmdshell
Didn't I tell you that must handle multi-row inserts? "I don't
have to", you said in an earilier posting. Well, someone changes
the application, and...
It works fine as long as the column OutputText has no carriage
returns.
I have used my Access2K front end to dump about ten lines of text into
OutputText, each line broken by vbcrlf so that OutputText looks like:
line1test
line2text
line3text
etc.
The trigger won't fire with this text.
Of course the trigger fires no matter the data. But what you think
is going to happen with this command batch:
ECHO line1test > c:\filenmane
line2text
line3text
You are only writing line1test to c:\filename - and only if you are
lucky. (Would line1test include a DOS meta-character, something else
will happen.)
I don't know if you can write multi-row strings with ECHO, but I don't
think so. In that case, you would have to break the strings into lines
and write each line with xp_cmdshell. You could set up a cursor over
iter_charlist_t o_table(*) , and call xp_cmdshell for each line, but
it's not good for performance.
(*)
http://www.sommarskog.se/arrays-in-s...ist-of-strings
You are still sure that you don't want to run a job from Agent that
reads the table and writes the files?
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp