What's wrong with this format file? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

What's wrong with this format file?

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-17 : 14:23:23

When I try a BULK INSERT


BULK INSERT Year_End..XLAT_Company FROM 'd:\data\tax\XLAT\XLAT_Company.csv' 
WITH (FORMATFILE = 'd:\data\tax\XLAT\XLAT_Company.fmt')

I get the message

quote:

Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 13
in format file 'd:\data\tax\XLAT\XLAT_Company.fmt'.

Which is waaaaaaay more than bcp was telling me...

But what's wrong with this?


8.0
13 
1  	SQLCHAR	0	0	","	1  	ETRSCompanyCode	SQL_Latin1_General_CP1_CI_AS
2  	SQLCHAR	0	0	","	2  	TaxDBPSId	SQL_Latin1_General_CP1_CI_AS
3  	SQLCHAR	0	0	","	3  	CleanUpPSId	SQL_Latin1_General_CP1_CI_AS
4  	SQLCHAR	0	0	","	4  	ETRSEIN		SQL_Latin1_General_CP1_CI_AS
5  	SQLCHAR	0	0	","	5  	CleanupEIN	""
6  	SQLCHAR	0	0	","	6  	TaxDBEIN	SQL_Latin1_General_CP1_CI_AS
7  	SQLCHAR	0	0	","	7  	ETRSName	SQL_Latin1_General_CP1_CI_AS
8  	SQLCHAR	0	0	","	8  	CleanupName	SQL_Latin1_General_CP1_CI_AS
9  	SQLCHAR	0	0	","	9  	TaxDBName	SQL_Latin1_General_CP1_CI_AS
10 	SQLCHAR	0	0	","	0  	Created_By	SQL_Latin1_General_CP1_CI_AS
11 	SQLCHAR	0	0	","	0  	Created_TS	""
12 	SQLCHAR	0	0	","	0  	Updated_By	SQL_Latin1_General_CP1_CI_AS
13 	SQLCHAR	0	0	"\r\n"	0  	Updated_Ts	""

Brett

8-)

mfemenel
Professor Frink

1421 Posts

Posted - 2003-12-17 : 14:49:00

Wouldn't that make you think you'd need to add: SQL_Latin1_General_CP1_CI_AS
to your last column instead of ""

Mike
"oh, that monkey is going to pay"

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-17 : 15:07:05

When I bcp out and generate the fmt file, it does that...

also when I script the table, there is no collation for these columns...

Guess I have to read up on collation (all I know it's a royal pain in the ass)...

I've tried it 7 way to sunday...And I've added the collations as you suggested...to no avail...

Table DDL


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XLAT_Company]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XLAT_Company]
GO

CREATE TABLE [dbo].[XLAT_Company] (
	[ETRSCompanyCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TaxDBPSId] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CleanUpPSId] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ETRSEIN] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CleanupEIN] [float] NULL ,
	[TaxDBEIN] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ETRSName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CleanupName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TaxDBName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Created_By] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Created_TS] [datetime] NULL ,
	[Updated_By] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Updated_TS] [datetime] NULL 
) ON [PRIMARY]
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-17 : 15:15:07

OH MY GOD

All I had to do was hit Enter so there was a carriage return after the last line...

WHAT A PIECE OF GARBAGE....

Brett

8-)

mm11683
Starting Member

1 Post

Posted - 2005-08-15 : 14:57:31

How do you get the file that is 3.4Gig open to add the charage return?

MuadDBA

628 Posts

Posted - 2005-09-02 : 14:08:14

If your format file was 3.4GB, you've got other problems to worry about than how to open/edit it.

Brett,

I've run into this problem before, and I had a similar reaction. Hard to beleive they don't mention it anywhere in the books online when they tell you all about creating the format files, isn't it?

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-02 : 14:16:50

Wow, that's one for the history books.

It's not the data file that had the problem, it's the format file.

Are you trying to load a 3.4GB file and have a problem?

That's a big file.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

santosh_ksharma
Starting Member

2 Posts

Posted - 2009-06-24 : 02:41:25

quote:
Originally posted by X002548

OH MY GOD

All I had to do was hit Enter so there was a carriage return after the last line...

WHAT A PIECE OF GARBAGE....

Brett

8-)

We learn till we die.
Santosh Kumar Sharma

santosh_ksharma
Starting Member

2 Posts

Posted - 2009-06-24 : 02:43:23

Brett,
You are absolutely right. The enter did worked for me.
You are simply great. I'm wondering how you arrived at this solution.
Thanks a lot.

We learn till we die.
Santosh Kumar Sharma

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources