Odd results with SELECT on SQL2005 DB's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Odd results with SELECT on SQL2005 DB's

Author

Topic

ajbuster
Starting Member

17 Posts

Posted - 2006-07-24 : 13:02:22

I have two SQL 2005 DB's with the same schema/tables that are producing different results when executing the same exact SQL statement. The query I'm using is:

SELECT
Store.ID,
MAX(Store.Region) AS Region
FROM Store
GROUP BY Store.ID
ORDER BY Store.Region

When executed on a sample DB, the proper results are displayed. When executed on a different (in this case production) database, the following syntax error appears:

Msg 8127, Level 16, State 1, Line 1
Column "Store.Region" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

The error is incorrect in that the Store.Region is indeed in an aggregate function. I've tried to drop/recreate the table in this DB and still get the error (with or without records in it as well).

The only difference between these two is how they were upgraded to SQL 2005. In the DB that works w/out error, a backup was made in 2000 and restored into 2005. In the other DB, it was detached from 2000 and attached in 2005.

I've found that if I change the query to the following, it will execute on BOTH databases w/out error:

SELECT
Store.ID,
MAX(Store.Region) AS Region
FROM Store
GROUP BY Store.ID
ORDER BY Region

Note the only change is Store. was removed from the Order By clause. Both DB's are of the same collation, so I can't figure out why this is happening. Any thoughts?

ajbuster

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-24 : 13:10:12

Try
declare @s varchar(max)

on the two servers.
I suspect one is in v2000 compatibility mode.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ajbuster
Starting Member

17 Posts

Posted - 2006-07-24 : 14:17:25

Actually, both DB's are on the same server under the same SQL2k5 instance.

ajbuster
Starting Member

17 Posts

Posted - 2006-07-25 : 09:17:38

Update on this. The DB was DTS'd to another fresh DB, and now the query works just fine. It can't be data related. Any ideas how I can fix this without having to DTS?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-25 : 09:27:28

did you try
declare @s varchar(max)

on both databases.
Nothing you have said indicates that one isn't in v2000 compatibility mode.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-25 : 10:02:29

the error is correct (no pun intended)
not sure why 2005 is allowing it to happen though (in the other db)

if you try order by max(store.region), this should also be ok

--------------------
keeping it simple...

ajbuster
Starting Member

17 Posts

Posted - 2006-07-25 : 10:39:35

quote:
Originally posted by nr

did you try
declare @s varchar(max)

on both databases.
Nothing you have said indicates that one isn't in v2000 compatibility mode.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sorry, thought this was a server setting not an individual database setting. I've executed that statement against both DB's but not sure what it should do. I still get the error on the one after executing it, and both just say "Command(s) completed successfully." when running it. Is there a command that shows me if a DB is in 2000 compat. mode? Thanks for your help,

ajbuster

ajbuster
Starting Member

17 Posts

Posted - 2006-07-25 : 10:42:40

quote:
Originally posted by jen

the error is correct (no pun intended)
not sure why 2005 is allowing it to happen though (in the other db)

if you try order by max(store.region), this should also be ok

--------------------
keeping it simple...

So is this something that has changed from SQL 2000? I can only recreate this error on the one DB in SQL 2005...all others I've tried in 2000 or 2005 execute w/out error.

Also, I unfortunately cannot modify the SQL statement as it is in the source code of an application we use. The chance of them changing it for us is VERY slim, so I need to figure out why it is happening and fix it if possible within the DB and not the SQL code. Thanks for your help!

ajbuster

ajbuster
Starting Member

17 Posts

Posted - 2006-07-25 : 10:48:17

Ok, nr nailed it. I ran this and found that one DB was in 80 mode (2000), and the one that was failing was in 90 mode (2005):
sp_dbcmptlevel dbname

Once I set the db to 80 mode (sp_dbcmptlevel dbname, 80), the statement executed fine. So why has this changed in 2005? Seems odd to me I guess...

Thanks for all the help,

ajbuster

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-25 : 10:50:03

you can right click on the database in mangement studio and it is at the top under options.

also sp_dbcmptlevel 'mydb'

just tried

select max(name) as name
from sysobjects
group by id
order by sysobjects.name

works in v2000 compatibility mode - fails in v2005 so I guess that's the problem.

the declare varchar(max) works in both so that wasn't a good test.

You will want the code to work on v2005 so I think you will have to change it. Easy if everything is in stored procedures.

In v2000 mode the object qualifier for the identifier is being ignored which could be considered a bug. The code is incorrect so even though it happens to work it should be changed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ajbuster
Starting Member

17 Posts

Posted - 2006-07-25 : 11:01:22

quote:
Originally posted by nr

you can right click on the database in mangement studio and it is at the top under options.

also sp_dbcmptlevel 'mydb'

just tried

select max(name) as name
from sysobjects
group by id
order by sysobjects.name

works in v2000 compatibility mode - fails in v2005 so I guess that's the problem.

the declare varchar(max) works in both so that wasn't a good test.

You will want the code to work on v2005 so I think you will have to change it. Easy if everything is in stored procedures.

In v2000 mode the object qualifier for the identifier is being ignored which could be considered a bug. The code is incorrect so even though it happens to work it should be changed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks nr. I'll have to see if the guys who made the app will change the source code. This isn't in a stored proc unfortunately, so this could be tough! For now we'll update the DB to 80 mode and wait for an update. Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources