Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 12th, 2006, 09:01 AM
Registered User
Join Date: Jan 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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,

Old January 12th, 2006, 03:07 PM
Friend of Wrox
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Startup Options/Database Password kritimehrotra Access 0 July 28th, 2006 05:54 PM
Startup Options/Database Password kritimehrotra Access VBA 0 July 28th, 2006 05:49 PM
Primary key changed - How to migrat database.? nmmure Crystal Reports 0 June 26th, 2006 03:47 PM
update values of changed cells into database using changusee2k PHP Databases 0 March 9th, 2006 01:05 PM
Search and update SQL database if value is changed jakvike VBScript 1 August 4th, 2004 06:32 AM

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