Insert Trigger and xp_cmdshell with carriage return text

[Lauren Quantrell]

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

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.
Is there something I can do to remedy this?
lq

[Erland Sommarskog]

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

[Lauren Quantrell]

Erland,
The front end app controls every aspect of the output and the
destination table is created for the sole purpose of handling this
output. The front end creates a specific string that is inserted into
the OutputText column so there's no chance of a stray DOS
meta-character popping in (the string is created entirely from columns
from static lookup tables based on user values.
I know I have to deal with multiple inserts for future development but
I have a sever time issue to roll this out and I constructed this on
the fly...
The front end app only inserts one row at this time.
I have to figure out how to deal with the carriage returns though...
thanks,
lq

ps(when I get time I'll convert this to run from Agent...)

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...

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?

[Lauren Quantrell]

I have come up with this using a stored procedure instead of a
trigger. An SP fires and stores the text in a table with five columns.
The SP below extracts the text into a file.

The only problem is, I need to create a file that only contains text
from one of the columns, a column names OutputText.
DO you know how I can do this?
Alter PROCEDURE "usp_ExportData "
@FileName varchar(100)
AS
SET NOCOUNT ON
DECLARE @ReturnCode int
DECLARE @ExportCommand varchar(255)

SET @ExportCommand =
'BCP myDatabaseName. .myTableName out "C:\' +

@FileName +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master..xp_cmds hell @ExportCommand
RETURN(@ReturnC ode)
/* GO */

DECLARE @ReturnCodeX int
EXEC @ReturnCodeX = usp_ExportData 'MyFile.txt'
PRINT @ReturnCodeX
"Mischa Sandberg" <mi************ *@telus.net> wrote in message news:<KQxLc.816 24$Rf.10988@edt nps84>...

Okay, the cmd shell is interpreting the crlf as end-of-command for each of
the echo commands.
Here's the crude solution:

SELECT @strcmdshell = 'copy NUL '+@filename+'
echo '+replace(@file contents, char(10), ' >>'+@filename+c har(10)+'echo ')+'

'+@filenana me

Print the resulting string and you'll see what the multiline command does.

"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...

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

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.
Is there something I can do to remedy this?
lq

[Mischa Sandberg]

My bad, I didn't test what I posted. And the way it appears here, the line
breaks
may have gotten a bit munged.

Try using '&' as a command separator instead of char(10); i.e:

SELECT @strcmdshell = 'copy NUL '+@filepath
+ ' & echo'
+ replace(@fileco ntents, char(10), ' >>'+@filepath +' & echo ')+'

'+@filepath
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** **@posting.goog le.com...

Mischa,
Thanks for your response.
The template is now created with the proper filename but it is blank
(no text.)
I'm using this, modified from your example:
CREATE TRIGGER CreateTemplate ON tblEventsTempla tes
FOR INSERT
AS

Declare @filename nvarchar(35)
Declare @filepath nvarchar(45)
Declare @filecontents nvarchar(2000)
Declare @strcmdshell varchar(150)
SELECT @filecontents = TemplateText FROM INSERTED
SELECT @filename = TemplateFileNam e FROM INSERTED
SELECT @filepath = 'c:\' + @filename
SELECT @strcmdshell = 'copy NUL '+@filepath+' echo
'+replace(@file contents, char(10), ' >>'+@filepath+c har(10)+'echo ')+''+@filepath exec master..xp_cmds hell @strcmdshell


"Mischa Sandberg" <mi************ *@telus.net> wrote in message

news:<KQxLc.816 24$Rf.10988@edt nps84>...

Okay, the cmd shell is interpreting the crlf as end-of-command for each of the echo commands.
Here's the crude solution:

SELECT @strcmdshell = 'copy NUL '+@filename+'
echo '+replace(@file contents, char(10), ' >>'+@filename+c har(10)+'echo ')+'

>'+@filenana me

Print the resulting string and you'll see what the multiline command

does.
"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@posting.goo gle.com...

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

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.
Is there something I can do to remedy this?
lq