FreeTDS Development Group

["Lowden, James K" <james.k.lowden AT alliancebernstein.com>]

> From: Constantin Vasilyev
> Sent: Wednesday, September 20, 2006 3:31 PM
> 
>  > 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
> (-;

Nice follow-up, thanks.  I wouldn't have constructed an example like
that in a million years (and I don't expect to live that long).  

I was able to reproduce your example using Microsoft's ISQL.EXE and
BCP.EXE, see below.  The only change was that the objects are owned by
my account, not dbo.  

This would mean a significant change.  freebcp would have to accept an
option (perhaps -O for "option" but since that's easily confused with
zero, perhaps -A for "ANSI", or maybe we accept -0 [zero] too).  These
options would be applied to the connection in a single batch.  Then the
query -- provided by the user with queryout or implied by the object
name -- would be issued as a second batch.  

Parsing (even constructing) such a -O option string is not for the faint
of heart.  Unix has a few ugly examples of comma-delimited-no-whitespace
options; passing linker options to gcc comes to mind.  Maybe -O should
just take SQL text, to be applied (in its own batch) before the
extraction query.  Multiple -O strings would be applied as separate
batches, in the order they appear on the command line.  

Another alternative would be to accept a batch delimiter in the query
text.  A logical choice would be the semicolon.  Then your command would
look like this:

$ QUERY='SET ANSI_NULLS, \
        QUOTED_IDENTIFIER, \
        CONCAT_NULL_YIELDS_NULL, \
        ANSI_WARNINGS, \
        ANSI_PADDING, \
        ARITHABORT ON; \
        select * from testdb..bar with(noexpand)

$ freebcp "${QUERY}" queryout filename [...]

Of course, a delimiter means we'd need an escape mechanism too....

Anyway, it's something to think about.  

--jkl

[== logs ==]
ISQL: 

1> SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
2> ANSI_WARNINGS, ANSI_PADDING, ARITHABORT ON
3> select * from testdb..bar with(noexpand)
4> go
Msg 1934, Level 16, State 1, Server AC2KAMA0848, Line 3
SELECT failed because the following SET options have incorrect settings:
'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,
ANSI_PADDING, ARITHABORT'.
1> exit

BCP:

$ bcp "select * from testdb..bar with(noexpand)" queryout t -T -S
mpquant -c
SQLState = 37000, NativeError = 1934
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed
because the following SET options have incorrect settings: 'QUO
TED_IDENTIFIER, ARITHABORT'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
could not be prepared.

$ bcp "SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
ANSI_WARNINGS, ANSI_PADDING, ARITHABORT ON select * from testdb..
bar with(noexpand)" queryout t -T -S asdf  -c
SQLState = 37000, NativeError = 1934
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed
because the following SET options have incorrect settings: 'ARI
THABORT'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
could not be prepared.

I almost beat the system, though:

$ bcp "SET ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL,
ANSI_WARNINGS, ANSI_PADDING, ARITHABORT ON exec ('select * from
testdb..bar with(noexpand)')" queryout t -T -S asdf  -c
SQLState = 37000, NativeError = 1934
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed
because the following SET options have incorrect settings: 'ARITHABORT'.
[==  end  ==]

-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent 
responsible
for delivering this message to the intended recipient, any review, 
dissemination,
distribution or duplication of this communication is strictly prohibited. If 
you are
not the intended recipient, please contact the sender immediately by reply 
e-mail
and destroy all copies of the original message. Please note that we do not 
accept
account orders and/or instructions by e-mail, and therefore will not be 
responsible
for carrying out such orders and/or instructions.  If you, as the intended 
recipient
of this message, the purpose of which is to inform and update our clients, 
prospects
and consultants of developments relating to our services and products, would 
not
like to receive further e-mail correspondence from the sender, please "reply" 
to the
sender indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New 
York,
NY 10105.