Invalid column name

Transact-SQL
1

Here is my code. Very simple yet I'm getting "invalid column." I am using SQL Express.

SELECT *
FROM TemporaryCustomers
WHERE FirstName = "Cassady";

2

Try this:

SELECT *
  FROM TemporaryCustomers
 WHERE FirstName = 'Cassady'
;
3

I tried that and it worked with single quotes. What's weird is that I know I tried that before and it didn't work before so I don't know how to explain that.

Thank you for your suggestion.

4

To use double quote you must issue the following SET command first:

SET QUOTED_IDENTIFIER OFF;
GO

http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/

1 Like
5

True, but do yourself a favour and never ever set Quoted_Identifier to off. It's a legacy setting that causes all sorts of weirdness. Lots of newer functionality (and when I say newer I mean SQL 2005+) straight up just will not work unless Quoted_Indentifier is on.

6

Please enlighten us - which functionallity?

7

XQuery and XML data modification, Filtered Indexes, Indexed Views ….

1 Like
8

See Remarks under
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-2017

try this

SET QUOTED_IDENTIFIER OFF;
GO


declare @myDoc xml  
set @myDoc = '<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
  <Warranty>1 year parts and labor</Warranty>  
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  
</Features>  
</ProductDescription>  
</Root>'  
SELECT @myDoc.query('/Root/ProductDescription/Features')
9

Andy,
There are many situations where I have used SET QUOTED_IDENTIFIER OFF safely. As you point out, there are many situations where it can't be used. Awareness of which features require the ON setting are few enough to become familiar with. As with anything you do, ignorance can bite you if you don't study the effects of the settings you use. It isn't a legacy setting. It is supported in SQL Server 2017. You may be over stating the warning but it's good to become acquainted with it's usage and side effects.
Don't SET QUOTED_IDENTIFIER OFF if you're:

  • Executing DDL on an index

  • Creating an indexed view or adding a computed column

    • These are the required settings for indexed views and computed columns: SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF

  • Invoking XML data type methods

10

When I say legacy setting, I mean that it is a setting that exists purely to re-enable non-standard behaviour from old versions of SQL Server prior to the introduction of ANSI standards. There is no good reason to revert to that behaviour for newly developed code and it is only likely to cause you problems in future that you could have avoided by just using ANSI compliant behaviour originally. Note that some of these legacy behaviours, such as ANSI NULLS and CONCAT_NULL_YIELDS_NULL, are already flagged as being errors in a future SQL Server release and it's likely others will go that way at some point.

There is a reason I said "Do yourself a favour", it's less painful in the long run to just use the standards compliant method for quoting strings.