Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Posts
    3

    Unhappy Unanswered: Problems using .fmt file

    I receive the following error msg when trying to do a Bulk Copy:

    Server: Msg 4839, Level 16, State 1, Line 1
    Cannot perform bulk insert. Invalid collation name for source column 22 in format file 'D:\FormatFiles\ConversionBuilder.fmt'.

    the format file is as follows:

    8.0
    26
    1 SQLDATETIM4 0 24 "|~|" 1 ReportDate ""
    2 SQLINT 0 12 "|~|" 2 AdvertiserId ""
    3 SQLCHAR 0 1000 "|~|" 3 AdvertiserName SQL_Latin1_General_CP1_CI_AS
    4 SQLINT 0 12 "|~|" 4 OrderId ""
    5 SQLCHAR 0 2000 "|~|" 5 OrderName SQL_Latin1_General_CP1_CI_AS
    6 SQLINT 0 12 "|~|" 6 AdId ""
    7 SQLCHAR 0 2000 "|~|" 7 AdName SQL_Latin1_General_CP1_CI_AS
    8 SQLINT 0 12 "|~|" 8 SiteId ""
    9 SQLCHAR 0 1000 "|~|" 9 SiteName SQL_Latin1_General_CP1_CI_AS
    10 SQLFLT8 0 30 "|~|" 10 CPM ""
    11 SQLINT 0 12 "|~|" 11 Impressions ""
    12 SQLINT 0 12 "|~|" 12 Clicks ""
    13 SQLINT 0 12 "|~|" 13 Conversions ""
    14 SQLFLT8 0 30 "|~|" 14 ClickRate ""
    15 SQLINT 0 12 "|~|" 15 ConversionRate ""
    16 SQLINT 0 12 "|~|" 16 Cost ""
    17 SQLINT 0 12 "|~|" 17 CPABannerSite ""
    18 SQLINT 0 12 "|~|" 18 CPABanner ""
    19 SQLINT 0 12 "|~|" 19 ImpressionsLife ""
    20 SQLINT 0 12 "|~|" 20 ClicksLife ""
    21 SQLINT 0 12 "|~|" 21 ConversionsLife ""
    22 SQLFLT8 0 30 "|~|" 22 Click RateLife ""
    23 SQLFLT8 0 30 "|~|" 23 Conversion RateLife ""
    24 SQLINT 0 12 "|~|" 24 CostLife ""
    25 SQLFLT8 0 30 "|~|" 25 CPABannerSiteLife ""
    26 SQLFLT8 0 30 "\r\n" 26 CPABannerLife ""


    the input file (test.txt) is as follows:

    21-APR-2002|~|3612|~|Columbia House, Inc.|~|949930|~|Pops 2_15-3_15_2002|~|3954255|~|Pop unders- 700x300 conversion|~|193940|~|Adtegrity.com, Inc. (Entertainment)|~|2|~|0|~|1|~|0|~|0|~|0|~|0|~|0|~| 0|~|348793|~|13208|~|0|~|3.78677|~|0|~|0|~|0|~|0


    the bulk copy procedure is as follows:

    BULK INSERT Conversion_Dump_Daily
    FROM 'd:\datasources\conversionbuilder\test.txt'
    WITH
    (
    FORMATFILE='D:\FormatFiles\ConversionBuilder.fmt'
    )

    when I do a "sp_help "Conversion_Dump_Daily", the collation name returned on the varchar fields is: "SQL_Latin1_General_CP1_CI_AS"


    Can anyone help me?

  2. #2
    Join Date
    Apr 2002
    Posts
    3
    Sorry everyone.... I just found the problem. I had a space in two of the "server column name"s. It only took me 5 hours to see it.... Daa!

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Dont beat yourself up, I am sure all of use have burned many hours staring at the trees wondering just where the heck the forrest was!

    In the end it sharpens your detective skills.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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