freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: [freetds] Unable to bcp file into MS SQL Server 2008
- 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
-
[freetds] Unable to bcp file into MS SQL Server 2008,
Srdan Dukic, 01/28/2010
-
Re: [freetds] Unable to bcp file into MS SQL Server 2008,
Robert Hetzel, 01/28/2010
-
Re: [freetds] Unable to bcp file into MS SQL Server 2008,
Srdan Dukic, 01/29/2010
-
Message not available
-
Re: [freetds] Unable to bcp file into MS SQL Server 2008,
Srdan Dukic, 01/29/2010
- Re: [freetds] Unable to bcp file into MS SQL Server 2008, James K. Lowden, 01/30/2010
-
Re: [freetds] Unable to bcp file into MS SQL Server 2008,
Srdan Dukic, 01/29/2010
-
Message not available
-
Re: [freetds] Unable to bcp file into MS SQL Server 2008,
Srdan Dukic, 01/29/2010
-
Re: [freetds] Unable to bcp file into MS SQL Server 2008,
Robert Hetzel, 01/28/2010
Archive powered by MHonArc 2.6.24.