Quoted identifiers are used by default in SQL Server 2000, that is, they are set to ON. This is different from SQL Server 7.0 where they were set to OFF by default. Read on to see what this means for you.
Great article. This article confirmed all of the trouble I was having, especially since the SQL Server 7 guys could not see the problem. The only downside I can see here... is that there is no mention of reserved words. I am now experiencing a situation where one of my passed in parameters to build the SQL Statement is equal to 'IN' (Indiana), and it errors out. The dillema, is that Microsofts method for setting the QUOTED_IDENTIFIER off from the ADO connection object does not work... so I'm stuck. Don't really know of a workaround on that one yet. All advice is appreciated!
The easiest way to avoid problems with quoted identifiers is to ensure you ONLY use single quotes to delimit strings...ALWAYS. That way you never have to worry about whether QUOTED_IDENTIFIER is on or off. You can also go further and only use square brackets [ ] to delimit object names and identifiers. If you do both of these, then you ensure that any double quote character (") is always part of a data value and has no kind of syntactical significance that can be misinterpreted by SQL Server.