xQuery requires QUOTED_IDENTIFIER
I am working for a client that would has XML data being passed into a Service Broker queue.
The queue is configured to activate a stored procedure once a message is recieved.
Inside that stored procedure I would like to you xQuery methods to find data in the xml that got passed in, however, I continue to get an error stating that...
'CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
... I have tried several different ways of turning on quoted identifiers (turning them on for the entire database, turning them on inside the stored procedure) however I cannot seem to get any xquery methods to work when the procedure is called via activation.
The procedure runs fine if I just run the sql in query analizer.
Any help would be appreciated before I pull out what is left of the hair on my head.
I assume you already have the answer from the SQL newsgroups but just in case, Quoted Identifier is one of serveral options that must be set when using the XML datatype. It must be set when creating the stored procedure. Setting it when it is executed doesn't help. Also, as I said on the newsgroup you might get better performance from OpenXML if you are pulling many elements from the XML document.
This is a restore from sql 2000 instance.
I changed the compatability level to 90
alter database [dbname] set enable_broker
After i did that, the application started erroring left and right on that database when i tried to insert/update through through the UI.
Quoter_identifier is turned ON.
when i set the db to disabel_broker, the application works fine.
Looks like enabling broker changes something.
Is there any article that explains about what else need to be set when i enable the broker
ERROR:[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
This is the error I am getting. I do not have Indexed view on that database at all.
any kind of help is appreciated
Thanks
uday
If this is a SQL 2000 application now running in SQL 2005, then it can't be query notification or xml data type or service broker because none of those things existed in SQL 2000. You said you have no indexed views so it appears that the only thing left is an index on a computed colum unless you have changed the database since upgraded it to use some of the other options. My quess is you'll find that you are doing something you're not aware of - maybe in a trigger or event - that's causing this. You might get more help in one of the SQL Server core newsgroups because unless there's something you're not telling us, there's no way Service Broker can be involved in an insert that used to work in SQL 2000 because service broker didn't exist then.
