[freetds] setting connection options in freebcp
vasilyev at ncbi.nlm.nih.gov
Wed Sep 20 15:30:44 EDT 2006
Subject changed from "FIRE_TRIGGERS hint"
> I wonder how Microsoft and Sybase deal with that in
> their utilities?
For MS SQL bcp the problem is not solved. The only connection option it
allows to control is QUOTED_IDENTIFIER (-q parameter). It also sets some
options to ON by default, but not ARITHABORT, which makes it impossible
to bcp out of indexed view while taking indexes in account. I'm sure
freebcp can do better than native client here (-;
> Do you have an example of a session setting that
> must be in a separate batch? Because other than that, "queryout"
> should do the trick.
Here's an example which illustrates the problem (for MS SQL):
-- create base table
set ansi_nulls on
create table foo(id int identity primary key, data int not null)
-- create indexed view
set ansi_nulls, quoted_identifier on
create view bar with schemabinding as
select data, count_big(*) cnt_id from dbo.foo group by data
set concat_null_yields_null, ansi_warnings, ansi_padding, arithabort on
create unique clustered index uci_data on bar(data)
> freebcp "select * from testdb.dbo.bar with(noexpand)" queryout
/tmp/bar.bcp -c -S ... -U ... -P ...
Msg 1934, Level 16, State 1
Server 'MSSQL0', Line 1
SELECT failed because the following SET options have incorrect
settings: 'ANSI_NULLS., QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
ANSI_WARNINGS, ANSI_PADDING, ARITHABORT'.
Msg 20018, Level 5
General SQL Server error: Check messages from the SQL Server.
NOEXPAND hint here required to take advantage of the index created on a
view. See article "Using Indexes on Views" here
or in Books OnLine for more details.
Adding "SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
ANSI_WARNINGS, ANSI_PADDING, ARITHABORT ON" into the query doesn't help
James K. Lowden wrote:
> Constantin Vasilyev wrote:
>>Could it be added, then. I'm not sure if this worth submitting the
>>And another idea. Sometime (like in case of pulling data out of indexed
>>views) database connection which freebcp opens requires some options
>>(like ANSI_NULLS) to be set before fetching the data. Some of those
>>options work if submitted within the same batch with SELECT statement,
>>so queryout mode may be used for them, but some work only if placed in
>>separate batch. For such cases it might be useful to add an option to
>>which allows to run some commands on the server (or simply set
>>particular options for the connection) before pumping the data.
> Good ideas, both. I wonder how Microsoft and Sybase deal with that in
> their utilities? Do you have an example of a session setting that must
> be in a separate batch? Because other than that, "queryout" should do the
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
More information about the FreeTDS