Insert Trigger and xp_cmdshell with carriage return text -- sql server (alternate)

laurenquantrell NO[at]SPAM hotmail.com
7/21/2004 9:25:01 AM

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...)

[quoted text, click to view]

Mischa Sandberg
7/21/2004 5:36:42 PM

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 ')+'
[quoted text, click to view]

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

[quoted text, click to view]

laurenquantrell NO[at]SPAM hotmail.com
7/22/2004 8:58:12 AM

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 ')+'
[quoted text, click to view]
exec master..xp_cmdshell @strcmdshell

[quoted text, click to view]

laurenquantrell NO[at]SPAM hotmail.com
7/22/2004 9:00:21 AM

I also have the character ":" as a text divider throughout this text.
is it possible that is causing the errors?
lq

[quoted text, click to view]

laurenquantrell NO[at]SPAM hotmail.com
7/22/2004 10:08:24 AM

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

[quoted text, click to view]

Mischa Sandberg
7/22/2004 9:25:15 PM

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 ')+'
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Insert Trigger and xp_cmdshell with carriage return text