export to text file with char(13)

[chaoma]

Hello,

I need to export this table to a text file. Here is how the table was created:

Select row_id+
column1+
column2+
char(13)+
row_id+
column3
from my table

With Char(13), it should split the data into 2 rows per record. However, after I export the table using DTS, it showed only one long row per record. What am I doing wrong? Was using Char(13) incorrect?

Note that row_id showed twice.

Thanks.

 

[pjw001]

I think that char(13) is carriage return.

Try char(10) which is newline.

But it could be that DTS is changing the data.

 

[chaoma]

Char(10) did not help. Thank pjw001. Anymore idea?

 

[philhege]

Your SELECT statement results in an unnamed field containing a carriage return.

Exporting a table to a text file creates a text representation of the table, meaning that each row has the same number of columns. This is obviously not what you're after, since you want one row with three fields and the next row with only two fields.

You'll have to write some script in an export routine that writes out the results as you want them, or come up with a smokin' T-SQL routine that creates the table in the desired format.

 

[chaoma]

I guess I have to write my code a different way. Thanks.