SQL 7: One column's value is repeated throughout entire result set

[Caleb]

I'm using the following query to look in a log file and show some
statistics. It counts the total number of views and the total number
of unique users who have viewed a particular item (the "id" and
"title" fields are associated with the item).

SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) AS
NumberUniqueUsers, Type, id
FROM ActivityLog
WHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 2005
12:00AM'
GROUP BY Title, Type, hdnId
ORDER BY TopViewed desc

This works fine on SQL Server 2000 (our development machine), but on
SQL Server 7 (our production machine), the title column has the same
value for every row. The other columns show the correct values.

If I remove the "ORDER BY" clause, then it works fine. If I remove
the "COUNT(DISTINCT UID)" column, it works fine. If I totally remove
the WHERE clause, it works fine.

Any ideas? It seems like a bug since it works fine on sql2k. I've
tried adding OPTION (MAXDOP 1) to the end of the query, but that
didn't help.

We're using SQL Server 7.0 sp1, and my boss doesn't want to risk
upgrading to sp4 because it might screw up all of our other
applications. I looked through all of the sp2 through sp4 bug fixes,
and I didn't see anything specifically mentioning this.

Thanks.

[David Rawheiser]

Am I missing something, or do you not have the order by columns in the
result set?

"Caleb" <ca********@gmail.com> wrote in message
news:fd**************************@posting.google.c om...

I'm using the following query to look in a log file and show some
statistics. It counts the total number of views and the total number
of unique users who have viewed a particular item (the "id" and
"title" fields are associated with the item).

SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) AS
NumberUniqueUsers, Type, id
FROM ActivityLog
WHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 2005
12:00AM'
GROUP BY Title, Type, hdnId
ORDER BY TopViewed desc

This works fine on SQL Server 2000 (our development machine), but on
SQL Server 7 (our production machine), the title column has the same
value for every row. The other columns show the correct values.

If I remove the "ORDER BY" clause, then it works fine. If I remove
the "COUNT(DISTINCT UID)" column, it works fine. If I totally remove
the WHERE clause, it works fine.

Any ideas? It seems like a bug since it works fine on sql2k. I've
tried adding OPTION (MAXDOP 1) to the end of the query, but that
didn't help.

We're using SQL Server 7.0 sp1, and my boss doesn't want to risk
upgrading to sp4 because it might screw up all of our other
applications. I looked through all of the sp2 through sp4 bug fixes,
and I didn't see anything specifically mentioning this.

Thanks.

[Erland Sommarskog]

Caleb (ca********@gmail.com) writes:

I'm using the following query to look in a log file and show some
statistics. It counts the total number of views and the total number
of unique users who have viewed a particular item (the "id" and
"title" fields are associated with the item).

SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) AS
NumberUniqueUsers, Type, id
FROM ActivityLog
WHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 2005
12:00AM'
GROUP BY Title, Type, hdnId
ORDER BY TopViewed desc

This works fine on SQL Server 2000 (our development machine), but on
SQL Server 7 (our production machine), the title column has the same
value for every row. The other columns show the correct values.

If I remove the "ORDER BY" clause, then it works fine. If I remove
the "COUNT(DISTINCT UID)" column, it works fine. If I totally remove
the WHERE clause, it works fine.

Any ideas? It seems like a bug since it works fine on sql2k. I've
tried adding OPTION (MAXDOP 1) to the end of the query, but that
didn't help.

We're using SQL Server 7.0 sp1, and my boss doesn't want to risk
upgrading to sp4 because it might screw up all of our other
applications. I looked through all of the sp2 through sp4 bug fixes,
and I didn't see anything specifically mentioning this.

It certainly sounds like a bug to me. If you can produce a repro that
demonstrates the problem I can take a closer look.

How anyone could take responsibility to run SP1 of SQL7 is beyond me.
But unless it's possible to find a rewrite of the query that produces
the correct result, you will have to live with this, if you insist on
staying on SP1.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp