April 8, 2004 at 3:38 am
Ok, I am going mad trying to get this to work. When I script my stored procedures I get similar to the following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE test1 AS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Why why why does it turn quoted identifier off at the end of the script??? I have changed the DB options to set quoted identifier and still no joy. I just cant figure out why it does this. Is it normal and is it possible to make the script set them on at the end of the script?? I am trying to enforce ANSI SQL 92 so I want all identifiers and nulls to be on all of the time
and another thing that i can not get my head around is this. When i check my DB settings via the following:
select DATABASEPROPERTYEX('test' , 'IsAnsiNullDefault')
select DATABASEPROPERTYEX('test' , 'IsAnsiNullsEnabled')
select DATABASEPROPERTYEX('test' , 'IsQuotedIdentifiersEnabled')
They all come back true. So one would assume that if i create a new stored procedure it will take on these settings as a default. But oh no no, it doesnt! When you create a stored procedure it takes on the settings of the last stored procedure that you created. Try it. Ok assuming you have Quoted identifiers on and nulls off, create a stored procedure using the following text:
CREATE PROCEDURE Test1 AS
Now preview the script you can generate and you will see it puts the appropriate headers in (nulls off and identifiers on). Which is fine.
Now create a new stored proc using the following:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE Test2 AS
Now preview the script you can generate and you will see it puts the appropriate headers in (nulls off and identifiers off). Which is fine.
Now any subsequent procedures you create without headers will use the last headers you entered regardless of what the DB settings are set too so if you write another procedure without any headers before it, when you preview the script it shows identifiers and nulls to be off. I find this to be stupid! Has anyone else come across this?
All help is appreciated!
April 9, 2004 at 5:31 am
Our DBA has told me that the server also has a QUOTED_IDENTIFIER and ANSI_NULLS setting but I am not really sure how to check the server setting.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply