Re: Difference between tsql and libsybdb

Just a bet, if you use any odbc application will work!

Probably it's the "odbc" flags used during connection.

Frediano Ziglio

Il giorno 28/set/2012, alle ore 17:11, Alberto Pulvirenti <[email protected]> ha scritto:

> Dear all,
> 
> I have some additional info about this problem, so I prefer to give all
> details again, hoping in some help from anyone.
> 
> So, the problem is the following.
> I have developed an executable which tries to log into a SQL Server 2008
> instance using dblib provided by freeTDS.
> The problem is that I don't manage to log into this server because it has a
> logon trigger procedure which fails when I try to access it.
> 
> The problem, in this logon trigger is that it has set to OFF the following
> three variables:
> 
> set ansi_warnings off
> set ansi_padding off
> set concat_null_yields_null off
> 
> 
> and the admin of this instance told me that he will NEVER switch them to
> ON. Then I must find a way to access this instance having these setting as
> they are currently.
> 
> I managed to reproduce the problem with the following procedure in my local
> instance of SQL Server 2008 Express. It is enough to add these instructions:
> 
> CREATE TRIGGER [filter_test]
> ON ALL SERVER WITH EXECUTE AS 'sa'
> FOR LOGON
> AS
> BEGIN
> declare @data XML
> declare @data01 varchar(100)
> 
> SET @data = EVENTDATA();
> -->set @data01= @data.value('(/EVENT_INSTANCE/ClientHost)[1]',
> 'varchar(100)');
> END;
> 
> I found that the line indicated with the arrow '-->' is the one that causes
> the problem. In fact, if I re-define the trigger commenting this line, the
> logon trigger is executed properly and I manage to login to the instance,
> while if IT don't comment this line, it fails. Then, there must be
> something that goes in the XML provided by EVENTDATA() when it is executed
> after the login through the db-lib that has something which must be set
> properly.
> 
> As all of you know, when one compiles the freetds-0.91 library, besides
> having the library libsydb.so (which I use), the compilation provides an
> executable which is called "tsql".
> I tried to access the same DB instance using this executable (then, now and
> in the following, when I say "tsql" I am referring to the executable, NOT
> the protocol).
> Well, if I log into this SQL Server instance using tsql with the suitable
> parameters, then the login succeeds perfectly.
> Then I assume that I should set something in the login packet in order for
> it to be properly configured to allow the logon trigger to process it
> correctly when loggin in. Something which surely is properly set in "tsql"
> but not when setting up the LOGINREC object which is needed by the dbopen()
> function which one invokes when using dblib.
> 
> BTW: I also tried to access this SQL Server instance using the other
> executable "bsqldb" which is provided in the library and I saw that it
> FAILS exactly like my application, then it must have to do with some
> non-standard settings which must be put in the login.
> I hope that it is not anything which is not managed/feasible using this
> library.
> 
> Thanks for all help and best regards
> 
> Alberto
> 
> 
> 2012/9/27 Alberto Pulvirenti <[email protected]>
> 
>> Hello,
>> 
>> 2012/9/27 James K. Lowden <[email protected]>
>> 
>>> On Tue, 25 Sep 2012 18:15:38 +0200
>>> Alberto Pulvirenti <[email protected]> wrote:
>>> 
>>>>> 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.
>>> 
>>> What may not be clear is that the problem you're seeing doesn't
>>> manifest itself when you first open the connection.  It shows up when
>>> you issue that particular query.  A simple query like
>>> 
>>>        SELECT 1 as 'one'
>>> 
>>> will not fail.  But one that requires e.g. CONCAT_NULL_YIELDS_NULL
>>> will.
>>> 
>> 
>> This is perfectly clear. Then, something which is sent by my dbopen()
>> invocation maybe triggers some query that relies on this.
>> I am just wanrdering what can this be, which is not done by the executable
>> "tsql".
>> 
>> 
>>> 
>>> CONCAT_NULL_YIELDS_NULL is a session property.  Session properties in a
>>> T-SQL session are set using the T-SQL SET command, cf.
>>> http://msdn.microsoft.com/en-us/library/ms190356.aspx.  Among the many
>>> arcane things affected by session properties are the ability to create
>>> indexed views and the ability to *use* an existing index of an indexed
>>> view, cf. "Requirements for the CREATE INDEX Statement" at
>>> http://msdn.microsoft.com/en-us/library/aa933148(v=SQL.80).aspx.
>>> 
>>>> In any case, I suppose that I can issue such a SET command only once
>>>> the DBPROCESS is properly initialized after dbopen().
>>> 
>>> English is a little ambiguous here.  You can issue any number of SET
>>> commands for the duration of the session, just as any query, after
>>> dbopen() and before dbclose().
>>> 
>> 
>> I am very sorry since maybe I don't manage to express myself clearly
>> enough.
>> Anyway, that's the point. My application fails when I call dbopen().
>> Then, whatever needed to solve this problem I need to apply BEFORE calling
>> dbopen(). That's what is driving me crazy.
>> 
>> Thanks,
>> 
>> Alberto
>> 
>> 
>>> 
>>> HTH.
>>> 
>>> --jkl
>>> _______________________________________________
>>> FreeTDS mailing list
>>> [email protected]
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>> 
>> 
>> 
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds