[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

QUOTED_IDENTIFIER in Proc

Posted on 2010-09-13
8
Medium Priority
?
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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
 

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 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