Skip to Content.
Sympa Menu

freetds - Re: [freetds] Having trouble doing a BULK INSERT

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <james.k.lowden AT alliancebernstein.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Having trouble doing a BULK INSERT
  • Date: Mon, 6 Nov 2006 17:49:13 -0500

> From: Wes Gamble
> Sent: Monday, November 06, 2006 3:10 PM
>
> I have gotten past my issue with locating the file. However, now
> I get an error inserting the data because of the primary key column.
>
> Here is a sample line from my data file, XXXX.upload:
>
> 832 865 4766||Wes
>
Gamble||G||||Great||||||||||||||wesgamble AT yahoo.com|||||||||||||||||||57
192|
>
> The last column in this table (UniqueID) is the primary key (a
> legacy table, I don't wanna hear about why the last column is the
> PKEY ;]).
>
> If I run bulk insert on an equivalent file on Windows through ODBC,
> it's fine. It replaces the NULL value in the file with a new
> primary key value in the database.
>
> But when I issue the following command:
>
> 1> BULK INSERT FAXNET FROM
> 'D:\FaxServe\upload_list_data\XXXX.upload' WITH (FIELDTERMINATOR='|')
>
> via tsql, I get
>
> "Msg 4864, Level 16, State 1, Server DBSERVE, Line 1
> Bulk insert data conversion error (type mismatch) for row 1, column
> 43 (UniqueID). (return status = -6)"

[I see from your latest message you "solved" this puzzle for a trivial
table. I had already done some investigation, and thought it could be
of interest even though it might not bear on the problem you're trying
to solve.]

I'm not very familiar with BULK INSERT. It's interesting that it should
behave differently depending on what *client* you use. (You said
"equivalent" file. Do you mean *same* file?)

When you connect with an ODBC client, Microsoft servers automatically
set certain connection options. Try these commands on your Windows box:

$ osql -E -S yourserver -Q"select @@options" (I get 5176)
$ isql -E -S yourserver -Q"select @@options" (I get 0)

First question would be, does BULK INSERT work for you using Microsoft's
ISQL?

Then try:

$ printf "select @@options\ngo\n" | tsql -Smpquant -U$U -P$P
locale is "C"
locale charset is "646"
1> 2>
5432
$ printf "select @@options\ngo\n" | bsqldb -Smpquant -U$U -P$P

-----------
0
1 rows affected
$ printf "select @@options\ngo\n" | sqsh -Smpquant -U$U -P$P

-----------
5432

The unixODBC isql also returns 5432.

The defaults vary by server, database, and connection. YMMV. I've
attached a Perl script to parse the options.

On my server, "select @@options" on tsql yields 5432, or:

ANSI_NULLS
ANSI_NULL_DFLT_ON
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
ANSI_PADDING

To that list, Query Analyzer adds ARITHABORT; osql removes
QUOTED_IDENTIFIER. (That's interesting. tsql doesn't set any options,
it just identifies itself to the server as an ODBC client.)

I don't see how any of these things can affect BULK INSERT, but the
options are the only thing I can think of that would differ. The server
doesn't know what client you're using. It knows only whether the "I'm
an ODBC client" bit was set on in the login packet, and the other
options set (possibly automatically by the client) since the connection
was formed.

Anyway, if you can get "select @@options" to return the same value (cf.
the SET command) with tsql that you get with Query Analyzer, I'm sure
that would help you and be interesting to others.

--jkl

== Perl ==
#! /usr/bin/perl -w

use File::Basename;

%options = ( 1 => DISABLE_DEF_CNST_CHK
# Controls interim or deferred constraint checking.
, 2 => IMPLICIT_TRANSACTIONS #
Controls whether a transaction is started implicitly when a statement is
executed.
, 4 => CURSOR_CLOSE_ON_COMMIT #
Controls behavior of cursors after a commit operation has been
performed.
, 8 => ANSI_WARNINGS
# Controls truncation and NULL in aggregate warnings.
, 16 => ANSI_PADDING
# Controls padding of fixed-length variables.
, 32 => ANSI_NULLS
# Controls NULL handling when using equality operators.
, 64 => ARITHABORT
# Terminates a query when an overflow or divide-by-zero error occurs
during query execution.
, 128 => ARITHIGNORE
# Returns NULL when an overflow or divide-by-zero error occurs during a
query.
, 256 => QUOTED_IDENTIFIER
# Differentiates between single and double quotation marks when
evaluating an expression.
, 512 => NOCOUNT # Turns
off the message returned at the end of each statement that states how
many rows were affected.
, 1024 => ANSI_NULL_DFLT_ON
# Alters the session's behavior to use ANSI compatibility for
nullability. New columns defined without explicit nullability are
defined to allow nulls.
, 2048 => ANSI_NULL_DFLT_OFF
# Alters the session's behavior not to use ANSI compatibility for
nullability. New columns defined without explicit nullability are
defined not to allow nulls.
, 4096 => CONCAT_NULL_YIELDS_NULL #
Returns NULL when concatenating a NULL value with a string.
, 8192 => NUMERIC_ROUNDABORT
# Generates an error when a loss of precision occurs in an expression.
, 16384 => XACT_ABORT # Rolls
back a transaction if a Transact- SQL statement raises a run-time error.

);

$syntax = qq(syntax: basename($0) option-value (as decimal)\n);

$option = $ARGV[0];
die $syntax unless $option;

while ( ($mask, $name) = each %options ) {
print "$name\n"
if( 0+$option & 0+$mask );
}

__END__

=head1 SYNOPSIS

verbose_options option-value

=head1 DESCRIPTION

Print list of options as defined by "select @@OPTIONS" on a Microsoft
server.
The option argument is provided as a decimal string representation.

=head1 EXAMPLE

$ printf "select @@options\ngo\n" \
| tsql -Smpquant -U$U -P$P \
| grep '^[0-9]' \
| grep -v '>' \
| xargs ./verbose_options.pl
ANSI_NULLS
ANSI_NULL_DFLT_ON
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
ANSI_PADDING

=cut
== end Perl ==


-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent
responsible
for delivering this message to the intended recipient, any review,
dissemination,
distribution or duplication of this communication is strictly prohibited. If
you are
not the intended recipient, please contact the sender immediately by reply
e-mail
and destroy all copies of the original message. Please note that we do not
accept
account orders and/or instructions by e-mail, and therefore will not be
responsible
for carrying out such orders and/or instructions. If you, as the intended
recipient
of this message, the purpose of which is to inform and update our clients,
prospects
and consultants of developments relating to our services and products, would
not
like to receive further e-mail correspondence from the sender, please "reply"
to the
sender indicating your wishes. In the U.S.: 1345 Avenue of the Americas, New
York,
NY 10105.




Archive powered by MHonArc 2.6.24.

Top of Page