Post reply

BULK INSERT Error

  • August 17, 2007 at 3:35 pm

    #120174

    Hello everybody,

    I have a table 'online_batch14' with the following structure :

    SerialNumber Varchar(20), Comments VarChar(50), batch int

    I have created the following formatfile :

    8.0

    2

    1 SQLCHAR 0 20 "\t" 1 SerialNumber ""

    1 SQLCHAR 0 30 "\t" 2 Comments ""

    I have the data file as shown below :

    5000101018001772486 

    5000101018001772484 

    58115120060700690855 entered manually

    58115120060700690459 entered manually

    58115120060700685905 entered manually

    58115120060700685509 entered manually

    58115120060700684915 entered manually

    58115120060700684519 entered manually

    58115120060700681945 entered manually

    5000101018001832054 

    5000101018001832053 

    I am using the following command to import this file into the table :

    bulk insert online_batch14 from 'C:\Amit\Online\batch14\online_test_batch14.txt'

    WITH

          (

      FORMATFILE = 'C:\Amit\Online\Online.fmt'

          )

    I get this error -

    Server: Msg 4820, Level 16, State 1, Line 1

    Could not bulk insert. Unknown version of format file 'C:\Amit\Online\Online.fmt'.

    Any one able to help me on this ?

    Thanks in Advance.

  • August 17, 2007 at 3:45 pm

    #727766

    What version of SQL Server are you running?

  • August 17, 2007 at 3:47 pm

    #727767

    I am on SQL SERVER 2000

     

    Thanks.

  • August 17, 2007 at 3:50 pm

    #727768

    From what I can see, you have the file setup correctly.  Perhaps someone with more knowledge of BCP will see something I am missing.

  • August 17, 2007 at 3:51 pm

    #727769

    From a command prompt, run bcp -v to get your version number.  Please post it.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • August 17, 2007 at 3:55 pm

    #727770

    bcp version is 8.00.194

     

    Thanks.

  • August 17, 2007 at 4:34 pm

    #727773

    'C:\Amit\Online\Online.fmt' - is this file on the server or on you local drive?

    _____________
    Code for TallyGenerator

  • August 17, 2007 at 4:44 pm

    #727774

    With this file :

    8.0

    2

    1 SQLCHAR 2 20 "\t" 1 SerialNumber ""

    2 SQLCHAR 2 50 "\t\n" 2 Comments ""

    and this command :

    exec master..xp_cmdshell 'bcp "TKD01.dbo.online_batch14" in "C:\Amit\Online\batch14\online_test_batch14.txt" -f"C:\Amit\Online\Online.fmt" -U"sa" -P""'

    I get this output :

    output                                                                                                                                                                                                                                                          ---------------------------------

    NULL

    Starting copy...

    NULL

    0 rows copied.

    Network packet size (bytes): 4096

    Clock Time (ms.): total        1

    (6 row(s) affected)

    It does not give any erros, but doesn't copies any rows either ..

  • August 17, 2007 at 4:46 pm

    #727775

    The file is on my local machine - the server is also on my local machine.

     

    Thanks.

     

  • August 17, 2007 at 9:33 pm

    #727789

    Where are the tabs located in the text file?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply