0

Why does the below query work in SQL Server 2008, but not in SQL Server 2014?

SELECT sysobj.ects.* FROM sysobjects

Notice how the table name has the dot between sysobj and ects in the SELECT statement, but the FROM statement doesn't have the dot. This happens with any table. You can put the dot anywhere in the table name and it will still run the query fine.

On 2014 you will see this error:

The column prefix 'sysobj.ects' does not match with a table name or alias name used in the query.

EDIT: Compatibility mode on the 2008 server is set to SQL Server 2000.

Jande
  • 1,695
  • 2
  • 20
  • 32
iheartcsharp
  • 1,279
  • 1
  • 14
  • 22
  • Why do you have a '.' in there? SELECT sysobjects.* from sysobjects works in both. Try using the INFORMATION_SCHEMA views to access system objects. – JimmyV Mar 28 '16 at 20:40
  • @JimmyV, I'm not concerned about accessing the tables. It just to demonstrate the issue. You can do this with any table and you can see the same behavior. – iheartcsharp Mar 28 '16 at 21:12
  • I tried to reproduce but my compatibility level only goes back to SQL Server 2005. I don't understand how the query you posted would ever function. What is it meant to be doing? Of course if you removed the dot from the middle it would make perfect sense. Why would you have legacy code with a meaningless dot banged in the middle? – Nick.Mc Mar 28 '16 at 21:22
  • I can confirm that this query runs on SQL2008 with compatibility level set to 2000. In fact, you can put (up to 3) dots anywhere you like – devio Mar 29 '16 at 13:04
  • @Nick.McDermaid I think some developer from 10 years back put the dot in there for whatever reason (or it may be just a typo). But now since we have moved to 2014 and are using the latest compatibility level those stored procs are failing. I think we will have to test all the sprocs and fix the ones that use this syntax. – iheartcsharp Mar 29 '16 at 18:03
  • You could try this code to go through and check all SP's compile OK http://stackoverflow.com/questions/3027399/how-to-check-all-stored-procedure-is-ok-in-sql-server – Nick.Mc Mar 29 '16 at 22:55

1 Answers1

0

It doesn't run for me in SQL Server 2008 R2. I get the same error you say you are getting in 2014. I don't see how this can work for you.

Noel

Isaac
  • 3,240
  • 2
  • 24
  • 31
  • The compatibility level on the database is set to SQL Server 2000, so that maybe why it doesn't run for you. I'll update my post to mention that. – iheartcsharp Mar 28 '16 at 21:13