Re: Unable to bcp file into MS SQL Server 2008

On 31 January 2010 07:38, James K. Lowden <[email protected]> wrote:
> Srdan Dukic wrote:
>> I've been looking at the freebcp man
>> page[1] but can't really see any option or example of how to specify
>> that any identity fields should be ignored.
>
> This looks like pilot error to me.  The attached script works on my
> server.
>
> To verify, set TDSDUMP and grep the log file for "bcp_control".  You'll
> see several calls; look at the second parameter:
>
> $ grep bcp_control /tmp/freetds.log.9501
> bcp.c:553:bcp_control(0x8053000, 2, 0)
> bcp.c:553:bcp_control(0x8053000, 3, 0)
> bcp.c:553:bcp_control(0x8053000, 1, 10)
> bcp.c:553:bcp_control(0x8053000, 4, 1000)
>
> These values are defined in include/sybdb.h.  If you're inserting values
> in an IDENTITY column from the datafile, you will see bcp_control called
> with a parameter value of 8 (BCPKEEPIDENTITY) and the query "set
> identity_insert %s on" sent to the server.
>
> If you don't see those things in the log file, the server should assign
> values for you.  Your datafile still needs to have something in the column
> -- a placeholder for the parser -- but the server will override it.  As
> you found (and my script demonstrates), file must have a value for the
> column; it can't be NULL.  That's a misfeature of freebcp; there's no
> reason it couldn't discover any identity columns in the target table and
> provide a zero value if the file indicates a NULL.
>
>> http://linux.die.net/man/1/freebcp
>
> That's our manpage alright, passed through a strange anti-English filter.
> Your distribution should have come with its own manual.
>
> == snip ==
> #! /bin/sh
> set -e
> bsqldb -q -S$S -U$U -P$(password $S $U) <<SQL
> create table test_id
>        ( id int IDENTITY
>        , name char(10) not NULL
>        , primary key (id)
>        )
> insert test_id (name) values ('one')
> insert test_id (name) values ('two')
> insert test_id (name) values ('three')
> go
> SQL
>
> freebcp test_id out test_id.dat -S$S -U$U -P$(password $S $U) -c
>
> cat test_id.dat
> sed -E 's/^[0-9]+//' test_id.dat > test_id.null.dat
>
> freebcp test_id in test_id.dat -S$S -U$U -P$(password $S $U) -c
> freebcp test_id in test_id.null.dat -S$S -U$U -P$(password $S $U) -c
>
> bsqldb -S$S -U$U -P$(password $S $U) <<SQL
> select * from test_id order by id
> drop table test_id
> go
> SQL
> == pins ==
>
> Output:
> jklowden@oak[tests]$ ./bcp.id.sh
> Starting copy...
> 3 rows copied.
> 1       one
> 2       two
> 3       three
>
> Starting copy...
> 3 rows copied.
>
> Starting copy...
> Msg 20073, Level 2
> Attempt to bulk copy a NULL value into a Server column which does not
> accept null values
>
> Msg 20073, Level 2
> Attempt to bulk copy a NULL value into a Server column which does not
> accept null values
>
> Msg 20073, Level 2
> Attempt to bulk copy a NULL value into a Server column which does not
> accept null values
>
> 0 rows copied.
>         id  name
> -----------  ----------
>          1  one
>          2  two
>          3  three
>          4  one
>          5  two
>          6  three
> 6 rows affected
>
> --jkl


Hi,

Thank you for the test script. It allowed me to diagnose the problem
and figure out where the error was. Ultimately the problem was with
the tables in SQL Server.

The tables had previously been replicated from another SQL Server and
it seems that once the replication had been removed, there remained
some kind of setting which meant that primary keys (whether set to
'0', '1', NULL, '' etc....) weren't allowed to be inserted into the
database. The solution was to just rename the tables and create new
tables with the same name and structure.

Turns out to have been a rather obscure error, happy to have it figured out :)

Thank you
-- 
Srđan Đukić
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds