[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# tsql write multi line parameter to text file sql 2000

Posted on 2009-12-21
Medium Priority
261 Views
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
0
Question by:9772885

LVL 143

Expert Comment

ID: 26103190
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

LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 26103346
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

Author Closing Comment

ID: 31668581
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month18 days, 18 hours left to enroll