Knowledge Base

A "COMPUTE BY statements not supported" 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.

MORE INFORMATION

Steps to Reproduce the Problem

Create a stored procedure "myProc" 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 "btnTest" and the Text property to "Test."
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
                    
```

Paste the following code in the code window after the region "Windows Form Designer generated code":

Note You must replace User ID <user name> 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 = _
                "User ID=<username>;password=<strong password>;Initial Catalog=pubs;Data Source=myServer"
    Dim myConnection As New SqlConnection(myConnString)
    Dim myCommand As New SqlCommand()
    Dim myReader As SqlDataReader

    myCommand.CommandType = CommandType.StoredProcedure
    myCommand.Connection = myConnection
    myCommand.CommandText = "MyProc"

    Dim RecordCount As Integer
    myConnection.Open()
    myReader = myCommand.ExecuteReader
    While myReader.Read()
        'Logic to process data
        RecordCount = RecordCount + 1
    End While
End Sub

Modify the Connection string (myConnString) as appropriate for your environment.
Save your project. On the Debug menu, click Start to run your project.
Click Test.

Notice that an exception is generated when you attempt to run the command.

REFERENCES

For more information about ADO.NET objects and syntax, see the Microsoft .NET Framework SDK Documentation, or browse to the following MSDN Web site:

Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx

For more details about the .NET Framework and the COM Interop layer, browse to the following MSDN Web site:

Exposing COM Components to the .NET Framework
http://msdn2.microsoft.com/en-us/library/z6tx9dw3(vs.71).aspx

Additional query words: COMPUTE BY

Keywords: kbtshoot kberrmsg kbnofix kbprb kbsqlclient kbstoredproc kbsystemdata KB311540

Microsoft KB Archive/311540

Contents