[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1260
  • Last Modified:

Writing multiple lines to a text file from within a stored proc

I know how to write to a text file using:

DECLARE @MyText nvarchar(500)
SET @MyText = 'type This is my text >> c:\MyLog.txt'
exec master..xp_cmdshell @MyText


What I want to know is how do I write multiple lines to MyLog.txt without having to call out xp_cmdshell each time I want a new line in my log?  Do I use newline character in @MyText like SET @MyText = 'type This is my text line 1</n>This is line2 >> c:\MyLog.txt'
0
ABaruh
Asked:
ABaruh
  • 2
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
Depending on your definition of "newline character"  you would use either
1. CHAR(13) + CHAR(10)
2. CHAR(13)
3. CHAR(10)

So if you selected the first option it would be:
SET @MyText = 'type This is my text line 1' + CHAR(13) + CHAR(10) + 'This is line2 >> c:\MyLog.txt'

And of course, for a number of reasons this is best handled by the front-end application ...
0
 
ABaruhAuthor Commented:
I don't have a front-end.  This is in a stored proc run from a SQL job.

Also, I cannot get your query to work.  I tried all three methods.

If I use @MyText = 'echo Line 1' + CHAR(13) + CHAR(10) + 'Line 2 >> c:\Test.txt' in my output I get:

      output
1    Line 1
2    NULL

And nothing is in my text file
0
 
Anthony PerkinsCommented:
Yes, your are right.  I am afraid I have no idea.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
SashPCommented:
Hi ABaruh

Is there any reason you have to use the xp_cmdshell function.  Is there any reason you do not want to use DTS?


Sash
0
 
SashPCommented:
Or if it really has to be inside the SQL perhaps the FileSystemObject.

declare @FileName varchar(255)
declare @doc varchar(8000)
DECLARE @FS int, @hr int, @file int

set @filename = 'c:\TSQL_FSO.txt'  -- remember this filename is relative to the server not your machine

SET @doc =
'Line 1
Line 2
Line 3
'

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @hr <> 0 select 'Error Occured creating FileSystemObject', @hr

--Open a file
execute @hr = sp_OAMethod @FS, 'CreateTextFile', @file OUT, @FileName
IF @hr <> 0 select 'Error Occured creating FileSystemObject', @hr

--Write Text1
execute @hr = sp_OAMethod @file, 'WriteLine', Null, @doc
IF @hr <> 0 select 'Error Occured Writing Line', @hr

execute @hr = sp_OAMethod @file, 'Close', Null
IF @hr <> 0 select 'Error Occured Closing File', @hr

EXECUTE @hr = sp_OADestroy @file
EXECUTE @hr = sp_OADestroy @FS

Cheers Sash
0
 
ABaruhAuthor Commented:
Thank you everybody but I ended up using xp_cmdshell for each line I needed to write, although I see that using fso would do the trick.  Sash, DTS is not an option for this application.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now