Re: set ansi_nulls OFF

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
>