Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jllondon
Starting Member
5 Posts |
 Posted - 2004-02-22 : 16:55:17
|
| Hopefully a straightforward DTS question:Is it possible to output carriage return separated content to a text file through DTS? For example:Column01Column02Column03Column01Column02Column03Column01Column02Column03etcAny help greatly appreciated. |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-02-23 : 02:41:30
|
| The "bcp" utility is a command line program that comes with SQL Server specifically for exporting data to text files. It is really convenient to use and beats DTS any day for simple export tasks. bcp lets you specify field and row terminators, which in your case are both the same "\n" (carriage return). Read the Books Online for more information. Example:bcp "SELECT * FROM Orders" out "MyOrders.txt" -c -q -S"servername" -U"Username" -P"password" -t"\n" -r"\n"OS |
 |
|
|
jllondon
Starting Member
5 Posts |
Posted - 2004-02-23 : 13:28:52
|
| Thanks mohdowais.I'm aware of bcp as an option. If anyone has a DTS method I would still be interested in hearing it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-23 : 13:32:46
|
| The DTS designer does not have a carriage return option. If you must use DTS, then export the data to the text file in whatever format DTS can do, then use VBScript to modify the output file. You could also write T-SQL that puts the result set in a correct format then use that query to get the output.I'm sure that you can use a carriage return if you coded DTS through VB.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-23 : 14:34:10
|
| Looks like you really want to create a vertical result set, and you're thinking that you can "break up" the columns with CRLF...Why not just use sql?SELECT Col1FROM myTable99UNION ALLSELECT Col2FROM myTable99SELECT Col3FROM myTable99Is that what you're trying to do?Mind you're datatypes now....Brett8-) |
|
|
|
jllondon
Starting Member
5 Posts |
Posted - 2004-02-24 : 03:58:36
|
| Thanks for those suggestions.The reason for going with DTS is taht everythign is very DTS focussed here, so it would integrate well with the existing setup.I was going to have a look at using SQLDMO, OLE automation stored procedures (sp_OOA...), the Bulk Copy object, and see if I could get that to produce the functionality.If that doesn't work, I'll be using SQL, as suggested by Brett.Thanks for your suggestions chaps. |
|
|
|
|
|
|
carriage return field delimiters through dts