FreeTDS Development Group

["Craig A. Berry" <craigberry AT mac.com>]

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