Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: 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:
    Code:
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you doing this?

    Leave the settings alone unless you HAVE to change them....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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*
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Nevermind...

    found my answer here...but still...
    http://www.dbforums.com/showthread.p...the+connection

    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
    Last edited by TallCowboy0614; 02-26-04 at 16:15.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Keep on truckin.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •