Skip to Content.
Sympa Menu

freetds - Re: [freetds] Unable to bcp file into MS SQL Server 2008

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Unable to bcp file into MS SQL Server 2008
  • Date: Sat, 30 Jan 2010 13:38:32 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page