Re: Difference between tsql and libsybdb

On Tue, 25 Sep 2012 11:12:29 +0200
Alberto Pulvirenti <[email protected]> wrote:

> Once I asked to the DB admin to report me the log of the failed
> operation in this logon trigger which kicks me out, the result is
> this:
> 
> SELECT failed because the following SET options have incorrect
> settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.

That's not a "logon trigger" message.  It doesn't come from a
trigger and it's not triggered by logging in.  

Read the message again.  It says SELECT failed.  You weren't "kicked
out" as far as the server was concerned; your connection was still OK.
It only refused to process your query.  Why?  Because your SET options
were incorrect for the type of query you issued.  

> 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.
...
> Since it speaks also about XML, that drove me to the idea that there
> must be something sent in XML which this server doesn't like, while
> tsql (yet the program, I mean) does not.

T-SQL has an "AS XML" clause in its select syntax.  That's what the
message refers to.  If you read up on it in BOL, you'll find certain
session properties have to be set for the server to process the query.
Similarly if you try to create a an indexed view, you'll find about 13
prerequisites.  

Session properties aren't set by dbopen().  They're set via
T-SQL's SET command.  Read up on that, and on @@options.  (Nowadays
there's a function to query session properties individually, not just
as a bit mask.)  

HTH.  

--jkl