Select * from [tablename] works but select * from tablename does not work....how to fix this?

Question

Post reply

Select * from [tablename] works but select * from tablename does not work....how to fix this?

RPSQL

Hall of Fame

Points: 3229
More actions
August 25, 2008 at 1:00 pm

#127123

Select * from [tablename] works but select * from tablename does not work....how to fix this?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

What is the table name here? Is it a sql reserved keyword?

MJ

RPSQL Hall of Fame Points: 3229 More actions · Answer 2

No it is not a reserved keyword...It is same for all the tables on a particular database.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

I suspect it has do to something with database collation. Also, check sys.objects view results to verify the tablenames there.

Are they having [] in their name in sys.objects too?

MJ

RPSQL Hall of Fame Points: 3229 More actions · Answer 4

on sysobject it is having name as tablename not as [tablename] or "tablename"

I forget to mention that it also works if we do select * from "tablename"

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Can you give us a few table names for tables where this occurs?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

RPSQL Hall of Fame Points: 3229 More actions · Answer 6

table names are,

emp

msa

reports

users

users_groups

users_groups_rel

xref...

etc..etc..etc...

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

Is this database migrated or came from Microsoft Access?

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

RPSql (8/25/2008)
Select * from [tablename] works but select * from tablename does not work....how to fix this?

Please define "works" and "does not work".

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

fortuneramesh Grasshopper Points: 17 More actions · Answer 9

fortuneramesh

Grasshopper

Points: 17

August 25, 2008 at 8:39 pm

#862879

hi,

chek out the status of quoted_identifier

rajiniforu Ten Centuries Points: 1147 More actions · Answer 10

Yeah I echo Ramesh..this has something to do with "QUOTED IDENTIFIER"

Check if the "QUOTED IDENTIFIER" is set to ON and then set it back to OFF

SET QUOTED_IDENTIFIER OFF

GO

-Rajini

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 11

I don’t know what causes this behavior, but I don’t think that it has something to do with the QUOTED IDENTIFIER set option. The reason I don’t think so is that when this option is set to on, then you can use double quotation marks to identify the objects, but you don’t have to use it. This is not the behavior that rpSQL wrote.

Few questions about this behavior:

1) Do you get the same behavior from each session or are there sessions that you don’t have to use delimiters to surround the tables’ name?

2) If there are sessions that you don’t need to use delimiters can you write a bit more about it (for example it happens only for a specific login, or it happens only from a specific machine etc’)

3) When you say that select * from tablename doesn’t work – what do you mean? Do you get an error message? If so, can you post the error message?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

RPSQL Hall of Fame Points: 3229 More actions · Answer 12

Adi

Answers:

1) I get same behavior for all the sessions by sql ids as well as windows authentication id that have all the permission..i also tried with "sa" user.

2) refer answer 1.

3) It doesn't work means, if suppose the following is my query

select * from emp

then o/p is

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'emp'.

now if I do select * from [emp]

OR

select * from "emp"

then it gives output and writes all the rows of table as o/p.

Thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

RPSql (8/26/2008)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'emp'.

Ah, OK. You see "Incorrect syntax" is a much different error than "Invalid object name."

Hmmm...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 14

I wonder what would happen if you prefix the table name with the schema and did not use the square brackets.

I'm not sure what that would prove, just curious.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]