Posts

Showing posts from July, 2015

QUOTED_IDENTIFIER Gotcha!

Image
This one is new for me. It might be something you have to consider all the time. I do not.

Stored procedures in SQL may have defaults for certain options set only at creation. So you can't override them or even alter them. You have to drop and recreate them if those options have an affect on your query.

This probably doesn't affect 99% of your queries. It doesn't affect mine too much. Today I had to so a bit of XML in my stored procedure though. It worked in dev fine because of course my options are set differently than in another server. When I gave the script to someone else to try... whammo!
SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'



What the hell does that mean? Is this Office Space? PC LOAD LETTER!

No it's not a movie. This is real life and SQL has this cool table called sys.sql_modules. It can store options that you have set when you created that bloody stored procedure that isn't working now…