Error 8120 when selecting MAX(...)

Hi Folks,

I have a very strange problem with the MSSQL JDBC drivers (SP1).
I am calling executeQuery() on a very simple query:

SELECT MAX(fulltext_id) FROM contractor

I am getting an error 8120 (column is not contained in an aggregate-- see stack trace below). It thinks that 'id' is part of the column list, yet it is not.

Now, the strange thing is, when I run SQL Profiler, I see this line:

exec sp_cursoropen @.P1 output, N'SELECT MAX(fulltext_id) , * FROM contractor', @.P2 output, @.P3 output, @.P4 output

It seems that someone (the driver?) added a comma and a "*" after the MAX(fulltext_id) clause! What gives?

Thanks much,
Steve

===

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Column 'contractor.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest. openCursor(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.execute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery Internal(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery (Unknown Source)I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

blindman|||Originally posted by blindman
I don't know why the Java is throwing that * in there, but that is definitely what is causing the problem. That is not a valid SQL statement.

blindman

Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

I am still kinda confused, but opening the cursor as read-only fixed the problem... :?|||Originally posted by sehugg
Actually I found what was wrong. It appears I was opening the ResultSet as an updatable cursor. The SQL Server driver apparently tacks on a "*" to make the query select all rows (so any row in the query can be updated?)

I am still kinda confused, but opening the cursor as read-only fixed the problem... :?

Obviously a bug in the JDBC driver.

Error 20068, Article Column Max 256

I'm trying to replicate a table that now has more than 256 columns. SQL2000
chokes when creating replication with error 20068, stating that the maximum
number of columns allowed for an article is 256.
The workarounds I am aware of involve creating two tables and using views to
abstract this change from the front end application. There is no way my
client will pay for a change as drastic as this with the only benifit is that
replication will now be allowed to work.
I have two questions:
a. Is there a better workaround?
b. Does SQL2005 solve this issue (I might be better able to sell my client
on an upgrade)?
partitioning the table as you describe is the standard workaround in SQL
Serever 2000.
In SQL Server 2005, for merge the max no of columns is 246. For
transactional and snapshot it is 1000 (995 with Oracle).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the application using this table uses a stored procedure to make the
updates you may be able to replicate the execution of the stored procedure -
however there is the same procedure limit. Another option is to use a
trigger to write to two "audit" tables and replicate these audit tables to
the subscriber where they in turn assemble the final row.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dtaylo75" <dtaylo75@.discussions.microsoft.com> wrote in message
news:EE67681F-C2FE-489B-88B1-1A69F719DE6D@.microsoft.com...
> I'm trying to replicate a table that now has more than 256 columns.
> SQL2000
> chokes when creating replication with error 20068, stating that the
> maximum
> number of columns allowed for an article is 256.
> The workarounds I am aware of involve creating two tables and using views
> to
> abstract this change from the front end application. There is no way my
> client will pay for a change as drastic as this with the only benifit is
> that
> replication will now be allowed to work.
> I have two questions:
> a. Is there a better workaround?
> b. Does SQL2005 solve this issue (I might be better able to sell my client
> on an upgrade)?