FreeTDS Development Group

[Constantin Vasilyev <vasilyev AT ncbi.nlm.nih.gov>]

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