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 Thursday, March 27, 2014 5:15 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:25 PM
Points: 19, Visits: 145
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].[table] WILL FAIL for databases saved in 2008 compatibility (100) mode for a table name of STOPLIST.

Everything runs as expected in 2005 (90) compatibility.
Post #1555697
Posted Friday, March 28, 2014 5:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 1,594, Visits: 5,633
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?
Post #1555837
Posted Friday, March 28, 2014 9:01 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 1:25 PM
Points: 19, Visits: 145
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.
Post #1555977
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse