I need to export my data using DTS into a csv text file but with fixed
record length

ie: "data ","more data ","data "

and so on - is there an easy way to do this? - when you setup the
destination text file it appears to be a choice between one and the other.

Regards

James Bevan

What are you coming from ? SQL Server
What is the definition of the Source ?

Here are some examples

(ANSI_PADDING is ON)

CREATE TABLE SourceCSVFixed_1(col1 char(5), col2 char(10))
INSERT SourceCSVFixed_1(col1,col2) VALUES('H','EE')


CREATE TABLE SourceCSVFixed_2(col1 varchar(5), col2 varchar(10))
INSERT SourceCSVFixed_2(col1,col2) VALUES('H','EE')


select col1, len(col1),datalength(col1) from SourceCSVFixed_1

select col1, len(col1),datalength(col1) from SourceCSVFixed_2

When I create a pump to a text file using SourceCSVFixed_1 then the values
are padded to the length of the string
When I create a pump to a text file using SourceCSVFixed_2 then the values
are NOT padded to the length of the string


Notes from BOL

ANSI_PADDING

When set to ON, trailing blanks in character values inserted into varchar
columns and trailing zeros in binary values inserted into varbinary columns
are not trimmed. Values are not padded to the length of the column. When set
to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are
trimmed. This setting affects only the definition of new columns.

Char(n) and binary(n) columns that allow nulls are padded to the length of
the column when SET ANSI_PADDING is set to ON, but trailing blanks and zeros
are trimmed when SET ANSI_PADDING is OFF. Char(n) and binary(n) columns that
do not allow nulls are always padded to the length of the column.



Important It is recommended that ANSI_PADDING always be set to ON. SET
ANSI_PADDING must be ON when creating or manipulating indexes on computed
columns or indexed views.

The status of this option can be determined by examining the
IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.


Example 2 is where you can determine the level of padding yourself so let's
take the SourceCSVFixed_2 table again and pad

This is in the SourceSQL Statement of your datapump task instead of choosing
to take over a whole table

select
col1 + Replicate(' ',2) as Col1,
col2 + Replicate(' ',6) as Col2
from
SourceCSVFixed_2


--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Adept Systems (Hull) Ltd" <JamesB (AT) adept-systems (DOT) co.uk> wrote

i was given the following and it sounds like it will do what i need

SELECT
""+CONVERT(char(20)Extension)+"','"+CONVERT(char(9 )Duration"','"+CONVERT(cha
r(10)Cost)+"','"+CONVERT(char(20)ClipNumber)+"','" +CONVERT(char(20)DialledDi
git)""
FROM SMDROUT

but i get the error
Error description : deferred prepare could not be completed
statements could not be prepared
cannot use empty or column names use a single space if necessary
line 1: incorrect syntax near extension

I'm sure this is a really basic mistake, if so then my appologies

Regards

James Bevan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

CONVERT(CHAR(20),Extension)

You missed the ,

Use CAST instead

CAST(Extension as char(20))


--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Adept Systems (Hull) Ltd" <JamesB (AT) adept-systems (DOT) co.uk> wrote

thanks - that worked fine
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote