MS SQL ODBC Error "Invalid Column Name"

I wanted to create a post around an error (SOLVED) I was encountering when trying to connect to MS SQL Server:

ODBC Error 207: Invalid Column Name

Specifically, when submitting a parameter via an SQL string, instead of MS SQL using the parameter as the value to lookup in the column, it was treating the value of the variable as the column name itself. For example, in the following query, where the parameter ‘key’ has a value of ‘FIVE’:

local R = conn:query(‘SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = “‘..key..'”‘)

You would get an ODBC error indicating there is no column named ‘FIVE’.

I’m not sure if this is a common problem or not, but I found the solution(s) on MS’s website:

http://support.microsoft.com/kb/222664

I think the solution most fitting to most Iguana users is going to be the last bullet point:

“If you are using a DSN connection to SQL Server you can also set Quoted Identifiers off by deselecting the option ‘Use ANSI Quoted Identifiers’ in the ODBC Administrator when you are creating or configuring the DSN to SQL Server. This accomplishes the same thing as using the ‘QuotedID=No’ option in the connection string, as shown in the third work around.”

I wonder if a note regarding this should be included in instructions for configuring an ODBC data source for use with MS SQL Server. Once I had the client make this change, I was able to successfully execute my queries.

Nice – thank you for sharing. Probably the best thing is to plonk a comment on the bottom of one of the pages like:

http://help.interfaceware.com/kb/1243

Where are you from anyways – I think this is the first time we’ve heard from in the forums.

The recommended best practice is not to turn off the “ANSI Quoted Identifiers”.

A better solution is simply to change the query use single quotes ‘ around the value rather than double quotes “.

So changing the code like either of these will work:

local R = conn:query("SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = '"..key.."'") 
local R = conn:query([[SELECT * FROM dbo.MY_TABLE WHERE COLUMN_ONE = ']]..key..[[']])

Why do we prefer this to turning off the “ANSI Quoted Identifiers”?

1. It enables you use table and field names with spaces (example below)
2. Also it (usually) best to write ANSI standard queries

If you have a table named “My Table” with a column named “My Column” (both containing spaces), then having “ANSI Quoted Identifiers” on enables you to write a query like this:

local R = conn:query('SELECT "My Column" FROM "My Table"') -- ANSI standard code
local R = conn:query('SELECT [My Column] FROM [My Table]') -- MSSQL specific code - not ANSI Standard