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


tsql write multi line parameter to text file sql 2000

Posted on 2009-12-21
Medium Priority
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26103190
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

Mark Wills earned 2000 total points
ID: 26103346

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


Author Closing Comment

ID: 31668581
Thansk for your help

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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

834 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