Re: set ansi_nulls OFF

Just for the record, I resolved this issue by creating all stored procs by the auto –script in SQL Server, then manually changing the SET ANSI_NULLS to OFF within the script and finally I recompiled all stored procs. It seems to work. Of course a client may choose to amend the ansi_nulls to ON.

Thanks,

Panos.

--- On Tue, 25/1/11, Panos Stavroulis <[email protected]> wrote:

> From: Panos Stavroulis <[email protected]>
> Subject: Re: [freetds] set ansi_nulls OFF
> To: "FreeTDS Development Group" <[email protected]>
> Date: Tuesday, 25 January, 2011, 18:50
> Hi Bill,
> 
> Yes at the moment the set ansi-nulls seems to defaul to ON
> for some reason. I am aware that I can go to all affected
> stored procs and set it at the top within the stored proc
> but I guess if I would like to avoid this as I would have to
> re-script loads of stored procs. 
> 
> So I was wondering whether it's possible to set it on the
> freetds side so won't have to make any changes hopefully.
> 
> It's possible that my code somewhere hardcodes this to be
> always ON but if there is no option within freetds can't see
> how it can be done. 
> 
> The other solution is changing the code manually which
> shouldn't take too long with some regular expression but not
> really ideal.
> 
> Thanks very much again.
> 
> Regards,
> 
> Panos.
> 
> --- On Mon, 24/1/11, Thompson, Bill D (London) <[email protected]>
> wrote:
> 
> > From: Thompson, Bill D (London) <[email protected]>
> > Subject: Re: [freetds] set ansi_nulls OFF
> > To: "FreeTDS Development Group" <[email protected]>
> > Date: Monday, 24 January, 2011, 22:54
> > Hi Panos,
> > 
> > freetds db-library *used* to connect to the database
> > "pretending" to be
> > an odbc connection (it was a little understood bit in
> the
> > TDS login
> > packet. Once we understood that bit, we unset it for
> the
> > dblib and ctlib
> > connections, viz:
> > 
> > dbproc->tds_socket->option_flag2 &=
> ~0x02; 
> > /* we're not an ODBC driver
> > */
> > 
> > in dblib.c...
> > 
> > let me explain a bit. if you look carefully in BOL for
> SET
> > ANSI_NULLS...you'll find the folowing:
> > 
> > "The SQL Server ODBC driver and Microsoft OLE DB
> Provider
> > for SQL Server
> > automatically set ANSI_NULLS to ON when connecting."
> > 
> > It's the bit in the login packet that triggers that
> > behaviour...
> > 
> > So...it should be set to OFF for a db-library
> connection.
> > but you tell
> > us it isn't...
> > 
> > tip from one in the know: check your stored procedure
> > scripts - there
> > may be a SET ANSI_NULLS ON at the top of your scripts
> ;-)
> > 
> > Bill
> > 
> > -----Original Message-----
> > From: [email protected]
> > [mailto:[email protected]]
> > On Behalf Of Panos Stavroulis
> > Sent: 24 January 2011 15:28
> > To: [email protected]
> > Subject: [freetds] set ansi_nulls OFF
> > 
> > 
> > Hi,
> > 
> > Do you know if the dbsetopt function supports the
> > ansi_nulls option? I
> > couldn't see anything in the library options for
> > ansi_nulls. I would
> > like to set it to  OFF for my SQL Server
> connections.
> > I have set it as
> > OFF on the SQL Server and my database but I think
> defaults
> > from the type
> > of connection you have and not from the database.
> > 
> > Has anyone come across this problem before?
> > 
> > Thanks,
> > 
> > Panos 
> > 
> > 
> >       
> > _______________________________________________
> > FreeTDS mailing list
> > [email protected]
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> > 
> >
> ----------------------------------------------------------------------
> > This message w/attachments (message) is intended
> solely for
> > the use of the intended recipient(s) and may contain
> > information that is privileged, confidential or
> proprietary.
> > If you are not an intended recipient, please notify
> the
> > sender, and then please delete and destroy all copies
> and
> > attachments, and be advised that any review or
> dissemination
> > of, or the taking of any action in reliance on, the
> > information contained in or attached to this message
> is
> > prohibited. 
> > Unless specifically indicated, this message is not an
> offer
> > to sell or a solicitation of any investment products
> or
> > other financial product or service, an official
> confirmation
> > of any transaction, or an official statement of
> Sender.
> > Subject to applicable law, Sender may intercept,
> monitor,
> > review and retain e-communications (EC) traveling
> through
> > its networks/systems and may produce any such EC to
> > regulators, law enforcement, in litigation and as
> required
> > by law. 
> > The laws of the country of each sender/recipient may
> impact
> > the handling of EC, and EC may be archived, supervised
> and
> > produced in countries other than the country in which
> you
> > are located. This message cannot be guaranteed to be
> secure
> > or free of errors or viruses. 
> > 
> > References to "Sender" are references to any
> subsidiary of
> > Bank of America Corporation. Securities and Insurance
> > Products: * Are Not FDIC Insured * Are Not Bank
> Guaranteed *
> > May Lose Value * Are Not a Bank Deposit * Are Not a
> > Condition to Any Banking Service or Activity * Are
> Not
> > Insured by Any Federal Government Agency. Attachments
> that
> > are part of this EC may have additional important
> > disclosures and disclaimers, which you should read.
> This
> > message is subject to terms available at the following
> link:
> > 
> > http://www.bankofamerica.com/emaildisclaimer. By
> > messaging with Sender you consent to the foregoing.
> > _______________________________________________
> > FreeTDS mailing list
> > [email protected]
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> > 
> 
> 
>       
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 


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