BUG: bulk copy into table with computed columns fails and also reports incorrect number of rows copied

[liquidaty]

The core issue here stems from default ANSI_NULLS and other options that are incompatible with bulk copy into tables with computed columns. Providing a means to change these from their current defaults is the only way to allow copy into a table with computed columns (see https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16)

To reproduce:

1. Create a DB table with one or more calculated columns e.g.:

CREATE TABLE mytest(
	f1 integer,
	f2 as  (f1+1) PERSISTED
)

2. Create a test file:

echo '1,1' > test.csv

3. Attempt to BCP:

freebcp mytest in test.csv -S xxx -U xxx -P xxx -D xxx -c -t ,

This gives the following message, including the inaccurate one about a row being copied (in reality, no rows are copied):

Starting copy...
Msg 1934, Level 16, State 1
Server 'EC2AMAZ-KR4REND', Line 1
	INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 20018, Level 16
General SQL Server error: Check messages from the SQL Server

1 rows copied.

Solution:

This issue can be solved using free-form SET commands:

            const char *set2017defaults =
              "SET ANSI_NULLS ON;"
              "SET QUOTED_IDENTIFIER ON;"
              "SET CONCAT_NULL_YIELDS_NULL ON;"
              "SET ANSI_PADDING ON;"
              "SET ANSI_WARNINGS ON;"
              "SET ARITHABORT ON;"
              "SET NUMERIC_ROUNDABORT OFF;"
              ;
            if(dbcmd(dbproc, set2017defaults) == FAIL || dbsqlexec(dbproc) == FAIL)
            fprintf(stderr, "Unable to set default options\n");
          else {
            dbcancel(data.dbproc);

            // continue with bulk load
          }

Proposed change:

Although a workaround as shown above can be used, it would be better if these settings a) were default and b) could be modified in a manner similar to the bcp_control KEEPIDENTITY option. I would be happy to contribute a pull request reflecting the above. I would be happy to submit a PR if helpful

[freddy77]

That's weird that this happens only with computed columns!
The issue here is that freebcp uses DB-library which, for compatibility, use the old settings.
bulk.c is not the right place to change these defaults, I think a better option would be in freebcp itself. You don't want to mess around with possible other options set by the user. To my surprise there's no option for our DB-library to say "I'm an ODBC driver" (it could sounds weird, but it's be way to have current options during the login which is the default of libTDS but turned off by DB-library).