Skip to Content.
Sympa Menu

freetds - Re: [freetds] setting connection options in freebcp

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Constantin Vasilyev <vasilyev AT ncbi.nlm.nih.gov>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] setting connection options in freebcp
  • Date: Wed, 20 Sep 2006 15:30:44 -0400

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):

use testdb
go
-- create base table
set ansi_nulls on
go
create table foo(id int identity primary key, data int not null)
go

-- create indexed view
set ansi_nulls, quoted_identifier on
go
create view bar with schemabinding as
select data, count_big(*) cnt_id from dbo.foo group by data
go
set concat_null_yields_null, ansi_warnings, ansi_padding, arithabort on
create unique clustered index uci_data on bar(data)
go




> 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.

dbsqlexec failed




NOEXPAND hint here required to take advantage of the index created on a view. See article "Using Indexes on Views" here
http://doc.ddart.net/mssql/sql2000/html/createdb/cm_8_des_06_0l9v.htm
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 much.



James K. Lowden wrote:
Constantin Vasilyev wrote:

Could it be added, then. I'm not sure if this worth submitting the
patch.

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
trick.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds




Archive powered by MHonArc 2.6.24.

Top of Page