# 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
###### Who is Participating?

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

Billing EngineerCommented:
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

0

Author Commented: