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 400 Bad Request in Reporting Services

Hi,
I get this strange error when I try to acess my SQL Reporting Services application.
I'm using SQL 2005 Beta 2. The installation was OK and the server is working fine. That is the only problem.
I checked the logs and a traced the database, but couldn't find someful usefull (at leat for me).
Here's the SQL2005 Report Server WebApp log:

w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing MaxScheduleWait to default value of '1' second(s) because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing DatabaseQueryTimeout to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing InstanceName to default value of '' because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing ProcessRecycleOptions to default value of '0' because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing RunningRequestsScavengerCycle to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing RunningRequestsDbCycle to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing RunningRequestsAge to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing CleanupCycleMinutes to default value of '10' minute(s) because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing WatsonFlags to default value of '1064' because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing WatsonDumpOnExceptions to default value of 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException' because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to default value of 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing SecureConnectionLevel to default value of '1' because it was not specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
w3wp!library!1!25/08/2005-12:57:13:: i INFO: Initializing WebServiceUseFileShareStorage to default value of 'False' because it was not specified in Configuration file.
w3wp!ui!1!25/8/2005-12:57:15:: e ERROR: The request failed with HTTP status 400: Bad Request.
w3wp!ui!1!25/8/2005-12:57:15:: e ERROR: HTTP status code --> 500
---Details---
System.Net.WebException: The request failed with HTTP status 400: Bad Request.
at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!25/8/2005-12:57:20:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
w3wp!ui!5!25/8/2005-12:57:44:: e ERROR: The request failed with HTTP status 400: Bad Request.
w3wp!ui!5!25/8/2005-12:57:44:: e ERROR: HTTP status code --> 500

Thanks in advance,

Anderson

Good Evening Anderson!
I had some "weird" problems immediately after I installed the SQL2005 JUNE CTP when initially launching RS. I do not know for cetain what your problems relates to above - but for some strange reason with my problem which I getting similar issues to your post was that the Virtual Directories (Reports and ReportServer) within IIS were somehow messed up!
So I deleted the two virtual directories and recreated and ensured I was not using 'Enable Anonymous Access' and I specified Integrated Windows Authentication.
Restarted IIS and everything was ok after that.
What version of Visual Studio are you using -? I had similar problems with bad requests without registering RS after install to .Net as well.
Best Regards,
Joe|||

Hi, Joe

Thanks for your interest.

I was having some trouble with the RS db. I've followed the steps below and now everything's ok:

1. Unistall RS;
2. Ensure RS database is deleted;
3. Reinstall RS;
4. Use RS Configuration Tool to setup everything.
Actually, I'm using SQL2K5April CTP and VS2K5 Beta 2.

Best regards,

Anderson

|||Hello Anderson!
these little things you have to go through to make things work sometimes!
Did you know the August SQL 2005 CTP is now available as well?
Best regards,
joe