You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q296685
BUG #: 352492 (SHILOH_BUGS)
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]
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.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
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:

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.
HIS 2000

Article ID: 296685 - Last Review: 02/22/2007 22:19:46 - Revision: 4.3

  • Microsoft Host Integration Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • kbbug kbfix kbsqlserv2000sp1fix KB296685