Solved

QUOTED_IDENTIFIER in Proc

Posted on 2010-09-13
8
346 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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