Solved

QUOTED_IDENTIFIER in Proc

Posted on 2010-09-13
8
342 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

12 Experts available now in Live!

Get 1:1 Help Now