Solved

QUOTED_IDENTIFIER in Proc

Posted on 2010-09-13
8
344 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 84
2016 SQL Licensing 7 40
SQL Update trigger 5 16
SQL Server: Unable to remove duplicate sets in Header/Detail 6 21
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

815 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

10 Experts available now in Live!

Get 1:1 Help Now