Solved

QUOTED_IDENTIFIER in Proc

Posted on 2010-09-13
8
343 Views
Last Modified: 2012-05-10
I'm creating a view that needs to be indexed.  But, in order to create an index for it, the view has to be created with the session property QUOTED_IDENTIFIERS ON.  Even though I'm setting this property just before creating the view, when I try to create the index, I get the error:

Cannot create index.  Object 'tbl_ForecastMonthClient' was created with tht efollowing SET options off:  'QUOTED_IDENTIFIER'.

IS THERE ANY OTHER WAY TO SET OPTIONS IN A PROC?



Following is the proc:



CREATE PROCEDURE dbo.spCreateMonthClientView  
                             @ForID int
AS
SET NOCOUNT ON


--Set the options to support indexed views.
-- Make sure that all of the session settings are set properly

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

EXEC ('
CREATE VIEW dbo.tbl_ForecastMonthClient
WITH SCHEMABINDING

AS

SELECT dbo.tbl_ForecastMonthClient_MAIN.ForMthClientID,
dbo.tbl_ForecastMonthClient_MAIN.ForID,
dbo.tbl_ForecastMonthClient_MAIN.ForMthID,
dbo.tbl_ForecastMonthClient_MAIN.AcctType,
dbo.tbl_ForecastMonthClient_MAIN.AcctCategory,
dbo.tbl_ForecastMonthClient_MAIN.CatOrder,
dbo.tbl_ForecastMonthClient_MAIN.AcctID,
dbo.tbl_ForecastMonthClient_MAIN.Account,
dbo.tbl_ForecastMonthClient_MAIN.Location,
dbo.tbl_ForecastMonthClient_MAIN.Segment,
dbo.tbl_ForecastMonthClient_MAIN.ID,
dbo.tbl_ForecastMonthClient_MAIN.Extension,
dbo.tbl_ForecastMonthClient_MAIN.ClientCode,
dbo.tbl_ForecastMonthClient_MAIN.NewNum,
dbo.tbl_ForecastMonthClient_MAIN.Quarter,
dbo.tbl_ForecastMonthClient_MAIN.UpdateAllYearYN,
dbo.tbl_ForecastMonthClient_MAIN.UpdateAllYN,
dbo.tbl_ForecastMonthClient_MAIN.M1Orig,
dbo.tbl_ForecastMonthClient_MAIN.M1ChgYN,
dbo.tbl_ForecastMonthClient_MAIN.M1,
dbo.tbl_ForecastMonthClient_MAIN.M2Orig,
dbo.tbl_ForecastMonthClient_MAIN.M2ChgYN,
dbo.tbl_ForecastMonthClient_MAIN.M2,
dbo.tbl_ForecastMonthClient_MAIN.M3Orig,
dbo.tbl_ForecastMonthClient_MAIN.M3ChgYN,
dbo.tbl_ForecastMonthClient_MAIN.M3,
dbo.tbl_ForecastMonthClient_MAIN.CalcYN,
dbo.tbl_ForecastMonthClient_MAIN.CalcPercent,
dbo.tbl_ForecastMonthClient_MAIN.CalcAcct,
dbo.tbl_ForecastMonthClient_MAIN.EditYN,
dbo.tbl_ForecastMonthClient_MAIN.NotActiveYN,
dbo.tbl_ForecastMonthClient_MAIN.HideYN,
dbo.tbl_ForecastMonthClient_MAIN.UpdateDate,
dbo.tbl_ForecastMonthClient_MAIN.UpdatedBy

FROM dbo.tbl_ForecastMonthClient_MAIN

WHERE dbo.tbl_ForecastMonthClient_MAIN.ForID=' + @ForID + '')
GO





0
Comment
Question by:DHompster
  • 4
  • 4
8 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33667550
EXEC starts a new session, so you need to set it inside EXEC
CREATE PROCEDURE dbo.spCreateMonthClientView  

                             @ForID int

AS

SET NOCOUNT ON





--Set the options to support indexed views.

-- Make sure that all of the session settings are set properly



SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF





declare @sql nvarchar(max)

set @sql = '

SET QUOTED_IDENTIFIER ON

;

declare @sql nvarchar(max)

set @sql = ''

CREATE VIEW dbo.tbl_ForecastMonthClient

WITH SCHEMABINDING



AS



SELECT dbo.tbl_ForecastMonthClient_MAIN.ForMthClientID,

dbo.tbl_ForecastMonthClient_MAIN.ForID,

dbo.tbl_ForecastMonthClient_MAIN.ForMthID,

dbo.tbl_ForecastMonthClient_MAIN.AcctType,

dbo.tbl_ForecastMonthClient_MAIN.AcctCategory,

dbo.tbl_ForecastMonthClient_MAIN.CatOrder,

dbo.tbl_ForecastMonthClient_MAIN.AcctID,

dbo.tbl_ForecastMonthClient_MAIN.Account,

dbo.tbl_ForecastMonthClient_MAIN.Location,

dbo.tbl_ForecastMonthClient_MAIN.Segment,

dbo.tbl_ForecastMonthClient_MAIN.ID,

dbo.tbl_ForecastMonthClient_MAIN.Extension,

dbo.tbl_ForecastMonthClient_MAIN.ClientCode,

dbo.tbl_ForecastMonthClient_MAIN.NewNum,

dbo.tbl_ForecastMonthClient_MAIN.Quarter,

dbo.tbl_ForecastMonthClient_MAIN.UpdateAllYearYN,

dbo.tbl_ForecastMonthClient_MAIN.UpdateAllYN,

dbo.tbl_ForecastMonthClient_MAIN.M1Orig,

dbo.tbl_ForecastMonthClient_MAIN.M1ChgYN,

dbo.tbl_ForecastMonthClient_MAIN.M1,

dbo.tbl_ForecastMonthClient_MAIN.M2Orig,

dbo.tbl_ForecastMonthClient_MAIN.M2ChgYN,

dbo.tbl_ForecastMonthClient_MAIN.M2,

dbo.tbl_ForecastMonthClient_MAIN.M3Orig,

dbo.tbl_ForecastMonthClient_MAIN.M3ChgYN,

dbo.tbl_ForecastMonthClient_MAIN.M3,

dbo.tbl_ForecastMonthClient_MAIN.CalcYN,

dbo.tbl_ForecastMonthClient_MAIN.CalcPercent,

dbo.tbl_ForecastMonthClient_MAIN.CalcAcct,

dbo.tbl_ForecastMonthClient_MAIN.EditYN,

dbo.tbl_ForecastMonthClient_MAIN.NotActiveYN,

dbo.tbl_ForecastMonthClient_MAIN.HideYN,

dbo.tbl_ForecastMonthClient_MAIN.UpdateDate,

dbo.tbl_ForecastMonthClient_MAIN.UpdatedBy



FROM dbo.tbl_ForecastMonthClient_MAIN



WHERE dbo.tbl_ForecastMonthClient_MAIN.ForID=' + @ForID + '

''

exec (@sql)'

exec (@sql)

GO

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33667553
Just remember that the session used to created the unique clustered index on the view itself must also have SET QUOTED_IDENTIFIER ON, otherwise you get an error as well.
0
 

Author Comment

by:DHompster
ID: 33667880
Thanks, cyberkiwi!
I think I'm getting closer.  It didn't see the humor in  'declare @sql nvarchar(max)'.  Maybe because it's SQL 2000?  I changed it to  'declare @sql nvarchar(4000)' and it seemed to like it.
However, now I get the following:

Server: Msg 245, Level 16, State 1, Procedure spCreateMonthClientView, Line 20
Syntax error converting the varchar value '
SET QUOTED_IDENTIFIER ON
;
declare @sql nvarchar(4000)
set @sql = '
CREATE VIEW dbo.tbl_ForecastMonthClient
WITH SCHEMABINDING
 
AS
 
SELECT dbo.tbl_ForecastMonthClient_MAIN.ForMthClientID,
dbo.tbl_ForecastMonthClient_MAIN.ForID,
dbo.tbl_ForecastMonthClient_MAIN.ForMthID,
dbo.tbl_ForecastMonthClient_MAIN.AcctType,
dbo.tbl_ForecastMonthCli...
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33667894
I thought the posted code was working.
In line 67, it should be

WHERE dbo.tbl_ForecastMonthClient_MAIN.ForID=' + convert(varchar(10), @ForID) + '
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:DHompster
ID: 33668027
YES!!!!  THANK YOU!!!
I don't quite understand the nesting of the 'set @SQL=' statements.  Is there a quick way to help me understand how/why that works?
0
 

Author Comment

by:DHompster
ID: 33668044
The two execs at the end are particularly confusing:  one inside the sql string; one outside.  ??
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33668240
Going for a simpler proc to more easily explain.
In a stored proc, whatever you set in there applies only to the proc, therefore SET QUOTED_IDENTIFIER ON does nothing - it sets it not for the session but for the SP only, which is not enough.

An EXEC starts a new "session", so it is like opening a new query window and pasting the code there and running.
The outer @sql sends this string to EXEC

SET QUOTED_IDENTIFIER ON
;
declare @sql nvarchar(max)
set @sql = '
CREATE VIEW dbo.tbl_ForecastMonthClient WITH SCHEMABINDING AS
SELECT 1
'
exec (@sql)

The inner EXEC is now a new session, it will first run the SET QUOTED_IDENTIFIER ON, then it will run the dynamic sql embedded inside, which is

CREATE VIEW dbo.tbl_ForecastMonthClient WITH SCHEMABINDING AS
SELECT 1

Hope that helps.
declare @sql nvarchar(max)

set @sql = '



SET QUOTED_IDENTIFIER ON

;

declare @sql nvarchar(max)

set @sql = ''

CREATE VIEW dbo.tbl_ForecastMonthClient WITH SCHEMABINDING AS

SELECT 1

''

exec (@sql)



'

exec (@sql)

GO

Open in new window

0
 

Author Comment

by:DHompster
ID: 33668429
Thank you!!  You're a scholar and a gentleman!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL:  Embedding a CTE 5 32
Export import database 4 42
T-SQL:  Negative Numbering in CTE Is Not Working 2 29
SQL - SP needs a little help 9 20
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now