SELECT problem when column name contains [ ] characters

[CharlesEF]

Hi All,

I have run into another problem that is eating my lunch. Should be
simple but I am having one heck of a time. Please look at this SELECT
statement:

SELECT [StateName] FROM States WHERE [[2DigitCountryCode]] = "US";

[2DigitCountryCode] is the SQL column name (because it sarts with 2?).
As the statement is shown I get the error message: [Microsoft][ODBC SQL
Server Driver][SQL Server]Unclosed quotation mark before the character
string '[2DigitCountryCode] = "US"'. What quotation marks is this
talking about?

I have gotten the SELECT statement to run, without error message, when
I change it to:

SELECT [StateName] FROM States WHERE '[[2DigitCountryCode]]' = "US";

But, this statement returns nothing. I know there are records to
return so I did something wrong. I've tried () and {} but problem
remains.

I am coding an ASP page using VBScript, connecting to an SQL 2000
server via ADO. I have tried so many things but nothing works for me.
My head hurts....

Can anyone see what I have done wrong? SQL Books Online was not much
help to me and I could not find any previous posts regarding my
problem.
Thanks for any help,

Charles

[Dave Anderson]

Ch*******@MailandNews.Com wrote:

SELECT [StateName] FROM States WHERE [[2DigitCountryCode]] = "US";

[2DigitCountryCode] is the SQL column name (because it sarts with 2?).
As the statement is shown I get the error message: [Microsoft][ODBC
SQL Server Driver][SQL Server]Unclosed quotation mark before the
character string '[2DigitCountryCode] = "US"'. What quotation marks
is this talking about?

It seems like you think brackets are required around column names. They
usually are not. SQL Server requires them when the column name makes parsing
ambiguous (when the column name is a SQL keyword or contains spaces, for
example). This is true not just of columns, but any user-named objects.

So it is incorrect to claim that [2DigitCountryCode] is the column name. The
brackets are there for the parser. The column name is 2DigitCountryCode.

Short answer: change your select statement to:

SELECT StateName FROM States WHERE [2DigitCountryCode] = 'US'

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[CharlesEF]

Hi,

My project started out as a Access database. I defined all tables,
indexes and relationships before I used the 'Upsizing Wizard' to
transfer the whole database over to SQL (in my case MSDE SP3). The
wizard placed brackets around every field, in every table, that started
with a number or had the - character in it. When I look at the table
definitions the brackets are there, part of the field name.

I had already tried leaving the 2nd set of brackets off and the error
message I get is that the 2DigitCountyCode (notice no brackets) field
was not found. Are you saying I should remove these brackets from
every table that has fields with them?

True, I do not know a lot about SQL but I have read that brackets are
required for every field that has numbers or special characters (the
minus sign, in my case).

Should I remove the brackets from every table? I have created a 'View'
in SQL and I have gotten the SELECT to return the correct records.
But, it does not have a 'View SQL' menu item so I can not see what the
actual SQL query is.
Thanks for any help,

Charles

[CharlesEF]

Hi,

As a test I have tried to remove the brackets from the
2DigitCountryCode field. Once I leave the field they are automatically
replaced. Seems these brackets are required in the SQL database but
not in the Access version of the database.

SELECT StateName FROM States WHERE [2DigitCountryCode] = 'US'

Works fine when used on the Access version of the database. If fails
when used on the SQL version of the database (field not found error
message).

I found a speaker icon that points up in the SQL view dialog. When I
hover my mose over it I get this: CRITERIA: {[2DigitCountryCode] =
N'US'}
I have tried using that statement for my WHERE clause but my SELECT
query still errors out.

I'm still scratching my head.
Thanks for any help,

Charles

[Dave Anderson]

Ch*******@MailandNews.Com wrote:

My project started out as a Access database. I defined all tables,
indexes and relationships before I used the 'Upsizing Wizard' to
transfer the whole database over to SQL (in my case MSDE SP3). The
wizard placed brackets around every field, in every table, that
started with a number or had the - character in it. When I look at
the table definitions the brackets are there, part of the field name.

When you say "look at the table definitions", do you mean you are looking in
Enterprise Manager? If that is the case, just use Enterprise Manager to
create a view with one of your tables and examine the SQL that is created.
This ought to clear thing up quite a bit.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[CharlesEF]

Hi,

I do not have Enterprise Manager. I am using Access XP to view the
tables and field names. I used the 'Create view in designer' option to
create my SQL view. It does work as it should here, on my computer,
but I can not see the actual SQL statement. I am trying to figure out
the actual SQL query statement so I can use it on the SQL 2000 server
on my web site. I use ADO and VBScript (ASP) to access the SQL server.

The error message I posted in my first post is the actual message
returned from SQL 2000 using ADO.
Thanks for your time,

Charles

[Bob Barrows [MVP]]

Ch*******@MailandNews.Com wrote:

Hi,

My project started out as a Access database. I defined all tables,
indexes and relationships before I used the 'Upsizing Wizard' to
transfer the whole database over to SQL (in my case MSDE SP3). The
wizard placed brackets around every field, in every table, that
started with a number or had the - character in it.
No, it could not have made a bracket be part of the column name. See below
as well as the article cited in my footnote. *
When I look at
the table definitions the brackets are there, part of the field name.

The tool you are using to view these names is putting the brackets there.
I had already tried leaving the 2nd set of brackets off and the error
message I get is that the 2DigitCountyCode (notice no brackets) field
Do you mean that you did this?:
....where [2DigitCountyCode] ...
was not found. If this column name was not found when you used a single set of brackets,
then I would suggest doing this:

EXEC sp_columns @table_name='States'

to find out the real names of your columns.
Regarding "2DigitCountyCode": this name is not a legal identifier:

The first character must be one of the following:
a.. A letter as defined by the Unicode Standard 2.0. The Unicode
definition of letters includes Latin characters from a through z and from A
through Z, in addition to letter characters from other languages.

This means that if you can't change the name of the field (which I highly
recommend that you do), then you must surround the name with brackets
whenever you use it in a sql statement ... one set of brackets.
Are you saying I should remove these brackets from
every table that has fields with them?

You can't: they aren't there*.
The tool you are using to view these names is putting the brackets there. True, I do not know a lot about SQL but I have read that brackets are
required for every field that has numbers or special characters (the
minus sign, in my case).
You are almost correct. Amend your sentence to say:
"When using names containing numbers or special characters in SQL
statements, you must surround those names with brackets in order to prevent
the query parser from generating an error."

Should I remove the brackets from every table?
You can't. they aren't there. The tool you are using to view these names is
putting the brackets there.
I have created a
'View' in SQL and I have gotten the SELECT to return the correct
records. But, it does not have a 'View SQL' menu item so I can not
see what the actual SQL query is.

How did you create the view? Did you use Access? When generating SQL, Access
will always put brackets around object identifiers whether they are needed
or not.

You can see the statement by querying the INFORMATION_SCHEMA.VIEWS view:

SELECT
t.VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS t WHERE
TABLE_CATALOG = 'YourDatabaseName' AND
TABLE_NAME = 'YourViewName'
HTH,
Bob Barrows

* Article in BOL called "Using Identifiers":
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acdata.chm::/ac_8_con_03_6e9e.htm
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[CharlesEF]

Hi,

When I look at
the table definitions the brackets are there, part of the field name.
The tool you are using to view these names is putting the brackets
there.
I had already tried leaving the 2nd set of brackets off and the error
message I get is that the 2DigitCountyCode (notice no brackets) field
Do you mean that you did this?:
....where [2DigitCountyCode] ...

Yes, that is exactly what I did.
I have created a
'View' in SQL and I have gotten the SELECT to return the correct
records. But, it does not have a 'View SQL' menu item so I can not
see what the actual SQL query is.

How did you create the view? Did you use Access? When generating SQL,
Access
will always put brackets around object identifiers whether they are
needed
or not.

Yes, I used Access XP to create the SQL view.

You can see the statement by querying the INFORMATION_SCHEMA.VIEWS
view:

SELECT
t.VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS t WHERE
TABLE_CATALOG = 'YourDatabaseName' AND
TABLE_NAME = 'YourViewName'

That is all greek to me, sorry to say. I will have to read the links
you pointed to and see if it will help me understand.

To solve the problem I have renamed all the affected field names. I
did not know I have done something illegal. Seems this is the only
correct way to fix this problem.
Thanks, everyone, for the input,

Charles