tsql write multi line parameter to text file sql 2000

Posted on 2009-12-21
Last Modified: 2012-05-08
I am reading the body of an email into a parameter (multi lined) i want to write this out to a text file. I have the following which will only write one line but i need to keep the lines split as they appear in the email, can anyone help?

            Declare @Script      varchar(8000)
            Set @Script = 'Echo test > c:testfolder\testfile.txt'
            exec master..xp_cmdshell @Script, no_output
Question by:9772885
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    what about this:
    Declare @Script      varchar(8000)
                Set @Script = 'Echo test > c:testfolder\testfile.txt'
                exec master..xp_cmdshell @Script, no_output
                Set @Script = 'Echo test2 >> c:testfolder\testfile.txt'
                exec master..xp_cmdshell @Script, no_output

    Open in new window

    LVL 51

    Accepted Solution


    If you are reading the body, then one would assume it is more like

    "Hi 9772885,
    How is it going ?
    kind regards,

    and using "echo" will not handle that type of message.

    What you might need to do is run a BCP type export into a flatfile... Also depends a bit on the datatype that the body is being stored in.

    for example:

    -- OK suppose we have a variable that represents the email body

    DECLARE @EmailBody nvarchar(4000)
    Set @emailbody = 'Hi 9772885,
    How is it going ?
    kind regards,

    -- now if ou temp table does not exist, then create it

    if object_id('tmp_email_output','U') is null Create table tmp_email_output (id int identity primary key, body nvarchar(4000))

    -- Now let's populate our table (and we probably need to have update flags etc depending on concurrency)

    insert tmp_email_output (body)
    select @emailbody

    -- lets then export the contents

    EXEC master..xp_cmdshell 'bcp "select body from mydb..tmp_email_output x order by" queryout "c:\ee\my_email_flatfile.txt" -T -c -CACP'


    Author Closing Comment

    Thansk for your help

    Featured Post

    Highfive Gives IT Their Time Back

    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!

    Join & Write a Comment

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    22 Experts available now in Live!

    Get 1:1 Help Now