Quoted Identifiers

Often, you'll download a query from a vendor or another DBA, and character data is in double-quotes. The example I gave earlier in this chapter for generating sample data uses double-quotes on the Web site like this:

SELECT * FROM (     SELECT "Fred" as fName union      SELECT "Wilma" union     SELECT "Barney" union     SELECT "Betty" ) as flintstones_1 CROSS JOIN (     SELECT "Flintstone" as lName union     SELECT "Rubble" union     SELECT "Knight" ) as flintstones_2

By default, SQL Server treats anything within double-quotes as a column name. This is because QUOTED_IDENTIFIERS are set to ON by default in Query Analyzer. The code in the preceding example results in the following error by default in Query Analyzer:

Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'Fred'.

To correct the problem, you can turn the QUOTED_IDENTIFIERS off in the first line of your stored procedures or in your ad hoc queries. Accomplish this by using the following syntax:

SET QUOTED_IDENTIFIER OFF

Once it is turned off, you can compile the earlier query.