tsql write multi line parameter to text file sql 2000

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
            END
9772885Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Ummm....

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

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

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,
Mark'

-- 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 x.id" queryout "c:\ee\my_email_flatfile.txt" -T -c -CACP'

GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
            END

Open in new window

0
 
9772885Author Commented:
Thansk for your help
0
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.

All Courses

From novice to tech pro — start learning today.