Microsoft KB Archive/311540

= A &quot;COMPUTE BY statements not supported&quot; exception occurs when you use the COMPUTE BY clause in SQL Server =

Article ID: 311540

Article Last Modified on 5/16/2007

-

APPLIES TO


 * Microsoft ADO.NET 2.0
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q311540



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data.OleDb
 * System.Data.SqlClient



SYMPTOMS
When you attempt to use the COMPUTE BY clause with the Microsoft SQL Server .NET Data Provider, you receive the following exception:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll.

Additional information: COMPUTE BY statements not supported.

Note The COMPUTE BY clause in a SQL Server statement generates subtotals within the result set.



CAUSE
This behavior is caused by a limitation of the SQL Server .NET Data Provider.

The SQL Server .NET Data Provider does not support all SQL syntax statements.



RESOLUTION
To resolve this issue, use ADO through COM Interop, or use the OLE DB .NET Data Provider.



STATUS
This behavior is by design.



Steps to Reproduce the Problem
  Create a stored procedure &quot;myProc&quot; in the Pubs database by running the following query in SQL Server Query Analyzer: CREATE PROCEDURE MyProc AS SELECT Type, Price FROM Titles ORDER BY Type COMPUTE SUM(Price) BY Type GO  Start Visual Studio .NET. Create a new Windows application in Visual Basic .NET. Make sure that your project contains a reference to the System.Data namespace. Place a command button on Form1. Change the Name property of the button to &quot;btnTest&quot; and the Text property to &quot;Test.&quot;</li>  Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add this to the General Declarations section of Form1, as follows: Imports System Imports System.Data.OleDb Imports System.Data.SqlClient </li>  Paste the following code in the code window after the region &quot;Windows Form Designer generated code&quot;:

Note You must replace User ID with an account that has the appropriate permissions to perform these operations on the database. Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click Dim myConnString As String = _ &quot;User ID= ;password= ;Initial Catalog=pubs;Data Source=myServer&quot; Dim myConnection As New SqlConnection(myConnString) Dim myCommand As New SqlCommand Dim myReader As SqlDataReader

myCommand.CommandType = CommandType.StoredProcedure myCommand.Connection = myConnection myCommand.CommandText = &quot;MyProc&quot;

Dim RecordCount As Integer myConnection.Open myReader = myCommand.ExecuteReader While myReader.Read 'Logic to process data RecordCount = RecordCount + 1 End While End Sub </li> Modify the Connection string (myConnString) as appropriate for your environment.</li> Save your project. On the Debug menu, click Start to run your project.</li> Click Test.

Notice that an exception is generated when you attempt to run the command.</li></ol>

<div class="references_section">