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
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
View Complete Post