p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Database options changed on Audit Login (http://p2p.wrox.com/showthread.php?t=37120)

ocliff January 12th, 2006 09:01 AM

Database options changed on Audit Login
I have set concat_null_yields_null to off on my database. When I connect from Access XP, VB6 with DAO or ADO or other that use OLE or ODBC I can see in the profiler that they set concat_null_yields_null to on:

-- network protocol: Named Pipes
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7

Can I configure SQL Server to ignore turning the setting back on again, or is it not an SQL Server issue?

Best regards,

David_the_DBA January 12th, 2006 03:07 PM

Those are the default settings to match ANSI standards which (specifically ANSI NULLS ON) enable you to execute distributed queries (linked servers, openquery, openrowset, opendatasource), as well as creating or modifying indexes on Views, and computed columns. It is also required to be on when executing Inserts, Updates, and Deletes against tables with indexes on computed columns or on indexed views will error out.

Fortunately, when executing a stored procedure it uses the ANSI_NULLS and Quoted_Identifier settings in place at the time of creation. All of the other settings are dependent on the current connection settings.

After opening the connection execute a sql statement with the various set commands you like:
-- VB 6 code fragment example ADO 2.x
Cn.Execute "Set concat_null_yields_null off"

Then do whatever you need

David Lundell
Principal Consultant and Trainer

All times are GMT -4. The time now is 10:58 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.