Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: What's wrong with this format file?

    When I try a BULK INSERT

    Code:
    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

    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?

    Code:
    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-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Looks OK to me, naturally. What happens if you put in the same collation name for the timestamp fields?

    I must admit, I am guessing here.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Same message...you know I've got to have tried every combination...

    maybe it's something to do with the BULK INSERT...

    And it's funny I don't think datetime or numeric data have collation

    table ddl

    Code:
    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
    script right from the database..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That'll do it

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Been there ... Welcome to the quirkiness of format files.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah well.. I was using code to generate them...

    I need to add a final union all to add a carrigae return line feed...

    holy crap...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •