March 26, 2014 at 10:20 pm
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
March 26, 2014 at 10:51 pm
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/
March 26, 2014 at 10:59 pm
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."
March 27, 2014 at 2:58 am
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];
March 27, 2014 at 9:37 am
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.
March 27, 2014 at 10:37 am
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)
March 27, 2014 at 11:05 am
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?
[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]
March 27, 2014 at 3:23 pm
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?
March 27, 2014 at 3:28 pm
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.
March 27, 2014 at 4:52 pm
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/
March 27, 2014 at 5:15 pm
Hi,
Yes, If the database is saved in SQL server 2008 (100) compatibility mode, then It makes NO difference if I bracket the host, database, schema and table names in the select statement. I get the error. If, on the other hand, I save the database compatibility to 2005 (90) everything works fine. It seems that the problem was because the database was saved in 2008 (100) compatibility mode. I would like to see if someone can duplicate this problem, so I will know if I have a problem in my database or if it is a problem with the 2008 format.
Summary:
Select * from [host].[database].[schema].
WILL FAIL for databases saved in 2008 compatibility (100) mode for a table name of STOPLIST.
Everything runs as expected in 2005 (90) compatibility.
March 28, 2014 at 5:26 am
I think the compatibility thing is a red herring--the STOPLIST keyword wasn't introduced until SQL 2008, so presumably one of the things that a database in compatibility mode 90 has to do is ignore the fact it's a keyword so it doesn't break old code.
Out of interest, why are you only using either STOPLIST or the fully-qualified name with square brackets? Have you just tried enclosing STOPLIST in square brackets and not bothering with the whole database.table.schema stuff?
March 28, 2014 at 9:01 am
Hi Paul-
Yes, all of my logic was originally built without bracket qualifiers. The complete tale is: We recently upgraded the database in accordance with the migration instructions provided by our vendor to remove the 2000 compatibility flag from the database (the application that needed that flag set used *= and =* to denote right and left outer joins).
Apparently, the upgrade set the compatibility flag to 2008, and when I was testing some of my integration and reports in our test vlan, things bombed horribly. I traced the problem to the stoplist keyword. I then posted my inquiry here. Yesterday I set the database compatibility to 2005 and everything works as it should (without qualifying brackets). If I change the compatibility flag back to 2008, anything referencing the STOPLIST table fails horribly with or without the brackets. I have my DBA talking with Ellucian (the application vendor) to see if setting the compatibility flag to 2005 will cause anything to break. Very strange.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply