Format File - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Format File

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 13:54:43

What a pain in the A_S

Did I mention I hate format files...What's wrong with the following..

It's telling me:

Invalid collation name for source column 1

For the format file:


8.0
1
1	SQLCHAR	0	7500	"\r\n"	1	dataRow	SQL_Latin1_General_CP1_CI_AS

Also If the file is only 80 bytes wide, can I still specify a length of 7500?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 14:04:03

Well it looks like a valid collation (esp since I grabbed from the script of the table...)

So that's not it.

The table has three columns, but the data file has 1.

Do I need to specify the three columns with a server order of 0?

But that doesn't make any sense because they're not in the data file

aaaaaaaaaaaaaaaaaaaaaaahhhhhhhhhhhhhhhhhhhhh

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 14:05:51

You BCP junkies ... always trying to make life harder for yourselves ...

Why specify collation at all?

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 14:09:40

AND...I'm trying to generate it in interactive mode but keep getting errors...

why is this so painful...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 14:10:39

quote:

You BCP junkies ... always trying to make life harder for yourselves ...

Why specify collation at all?

Jonathan
{0}

It's just for 1 column. I took it out and it still yelled at me.

I think you need to specify it now in 2k

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 14:17:03

The table has three columns? Then you will have to specify all three in the format file.

I just checked BOL and it does look like collation is mandatory, my bad. I hardly ever use BCP.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 14:29:53

Yup, that's it...

But When I do


8.0
3
1	SQLCHAR	0	80	"\r\n"	1	DataRow	SQL_Latin1_General_CP1_CI_AS
2	SQLCHAR	0	80	""	0	BatchId	SQL_Latin1_General_CP1_CI_AS
3	SQLCHAR	0	80	""	0	RowId	SQL_Latin1_General_CP1_CI_AS

I get 0 rows affected on the BULK INSERT

And if I do



8.0
3
1	SQLCHAR	0	80	""	1	DataRow	SQL_Latin1_General_CP1_CI_AS
2	SQLCHAR	0	80	""	0	BatchId	SQL_Latin1_General_CP1_CI_AS
3	SQLCHAR	0	80	"\r\n"	0	RowId	SQL_Latin1_General_CP1_CI_AS

I get 1 row affected...like it took the 5 rows and slammed all the rows as 1.

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 14:38:00

Looks like you have the "\r\n" in the wrong place, try putting it in row 1 vice row 3.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 14:43:32

quote:

Looks like you have the "\r\n" in the wrong place, try putting it in row 1 vice row 3.

Jonathan
{0}

Jonathan,

Look a little closer...

Let's get crazy and try all 3

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-16 : 14:49:36

I'm down with crazy ...

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 14:54:38

Here's the data file:


H	 07/04/2003Header Record For Test File
D07/04/2003 APPLES         1.00
D07/04/2003 PEACHES 10000000.00
D07/04/2003 PEACHES 10000000.00
T	 07/04/2003Trailer Record For Test File  00000000003

And with \r\n 3 times, it actually just loaded the header row only (I think)

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-16 : 14:58:37

What a

TOTAL
TOTAL
TOTAL
TOTAL
TOTAL

SCRUB AM I



8.0
3
1	SQLCHAR	0	7500	"\r\n"	1	DataRow	SQL_Latin1_General_CP1_CI_AS
2	SQLCHAR	0	0	""	0	BatchId	SQL_Latin1_General_CP1_CI_AS
3	SQLCHAR	0	0	""	0	RowId	SQL_Latin1_General_CP1_CI_AS

Jonathan, thanks for all your help.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources