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

Posted on 2004-11-05
Last Modified: 2012-08-13
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'
Question by:ABaruh
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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 ...
    LVL 7

    Author Comment

    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
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Yes, your are right.  I am afraid I have no idea.
    LVL 8

    Expert Comment

    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?

    LVL 8

    Accepted Solution

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

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now