Skip to Content.
Sympa Menu

freetds - Re: [freetds] error running freebcp on windows connecting to sql server db

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Konrad Hambrick <konrad AT payplus.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] error running freebcp on windows connecting to sql server db
  • Date: Wed, 2 May 2012 18:26:23 +0000

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of a g
Sent: Wednesday, May 02, 2012 10:20 AM
To: freetds AT lists.ibiblio.org
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




Archive powered by MHonArc 2.6.24.

Top of Page