Microsoft KB Archive/296685

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

Article ID: 296685

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft Host Integration Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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
 * 1) OPENQUERY
 * 2) OPENROWSET
 * 3) OPENDATASOURCE

For more information about these methods, refer to the &quot;Accessing External Data&quot; 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 &quot;sp_addlinkedserver&quot; topic in SQL Server Books Online.

Additional query words: HIS 2000

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB296685

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.