FIX: Cannot Execute SELECT COUNT(*) Against DB2 on Mainframe Using Four-Part Name (296685)


The information in this article applies to:

  • Microsoft Host Integration Server 2000
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q296685
BUG #: 352492 (SHILOH_BUGS)

SYMPTOMS

When you execute a SELECT COUNT(*) statement to retrieve the count of all records in a table against a linked DB2 server on a mainframe that is using it's four-part name, the following error message occurs:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'DB2OLEDB'.
[OLE/DB provider returned message: A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42601, SQLCODE: -104]

CAUSE

SQL Server parses a SELECT COUNT(*) statement as SELECT COUNT((0)) for all providers. This can be observed by creating a DB2 internal trace in the Trace utility that is provided with Microsoft SNA Server 4.0 and Host Integration Server 2000.

The OS/390 version of DB2 server that runs on a mainframe cannot work with the format of SELECT COUNT((0)) and causes the error message to occur.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

SQL Server allows you to create linked servers to access distributed, heterogeneous OLE DB data sources. After a linked server is successfully created, you can execute distributed queries against the linked server by using the following methods:
  1. Four-part name in the form of:

    LinkedServerName.Database/Catalog.Owner/Schema.Object
  2. OPENQUERY
  3. OPENROWSET
  4. OPENDATASOURCE
For more information about these methods, refer to the "Accessing External Data" topic in SQL Server Books Online.

Steps to Reproduce Behavior

Execute a SELECT statement in the following form after you successfully create a linked server to the DB2 server on a mainframe: 
SELECT COUNT(*) FROM LinkedDB2ServerName.Catalog.Schema.Table
For more information about creating a linked server to the DB2 server, refer to the "sp_addlinkedserver" topic in SQL Server Books Online.
Modification Type:MajorLast Reviewed:4/9/2004
Keywords:kbBug kbfix kbSQLServ2000sp1fix KB296685
©2004 Microsoft Corporation. All rights reserved.