Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


STOPLIST Key Word


STOPLIST Key Word

Author
Message
kmdavisjr
kmdavisjr
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 148
I have a bit of an interesting problem. I have a table called STOPLIST in our student information database. I have tried using select * FROM [hostname].[databasename].[dbo].[STOPLIST] without success. What can I do to ensure that the database engine will recognize the database object as a table and not a keyword? This did not happen in SQL Server 2005 or 2008 R2 to my knowledge. Changing table names is out of the question. I am trying to read the data from the a linked server. This does not seem to matter, because I get the same result if I try to query the table directly on the host using SSMS.

Thanks
Adi Cohn
Adi Cohn
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2645 Visits: 6329
I've just ran the small script that you can see bellow on SQL Server 2012 and it worked without any problems, so I don't think that the problem is that it doesn't recognize the table because of its name. Try to run the script in your environment and see if it works.
Problems that I've encountered in the past that may or may not be relevant in your case are – you are misspelling the object, object was dropped and you are not aware about it, the server is case sensitive and the table's name is not just with capital letters, you are using the wrong server name/database name/schema name when you use the 4 name convention.


use tempdb
go
create table STOPLIST (I INT)
go

insert into STOPLIST (I) values (1)

select * from STOPLIST
go

drop table STOPLIST
go




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/
free_mascot
free_mascot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3157 Visits: 2235
What is the error message? Do you have proper credentials for linked server and/or local machine?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Eirikur Eiriksson
Eirikur Eiriksson
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8791 Visits: 18214
Quick question, are you using full-text search on the server?

Edit:
The server is pretty smart when it comes to interpreting even the strangest of user's naming w00t

As an example, this should run on any sql server which does not have full text search enabled, fails if it is.

use tempdb;
go
create table [STOPLIST] ( [tinyint] int null, [char] char(1) null);
insert into STOPLIST(tinyint,char) values (-2147483647,'a'),(0,'b'),(1,'c'),(2147483647,'d');
select * from STOPLIST;
drop table [STOPLIST];


kmdavisjr
kmdavisjr
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 148
Hi Everyone. I have full server rights (OS and SQL) on the host. The screenshot is a capture of the error I am getting. Full text is enabled but I we are not using it.
Attachments
shot.png (24 views, 64.00 KB)
Eirikur Eiriksson
Eirikur Eiriksson
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8791 Visits: 18214
kmdavisjr (3/27/2014)
Hi Everyone. I have full server rights (OS and SQL) on the host. The screenshot is a capture of the error I am getting. Full text is enabled but I we are not using it.


The error in the screenshot is the same as I get on FTS enabled db. Not certain it will work but you can try running sp_fulltextsearch 'disable' (if I remember it right)
RBarryYoung
RBarryYoung
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10400 Visits: 9517
kmdavisjr (3/27/2014)
Hi Everyone. I have full server rights (OS and SQL) on the host. The screenshot is a capture of the error I am getting. Full text is enabled but I we are not using it.


I get this same error when I execute this command ("select * from STOPLIST") on my FTS databases. However this is not the command that you originally said that you were executing.

When I execute that command (
select * FROM [hostname].[databasename].[dbo].[STOPLIST] 

) it works fine.

Can you show us the error from that command?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
kmdavisjr
kmdavisjr
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 148
It is getting curiouser and curiouser! I looked at the database and it was in 2008 (100) compatibility mode. I dropped it to 2005 (90) compatibility mode, and the fail went away. I put it back up to 2008 and it the error came back. Can someone verify if this is a problem for database saved in 2008 compatibility?
kmdavisjr
kmdavisjr
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 148
Hi Barry,

It is a simple "generic error". I have a screen shot of it a few posts above this one. It seems to have something to do with a database being saved in 2008 (100) compatible mode. When I dropped it down to 90, the fail went away. I have been able to duplicate it several times without problem, so there is definitely something up.
Adi Cohn
Adi Cohn
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2645 Visits: 6329
The SQL statement that you are using in the screen shot that causes the runtime error is different from the statement that you wrote in the original question. In the original question, you used square brackets and in the screen shot you didn't ([stoplist] vs. stoplist). That makes a big difference because the square brackets should let you use none standard names and prevent an error message. Do you still get an error when you use the square brackets?

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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search