June 15, 2004 at 11:30 am
Hi,
I would like to know how to use the DTS to export a varchar column of a table to a file. According to length of each record, I need have a carriage return after each row (variable length file). If the DTS does not work, is there any scripts that I can use to write each record directly to a file with in a step of a SQL JOB?
Thanks
June 15, 2004 at 11:22 pm
jw8
I have had this problem before, where I had to create a blank line between records when exporting SQL => text file using DTS.
I fixed this by in the select statement in the data transformation "select <field_name(s)> , CHAR(13) + CHAR(10) AS C from <table_name>" where char(13) is the carriage return and Char(10) line feed.
This worked a treat. I hope this answers your question.
Happy Moose
vitaldata.com.au
June 16, 2004 at 12:16 am
checkout the BCP utility in the SQL Server documentation (Books Online).
BCP is a command line program optimised for importing / exporting data from sql server to a text file. BCP is a lot faster than DTS is at exporting, and can be run in a Dos Batch file.
The -r option lets you specify what you want to end each row with, and by default it uses a \n (newline character / same thing as char(10) ).
example:
bcp "select * from orders" queryout "Jane's Orders.txt" -c -T
Julian Kuiters
juliankuiters.id.au
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply