[freetds] 'SET ANSI_WARNINGS OFF' on SQL Server
Craig A. Berry
craigberry at mac.com
Sat May 22 19:30:43 EDT 2010
On May 21, 2010, at 4:44 PM, Gouda, Ravajappa wrote:
> The SET ANSI_WARNINGS OFF on SQL Server, among other things, it
> replaces null values with 'blank' character. It works with BULK-
> INSERT and INSERT statement on SQL Server.
I don't actually see any indication in the docs to SET ANSI_WARNINGS
that this is one of the things it does, but I could be missing
something. How did you form the impression that it will replace nulls
with blanks?
> However, with freebcp I expected it to work with -O option as
> provided in the example below. It does not seem to work, please
> advise?
Setting the option works, but it doesn't have the effect you desire.
The only thing I've seen that does is creating the non-nullable column
with a default value of a blank space. When you bulk in an empty
string, it will, by default, populate the field with space rather than
null if a space is the default value of the field. This behavior can
be suppressed with the KEEPNULLS bulk insert option (-k with the
Microsoft bcp client).
A quick experiment shows that KEEPNULLS is always on with freebcp, or
at least I always get the
Msg 20073, Level 2
Attempt to bulk copy a NULL value into a Server column which does not
accept null values
error even if I do set the default value for the column to a blank
space. I have no idea whether KEEPNULLS is part of the dblib
specification. This will need more investigation.
> I tried with and without format files, tds version 7.0 and 8.0. The
> database server is SQL Server 2005. I am using freebcp version 0.82
> on Linux.
>
>
>
> Version information:
>
> $ /efs/dist/fsf/freetds/0.82-ml02/exec/bin/freebcp -v
>
> usage: freebcp [[database_name.]owner.]table_name {in | out} datafile
>
> [-m maxerrors] [-f formatfile] [-e errfile]
>
> [-F firstrow] [-L lastrow] [-b batchsize]
>
> [-n] [-c] [-t field_terminator] [-r row_terminator]
>
> [-U username] [-P password] [-I interfaces_file] [-S server]
>
> [-v] [-d] [-h "hint [,...]" [-O "set connection_option on|
> off, ...]"
>
> [-A packet size] [-T text or image size] [-E]
>
>
>
> BCP Command:
>
> freebcp dbname..tbname in /tmp/bcpFile.dat -O 'set ansi_warnings
> off' -S SQL_SERVER_NAME -U myself -P secret -c -t\|
>
>
>
>
>
> Further details -
>
> Here is the test table
>
> create table test_table (
>
> col1 varchar(2) not null,
>
> col2 char(5) null,
>
> col3 varchar(10) null
>
> )
>
>
>
> More bcpFile.dat
>
> a|b|c
>
> |b|c ß First column is null should be padded
> with a blank character when inserted into test_table. But that is
> not the case, can someone help?
>
>
>
> Thanks,
>
> Gouda
>
> ----------------------------------------------------------------------
> This message w/attachments (message) is intended solely for the use
> of the intended recipient(s) and may contain information that is
> privileged, confidential or proprietary. If you are not an intended
> recipient, please notify the sender, and then please delete and
> destroy all copies and attachments, and be advised that any review
> or dissemination of, or the taking of any action in reliance on, the
> information contained in or attached to this message is prohibited.
> Unless specifically indicated, this message is not an offer to sell
> or a solicitation of any investment products or other financial
> product or service, an official confirmation of any transaction, or
> an official statement of Sender. Subject to applicable law, Sender
> may intercept, monitor, review and retain e-communications (EC)
> traveling through its networks/systems and may produce any such EC
> to regulators, law enforcement, in litigation and as required by law.
> The laws of the country of each sender/recipient may impact the
> handling of EC, and EC may be archived, supervised and produced in
> countries other than the country in which you are located. This
> message cannot be guaranteed to be secure or free of errors or
> viruses.
>
> References to "Sender" are references to any subsidiary of Bank of
> America Corporation. Securities and Insurance Products: * Are Not
> FDIC Insured * Are Not Bank Guaranteed * May Lose Value * Are Not a
> Bank Deposit * Are Not a Condition to Any Banking Service or
> Activity * Are Not Insured by Any Federal Government Agency.
> Attachments that are part of this EC may have additional important
> disclosures and disclaimers, which you should read. This message is
> subject to terms available at the following link:
> http://www.bankofamerica.com/emaildisclaimer. By messaging with
> Sender you consent to the foregoing.
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
________________________________________
Craig A. Berry
mailto:craigberry at mac.com
"... getting out of a sonnet is much more
difficult than getting in."
Brad Leithauser
More information about the FreeTDS
mailing list