Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 12th, 2006, 09:01 AM
Registered User
Join Date: Jan 2006
Location: , , .
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,

Reply With Quote
  #2 (permalink)  
Old January 12th, 2006, 03:07 PM
Friend of Wrox
Join Date: Dec 2005
Location: , AZ, .
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
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 12:04 AM.

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