Re: error running freebcp on windows connecting to sql server db

-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of a g
Sent: Wednesday, May 02, 2012 10:20 AM
To: [email protected]
Subject: Re: [freetds] error running freebcp on windows connecting to sql server db

James K. Lowden <jklowden <at> freetds.org> writes:

> 
> On Fri, 30 Mar 2012 01:21:21 -0400
> a g <mewalig <at> gmail.com> wrote:
> 
> >  Instead, I get an error message saying that I attempted to copy an
> > oversized row and the copy failed, but the copy actually will have
> > inserted a row of jumbled data. 
> 
> You are undoubtedly not describing the file correctly to freebcp, or
> the file does not match the table.  That message usually means freebcp
> didn't recognize the EOL as a row terminator.  
> 
> > I compiled ... with both nmake and with mingw
> 
> Since you're running in Windows, you need to be sure 
> 
> 1.  What the line ending is in the file, probably 0x0D0A
> 2.  That you express that correctly to freebcp
> 
> Logically, because cmd.exe doesn't recognize '\' as an escape
> character, "freebcp .... -t \r\n" should work.  YYMV if you're using
> another shell.  
> 
> For BCP the server requires the client to format the data, and to
> format them correctly.  Sometimes if the client gets it "sort of
> right" the server will place the data as received into the table.
> >From the server's point of view, the data were correct insofar as they
> didn't violate any of the rules of the table.  On retrieval the data
> are "jumbled" because there were not placed according to expectation.  
> 
> Could freebcp do a better job of working this out, of making sure the
> "line" read from the file corresponds to a row defined in the target
> table?  Yes, probably.  Certainly it can count columns, and certainly
> it's odd to find a file with "one giant line" because the line ending
> was incorrectly specified.  I don't think inferring a line ending would
> be so bad or so hard.  Just a SMP.  
> 
> HTH.  
> 
> --jkl
> 

Thanks for the reply. I didn't see it for a while and just got to try it out. 
Unfortunately, no luck. Here's what I did:

0. Environment:
- mingw + msys with gcc 4.6.2
- mingw-get install libiconv libiconv-dev
(plus a few other stock mingw libraries)

1. compiled freetds-0.91RC2 from source with the following command:
  ./configure --prefix=/mingw --enable-static --disable-shared --with-libiconv-
prefix=/mingw

2. on sql server, create a table using "create table testTable (f1 varchar
(10));"

3. create a file inputFile1.csv with the following, using LF line-end:
xyz\n
abc
  
4. create a file inputFile2.csv with the following, using CRLF line-end:
xyz\r\n
abc

5. create a file inputFile3.csv with the following, using LF line-end:
xyz\n
abc\n
  
6. create a file inputFile4.csv with the following, using CRLF line-end:
xyz\r\n
abc\r\n

7. Tried to import using:
 freebcp.exe myserver.myschema.testTable in inputFile.csv -c -F 1 -t , -U 
u813487 -P - -SPLMR  -I freetds.conf -h FIRE_TRIGGERS

and got: 
  bcp_init must be called before any other bcp routines. db-lib: Unable to set 
hint "FIRE_TRIGGERS"
   - Note: I submitted a patch for this issue about a year ago through Bill, 
and I think the patch made it into the official source, but as I only tested 
it on Linux, perhaps it doesn't work under Windows.

8. after removing the "-h FIRE_TRIGGERS" option, I tried the same command 
using the following variations
  a. -r \r\n
  b. -r \r
  c. -r \n
  d. no -r option

Results were as follows:
  i) freebcp.exe dbserver.schema.testTable in inputFile1.csv -c -F 1 -t , -U 
userid -P password -Sserver -I freetds.conf
    a and b. error: file contains only 0 rows
    c and d. 1 row: "abcxyz"
  ii) same command with input2.csv
    a and b. error: file contains only 0 rows
    c and d. 1 row: "abcxyz\r\n"
  iii) same command with input3.csv
    a and b. error: file contains only 0 rows
    c and d: error: Attempt to bulk copy an oversized row to the server
  iv) same command with input4.csv. same results as for input3.csv

If I add any additional rows of data to input3 or input4, I get the same 
results as I got for input3/input4. Any suggestions?

Regards,

a g

a g --

Have you tried using format files ?

For example:

#################### lf.fmt ###################
7.0
1
1    SYBCHAR 0    10   "\n"         1 f1
#################### lf.fmt ###################
#################### crlf.fmg #################
7.0
1
1    SYBCHAR 0    10   "\r\n"       1 f1
#################### crlf.fmg #################

then invoke freebcp as:

freebcp.exe dbserver.schema.testTable \
            in inputFile3.csv         \ 
            -f lf.fmt                 \
            -U userid                 \
            -P password               \
            -S server                 \
            -I freetds.conf

or

freebcp.exe dbserver.schema.testTable \
            in inputFile4.csv         \ 
            -f crlf.fmt               \
            -U userid                 \
            -P password               \
            -S server                 \
            -I freetds.conf

-- kjh