none
How to avoid "'CREATE VIEW' must be the first statement in a query batch" when I need to use SET QUOTED_IDENTIFIER ON

    Question

  • I have a problem that I have been unable to solve.  I have a stored procedure in a database on server A that needs to create an indexed view in a database on server B.  (The environment is SQL Server 2005.)  The procedure on server A does the following:  (The EXEC statement inside the quotes is really a variable in my procedure, I've just substituted the value to make the example understandable.)

    EXEC sp_executesql 'EXEC [ServerB].[Database].dbo.sp_executesql @sqlStatement;'

    The "outer" sp_executesql causes the "inner" sp_executesql to be execute within the database on server B.  (@sqlStatement contains the CREATE VIEW syntax.)  I am able to create the view that I want to index with no problem.  However, when I attempt to create an index on the view in a subsequent step, it fails with the message "Cannot create index. Object '_dta_mv_51' was created with the following SET options off: 'QUOTED_IDENTIFIER'".  Fair enough, but if I attempt to precede my CREATE VIEW statement with the required QUOTED_IDENTIFIER, I receive an error to the effect that "'CREATE VIEW' must be the first statement in a query batch". 

    I'm not sure how to get around this problem.  Does anyone have the answer? SQL Server 2008 doesn't object to indexing a view that was created with QUOTED_IDENTIFIER off, but that's not doing me any good.

    Thanks.

    Thursday, September 16, 2010 10:23 PM

Answers

  • > Here is what my testing has shown.  First, an indexed view consists of two things, the view and the index on the view.  To create the indexed view, you would issue a CREATE VIEW followed by  a CREATE INDEX.  In SS 2005, you must precede the CREATE VIEW with a SET QUOTED_IDENTIFIER ON statement.  However, in SS 2008, this is not required.  

    Again, this has nothing to do with the version of SQL Server. Both SQL 2005 and SQL 2008 save the setting of QI with the view definition, and both require QI to be ON for an indexed view.

    This script fails on both SQL 2005 and SQL 2008:

    USE tempdb
    go
    CREATE TABLE mybasetable (a int NOT NULL PRIMARY KEY)
    go
    SET QUOTED_IDENTIFIER OFF
    go
    CREATE VIEW myview WITH SCHEMABINDING AS
      SELECT a FROM dbo.mybasetable WHERE a > 0
    go
    CREATE UNIQUE CLUSTERED INDEX vix ON myview(a)
    go
    DROP VIEW myview
    DROP TABLE mybasetable
    go
    SET QUOTED_IDENTIFIER ON

    You can do the nested EXEC kludge, but the real problem is why QUOTED_IDENTIFIER is OFF on your SQL Server 2005 machine. It shouldn't be.

    You said that you createing this from a stored procedure (which I think is a dubious practice), but that is another story. Could you disclose the full code? I got the impression that were linked servers involved, could you confirm this? If that is the case, I would think there is something bad with the definition of the linked server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by KJian_ Wednesday, September 22, 2010 7:24 AM
    Friday, September 17, 2010 10:15 PM

All replies

  • QUOTED_IDENTIFIER must be ON for view to be indexable. This applies to SQL 2008 as well. Furthermore, QUOTED_IDENTIFIER is on by default in most environments, so I'm not sure why it's off here. Are you by chance using SQLCMD? SQLCMD has QUOTED_IDENTIFIER OFF by default. Use the -I option to overridable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Thursday, September 16, 2010 10:29 PM
  • No, I'm not using SQLCMD, just a stored procedure.  I need a way to programmatically set QUOTED_IDENTIFIER on so that my view can be indexed.  I know that CREATE VIEW must be the first statement in a batch.  I wonder if I could create the view in one batch (executed by sp_executesql), then satisfy the QUOTED_IDENTIFIER requirement by preceding an ALTER VIEW with SET QUOTED_IDENTIFIER ON in a second batch, then creating the index in a third batch.

    Thanks.

    Thursday, September 16, 2010 10:54 PM
  • > No, I'm not using SQLCMD, just a stored procedure.  I need a way to programmatically set QUOTED_IDENTIFIER on so that my view can be indexed.  I know that CREATE VIEW must be the first statement in a batch.  I wonder if I could create the view in one batch (executed by sp_executesql), then satisfy the QUOTED_IDENTIFIER requirement by preceding an ALTER VIEW with SET QUOTED_IDENTIFIER ON in a second batch, then creating the index in a third batch.

    My point is that QUOTED_IDENTIFIER should never be off in the first place, so we should try to find out how that happens.

    Could you give a complete script that shows your steps so that I can understand? Also, you seem to use a linked server. Can you post the row from sys.servers for this server?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Friday, September 17, 2010 7:57 AM
  • Erland,

    When you script out a view in SSMS Object Explorer, you get 2 SETs preceding CREATE VIEW:

    USE [AdventureWorks2008]
    GO
    
    /****** Object: View [HumanResources].[vEmployee]  Script Date: 09/17/2010 04:01:07 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE VIEW [HumanResources].[vEmployee] 
    AS 
    SELECT 
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle] 
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,p.[AdditionalContactInfo]
    FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON p.[BusinessEntityID] = ea.[BusinessEntityID];
    
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee names and addresses.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'VIEW',@level1name=N'vEmployee'
    GO
    
    
    
    

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Friday, September 17, 2010 8:02 AM
  • Hi,

    I saw recently a similar question in a different forum. The solution is to perform "nested" execute.

    set @SQL = 'declare @set nvarchar(100)
    set @set = ''SET QUOTED_IDENTIFIER OFF''
    execute (@set)

    See this thread http://tek-tips.com/viewthread.cfm?qid=1620195&page=1

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, September 17, 2010 1:04 PM
  • When you script out a view in SSMS Object Explorer, you get 2 SETs
    preceding CREATE VIEW:

    Yes, this is because the settings of ANSI_NULLS and QUOTED_IDENTIFIER when the view is created are preserved, so even if there is different setting at run-time the create-time setting applies. And BlackCatBone appears to understand this, since he/she wants to issue SET QUOTED_IDENTIFIER before CREATE VIEW.

    In the script you generate from SSMS, the SET commands are separated with GO, but this does not help BlackCatBone, since he/she is running this as dynamic SQL.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Friday, September 17, 2010 1:28 PM
  • Thanks for the link, Naom.  I haven't tried the approach yet, but I will.

    Here is what my testing has shown.  First, an indexed view consists of two things, the view and the index on the view.  To create the indexed view, you would issue a CREATE VIEW followed by  a CREATE INDEX.  In SS 2005, you must precede the CREATE VIEW with a SET QUOTED_IDENTIFIER ON statement.  However, in SS 2008, this is not required.  It is enough to precede the CREATE INDEX with the quoted identifier command.  That is quite a difference.  Since I am creating the indexed view inside a stored procedure, and since the CREATE VIEW must be the first statement in a batch, I can't get the indexed view to build under SS 2005, but it works OK under SS 2008 because I have preceded the CREATE INDEX with the required quoted identifier setting and there is no restriction that requires a CREATE INDEX to be the first statement in a batch.

    I've tried approaches similar but not identical to the one pointed out by Naom.  Hope it works!

    BCB

    Friday, September 17, 2010 2:26 PM
  • > Here is what my testing has shown.  First, an indexed view consists of two things, the view and the index on the view.  To create the indexed view, you would issue a CREATE VIEW followed by  a CREATE INDEX.  In SS 2005, you must precede the CREATE VIEW with a SET QUOTED_IDENTIFIER ON statement.  However, in SS 2008, this is not required.  

    Again, this has nothing to do with the version of SQL Server. Both SQL 2005 and SQL 2008 save the setting of QI with the view definition, and both require QI to be ON for an indexed view.

    This script fails on both SQL 2005 and SQL 2008:

    USE tempdb
    go
    CREATE TABLE mybasetable (a int NOT NULL PRIMARY KEY)
    go
    SET QUOTED_IDENTIFIER OFF
    go
    CREATE VIEW myview WITH SCHEMABINDING AS
      SELECT a FROM dbo.mybasetable WHERE a > 0
    go
    CREATE UNIQUE CLUSTERED INDEX vix ON myview(a)
    go
    DROP VIEW myview
    DROP TABLE mybasetable
    go
    SET QUOTED_IDENTIFIER ON

    You can do the nested EXEC kludge, but the real problem is why QUOTED_IDENTIFIER is OFF on your SQL Server 2005 machine. It shouldn't be.

    You said that you createing this from a stored procedure (which I think is a dubious practice), but that is another story. Could you disclose the full code? I got the impression that were linked servers involved, could you confirm this? If that is the case, I would think there is something bad with the definition of the linked server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by KJian_ Wednesday, September 22, 2010 7:24 AM
    Friday, September 17, 2010 10:15 PM
  • Store the Script in a file and open it with "Notepad"

    If you find the script in 1 line, it will give error. Avoid having
    [ENTER Character dispayed as BOX in notepad] character

     

    Wednesday, March 30, 2011 4:34 AM