469,275 Members | 1,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Insert Trigger and xp_cmdshell with carriage return text

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_cmdshell @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
Jul 20 '05 #1
4 7035
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_cmdshell @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_to_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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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****@sommarskog.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_cmdshell @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_to_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?

Jul 20 '05 #3
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_cmdshell @ExportCommand
RETURN(@ReturnCode)
/* GO */

DECLARE @ReturnCodeX int
EXEC @ReturnCodeX = usp_ExportData 'MyFile.txt'
PRINT @ReturnCodeX
"Mischa Sandberg" <mi*************@telus.net> wrote in message news:<KQxLc.81624$Rf.10988@edtnps84>...
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(@filecontents, char(10), ' >>'+@filename+char(10)+'echo ')+'
'+@filenaname


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

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
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_cmdshell @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

Jul 20 '05 #4
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(@filecontents, char(10), ' >>'+@filepath+' & echo ')+'
'+@filepath
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47*************************@posting.google.co m...
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 tblEventsTemplates
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 = TemplateFileName FROM INSERTED
SELECT @filepath = 'c:\' + @filename
SELECT @strcmdshell = 'copy NUL '+@filepath+' echo
'+replace(@filecontents, char(10), ' >>'+@filepath+char(10)+'echo ')+''+@filepath exec master..xp_cmdshell @strcmdshell


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

news:<KQxLc.81624$Rf.10988@edtnps84>...
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(@filecontents, char(10), ' >>'+@filename+char(10)+'echo ')+'
>'+@filenaname


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

does.
"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
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_cmdshell @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

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Yvonne | last post: by
9 posts views Thread by Lauren Quantrell | last post: by
1 post views Thread by Paradigm | last post: by
1 post views Thread by lytung | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.