View Single Post
  #2 (permalink)  
Old January 12th, 2006, 03:07 PM
David_the_DBA David_the_DBA is offline
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