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 '80004005' while accessing the ASP pages

hi,
I am having while accessing the asp pages in our the problem is
"Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
/pressreleases/pressroom.asp, line 90 "
If i restart the system then i am able to connect the database and working
well.. but afterdays again i am getting same error.. so please help me in
this. Thanks in advance
rajavelWhy are you using named pipes? See http://www.aspfaq.com/2126 for optimal
connection strings...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rajavelu Dhakshinamurthy" <rajavelu_d@.dev.visualsoft-tech.com> wrote in
message news:#xySVGH7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> hi,
> I am having while accessing the asp pages in our the problem is
> "Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
> /pressreleases/pressroom.asp, line 90 "
>
> If i restart the system then i am able to connect the database and working
> well.. but afterdays again i am getting same error.. so please help me in
> this. Thanks in advance
> rajavel
>
>|||Either you have your database open while you are executing an asp page which
either reads or writes from a datagase or,
You don't have the permission to read/write your database
Click on My Computer icon on your desktop, keep clicking until you find the
folder where your database is. Right click on the database and select
properties and make sure the correct permissions are selected.
regards,
harsh.
"Rajavelu Dhakshinamurthy" <rajavelu_d@.dev.visualsoft-tech.com> wrote in
message news:#xySVGH7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> hi,
> I am having while accessing the asp pages in our the problem is
> "Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
> /pressreleases/pressroom.asp, line 90 "
>
> If i restart the system then i am able to connect the database and working
> well.. but afterdays again i am getting same error.. so please help me in
> this. Thanks in advance
> rajavel
>
>|||In article <uukjTUH7DHA.2760@.TK2MSFTNGP09.phx.gbl>, "harsh" <harshalmistry@.hotmail.com> wrote:
>Either you have your database open while you are executing an asp page which
>either reads or writes from a datagase or,
>You don't have the permission to read/write your database
>Click on My Computer icon on your desktop, keep clicking until you find the
>folder where your database is. Right click on the database and select
>properties and make sure the correct permissions are selected.
>regards,
>harsh.
Huh?
I'm afraid you didn't catch the fact that this is a SQL Server DB.
SQL Server doesn't work like that.
What exactly is the SQL statement that is bombing out doing?
Have you tried running profiler when this occurs?|||Check this general guide how to troubleshoot the 80004005 error as well:
http://support.microsoft.com/default.aspx?scid=kb;en-us;306518.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Rajavelu Dhakshinamurthy" <rajavelu_d@.dev.visualsoft-tech.com> wrote in
message news:#xySVGH7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> hi,
> I am having while accessing the asp pages in our the problem is
> "Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
> /pressreleases/pressroom.asp, line 90 "
>
> If i restart the system then i am able to connect the database and working
> well.. but afterdays again i am getting same error.. so please help me in
> this. Thanks in advance
> rajavel
>
>

error '80004005' while accessing the ASP pages

hi,
I am having while accessing the asp pages in our the problem is
"Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
/pressreleases/pressroom.asp, line 90 "
If i restart the system then i am able to connect the database and working
well.. but afterdays again i am getting same error.. so please help me in
this. Thanks in advance
rajavelWhy are you using named pipes? See http://www.aspfaq.com/2126 for optimal
connection strings...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rajavelu Dhakshinamurthy" <rajavelu_d@.dev.visualsoft-tech.com> wrote in
message news:#xySVGH7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> hi,
> I am having while accessing the asp pages in our the problem is
> "Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
> /pressreleases/pressroom.asp, line 90 "
>
> If i restart the system then i am able to connect the database and working
> well.. but afterdays again i am getting same error.. so please help me in
> this. Thanks in advance
> rajavel
>
>|||Either you have your database open while you are executing an asp page which
either reads or writes from a datagase or,
You don't have the permission to read/write your database
Click on My Computer icon on your desktop, keep clicking until you find the
folder where your database is. Right click on the database and select
properties and make sure the correct permissions are selected.
regards,
harsh.
"Rajavelu Dhakshinamurthy" <rajavelu_d@.dev.visualsoft-tech.com> wrote in
message news:#xySVGH7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> hi,
> I am having while accessing the asp pages in our the problem is
> "Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
> /pressreleases/pressroom.asp, line 90 "
>
> If i restart the system then i am able to connect the database and working
> well.. but afterdays again i am getting same error.. so please help me in
> this. Thanks in advance
> rajavel
>
>|||In article <uukjTUH7DHA.2760@.TK2MSFTNGP09.phx.gbl>, "harsh" <harshalmistry@.hotmail.com>
wrote:
>Either you have your database open while you are executing an asp page whic
h
>either reads or writes from a datagase or,
>You don't have the permission to read/write your database
>Click on My Computer icon on your desktop, keep clicking until you find the
>folder where your database is. Right click on the database and select
>properties and make sure the correct permissions are selected.
>regards,
>harsh.
Huh?
I'm afraid you didn't catch the fact that this is a SQL Server DB.
SQL Server doesn't work like that.
What exactly is the SQL statement that is bombing out doing?
Have you tried running profiler when this occurs?|||Check this general guide how to troubleshoot the 80004005 error as well:
http://support.microsoft.com/defaul...b;en-us;306518.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Rajavelu Dhakshinamurthy" <rajavelu_d@.dev.visualsoft-tech.com> wrote in
message news:#xySVGH7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> hi,
> I am having while accessing the asp pages in our the problem is
> "Microsoft OLE DB Provider for ODBC Drivers error '80004005'
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
> /pressreleases/pressroom.asp, line 90 "
>
> If i restart the system then i am able to connect the database and working
> well.. but afterdays again i am getting same error.. so please help me in
> this. Thanks in advance
> rajavel
>
>