ANSI_NULL and ANSI_QUOTED_IDENTIFIER

I am trying to set these two options to ON for default sp creation. or at LEAST be able to set them for sp's accessing databases on remote servers.

Seems though, that no matter what I set the rekated checkboxes to in the "edit SQL Server registration properties"'s connections pane, they get created as:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[sp_yada_yada_yada]
AS

DECLARE @.YoMama float
DECLARE @.YoDaddy float
DECLARE @.YoSista float

SET @.YoSista = @.YoDadd + @.YoMama

RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

(which I assume is a previous/default setting of some type).

I also had someone tell me that I need to set those params when I initially register the server, but I can't see any option to do so when I register my server(s) *scratching head*

I even verified in the connection pane's "running values" radio button, and they ARE checked (as I expected) but creating a new SP STILL results in the incorrect option settings being auto-magically applied.

help?
Thanks!


Any SET options that affect the results generated by Transact-SQL statements must have the same settings for all operations referencing the index. There are seven SET options that affect the results stored in computed columns and returned by views. All connections using indexes on computed columns or indexed views must have the same settings for these seven options:

These six SET options must be set to ON:
ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YIELDS_NULL

QUOTED_IDENTIFIER
The NUMERIC_ROUNDABORT option must be set to OFF.
These SET options must be set correctly for any connection that creates an index on a view or computed column. Any connection executing INSERT, UPDATE or DELETE statements that change data values stored in the indexes must have the correct settings.Why are you doing this?

Leave the settings alone unless you HAVE to change them...|||Originally posted by Brett Kaiser
Why are you doing this?

Leave the settings alone unless you HAVE to change them...

It's my understanding that I need to change them to keep from getting the error I'm getting when I try to access a database on a linked server...
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

I know of no other way around it - - currently I create my stored procedures and functions that access the linked servers though Query Analyzer - because it pays attention to my SET OPTIONS stuff outside the function/stored procedure.

If there's a way to get it to be kind to my linked server queries from the SQL Server Enterprise Manager, that would be cool...but nothing I've tried SO FAR will convince it to be nice to me.

Maybe there's a blurb in the Books Online that can help me understand more about why I shouldn't set these options to ON when doing a query to a linked server, but everything I've read so far says then need to be set ON...*shrug*|||found my answer here...but still...
http://www.dbforums.com/showthread.php?threadid=971846&highlight=Heterogeneous+queries+require+the+ANSI_N ULLS+and+ANSI_WARNINGS+options+to+be+set+for+the+c onnection

I guess it makes sense to just do it in the creation of the specific SP, rather than modifying a server option I know very little about *LOL*

Thanks for the loan of your time though :D|||Keep on truckin....