Re: Difference between tsql and libsybdb

Hi, thanks for the reply, my points are inline.

2012/9/25 James K. Lowden <[email protected]>

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

Yes, this I understood.
The point is still the same, anyway: if I connect using the shell command:

./tsql -H <host> -p <port> -U <user> -P <pwd> -D <db>

then I manage to connect to the DB, execute queries and so on.

Instead, when I use a program which does a pool of DBSETL...stuff... and
then calls dbopen(), this fails (the message reported by logon trigger is
not given to me, it was a favor by the instance helpdesk who sent me this
as the error message which caused the logon trigger procedure to fail).

The only item which I can modify before calling dbopen() is the LOGINREC
object. I have taken a look also using wireshark, but it is hard to see
exactly which bytes should be perfectly equal and which ones don't.

BTW: of course I have only one $HOME/.freetds.conf file which I use for
both (and apparently both them see it, since I pass the server name in the
same way to both: that is, I pass the same string to the '-H' argument of
tsql command, and to the argument #2 of dbopen()


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

This last paragraph I didn't fully understand.
In any case, I suppose that I can issue such a SET command only once the
DBPROCESS is properly initialized after dbopen().
Or not?

Thanks

Alberto


>
> HTH.
>
> --jkl
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>