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

  • 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'
  • 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 ...
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:

1    Line 1
2    NULL

And nothing is in my text file
Anthony PerkinsCommented:
Yes, your are right.  I am afraid I have no idea.
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.

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?

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

Featured Post


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