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'
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.