Exposing API Server Cursors

OPENQUERY

 SQL Server has a built-in function which is used to ensure that the processing of the distributed query is performed against the remote server.  Please refer to the OPENQUERY topic in SQL Server Books Online for additional information relating to this function. 

The use of the API Server Cursor was eliminated by simply revising the query to utilize the OPENQUERY function.

use LocalDatabase;
select  *
from    OPENQUERY(
          RemoteServer
          , 'select *
                   from RemoteDatabase.dbo.RemoteTable0 as t0
                   inner join       RemoteDatabase.dbo.RemoteTable1 as t1
                             on      t0.Account = t1.Account
                   where  t0.Account = ''XYZ'''
); 

The results of another SQL Server Profiler trace denote that the SELECT statement identified in the OPENQUERY function is the only statement that is executed on the remote server.  This significantly reduces the amount of resources that are utilized to support the execution of this query.

Conclusion

In this article, I’ve tried to provide a brief overview of what an API Server Cursor is, how to identify when a distributed query is utilizing an API Server Cursor, and provide an example of a simple distributed query that eliminates the usage of the API Server Cursor.  Thankfully, SQL Server provides all of the tools that you need to eliminate the use of an API Server Cursor from your distributed queries.  Good luck!

Reference

API Server Cursors
OPENQUERY
SQL Server 2000 Operations Guide
System Stored Procedures (Transact-SQL)