write multiple lines to DOS file with xp_cmdshell
-
I am trying to write multiple lines to a DOS file with xp_cmdshell. This works:
DECLARE @cmd varchar(255)
SET @cmd = 'echo line 1 > C:\outfile.txt' -- overwrites the file if present
EXEC master..xp_cmdshell @cmd
SET @cmd = 'echo line 2 >> C:\outfile.txt' -- appends to the file
EXEC master..xp_cmdshell @cmd
Is there a way to somehow concatenate the lines into one string and call xp_cmdshell once? I tried this, and it does not work:
DECLARE @cmd varchar(255)
SET @cmd = 'echo line 1' + CHAR(13) + CHAR(10) + 'echo line 2' > C:\outfile.txt' -- overwrites the file if present
EXEC master..xp_cmdshell @cmd
Any ideas? I can write to a temp table, then bcp out, but that seems like overkill to write just a few lines. Besides, the second method above really oughta work.
There is no "i" in team, but idiot has two. -
Tried and failed - same as you. How irritating!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
You're not just whistling, Phil.
I have tried every variation of CHAR(13) + CHAR(10), and every example of ECHO I could find. No joy.
There is no "i" in team, but idiot has two. -
Maybe silly, but if your input strings are short enough you could use the | operator to separate several commands on one line:
line 1 > C:\outfile.txt|line 2 >> C:\outfile.txt|line 3 >> C:\outfile.txt
... and so on out to, what, 255 characters?
<Shrug> I don't believe xp_cmdshell is intended to be an actual shell interface for SQL Server. Just a single command-line.
-SJT
-
Sauron: I tried your example, and QA went off for five minutes or so, then gave me a "timeout" message and broke the connection. I'm not sure what I might have done wrong there. FWIW, I am sure xp_cmdshell is still the original version that a summer intern cranked out in a latte fueled frenzy in 1987, but it's all we've got.
Everyone got their airsickness bags handy? Here's what worked:
DECLARE @Command nvarchar(2000)
, @LineConnector nchar(1)
, @FileCreateChar nchar(1)
, @FileAppendString nchar(2)
, @FilePath nvarchar(120)
SELECT @LineConnector = '&', @FileCreateChar = '>', @FileAppendString = '>>',
@FilePath = '\\Servername\DATA\IDBR\ExtractFiles\outfile.txt'
SET @Command = 'ECHO line1' + @FileCreateChar + @FilePath + @LineConnector
+ 'ECHO line2' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line3' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line4' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line5' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line6' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line7' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line8' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line9' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line10' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line11' + @FileAppendString + @FilePath + @LineConnector
+ 'ECHO line12' + @FileAppendString + @FilePath + @LineConnector
exec master..xp_cmdshell @Command, NO_OUTPUT
"Coyote ugly".
There is no "i" in team, but idiot has two. -
Is it worth it?
-
Hey, I found this link that had an interesting proc. You could change it up a bit to work for you...
Usage:
EXEC master.dbo.write_to_file @sqlErr,@errFile ,0 --Where @sqlErr is a
varchar string, @errFile is path-filename for file, 0 is append (1 is
overwrite as stated in SP)
SP:
CREATE PROC write_to_file
@msg VARCHAR(7999),
@file VARCHAR(100),
@overwrite BIT = 0
AS
/*
Date written: January 12th 2001
Purpose: To log messages to text files from stored
procedures/triggers/sql scripts
Input parameters: message, file name, overwrite flag (1 to overwrite, 0
to append to file, 0 is the default)
Example: EXEC write_to_file 'Duplicates found','C:\logfile.txt',0
Tested on: SQL Server Version 7.0, 2000
Remarks: You should have permissions required through access file system
through xp_cmdshell
See SQL Server Books Online for xp_cmdshell if you are having
problems with this procedure
*/
BEGIN
SET NOCOUNT ON
DECLARE @execstr VARCHAR(255)
SET @execstr = RTRIM('echo ' + COALESCE(LTRIM(@msg),'-') + CASE WHEN
(@overwrite = 1) THEN ' > ' ELSE ' >> ' END + RTRIM(@file))
EXEC master..xp_cmdshell @execstr
SET NOCOUNT OFF
END
EXEC write_to_file 'Line1 ','C:\test.txt',0
EXEC write_to_file 'Line2 ','C:\test.txt',0
EXEC write_to_file 'Line3 ','C:\test.txt',0
EXEC write_to_file 'Line4 ','C:\test.txt',0
This may not solve your problem, but thought it was an interesting little proc.
Good luck!
Rich
-
michanne: Sure it's worth it, if it gets done what I need done.
Rich: Thanks, that is certainly handy and dandy.
There is no "i" in team, but idiot has two. -
Try this
DECLARE @cmd varchar(255)
SET @cmd = 'echo line 1 > C:\outfile.txt && echo line 2 >> C:\outfile.txt'
EXEC master..xp_cmdshell @cmd
Far away is close at hand in the images of elsewhere.
Anon. -
This just saved my bacon -- had to restore a db from a gzipped backup without admin rights to a server.
Thanks!!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply