Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

STOPLIST Key Word Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2014 10:20 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:25 PM
Points: 19, Visits: 145
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
Post #1555249
Posted Wednesday, March 26, 2014 10:51 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #1555256
Posted Wednesday, March 26, 2014 10:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1555258
Posted Thursday, March 27, 2014 2:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 2,018, Visits: 5,502
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

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];

Post #1555317
Posted Thursday, March 27, 2014 9:37 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:25 PM
Points: 19, Visits: 145
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.

  Post Attachments 
shot.png (18 views, 64.89 KB)
Post #1555509
Posted Thursday, March 27, 2014 10:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 2,018, Visits: 5,502
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)
Post #1555545
Posted Thursday, March 27, 2014 11:05 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #1555566
Posted Thursday, March 27, 2014 3:23 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:25 PM
Points: 19, Visits: 145
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?
Post #1555673
Posted Thursday, March 27, 2014 3:28 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:25 PM
Points: 19, Visits: 145
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.
Post #1555677
Posted Thursday, March 27, 2014 4:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #1555692
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse