Microsoft KB Archive/198485

= How to use the Visual C++ ATL Consumer templates to perform a distributed query =

Article ID: 198485

Article Last Modified on 1/9/2006

-

APPLIES TO


 * Microsoft Visual C++ 2005 Express Edition
 * Microsoft Visual C++ .NET 2003 Standard Edition
 * Microsoft Visual C++ .NET 2002 Standard Edition
 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Visual C++ 6.0 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q198485



Note Microsoft Visual C++ .NET 2002 and Microsoft Visual C++ .NET 2003 support both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model. The information in this article applies only to unmanaged Visual C++ code. Microsoft Visual C++ 2005 supports both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model.



SUMMARY
The sample below is an OLE DB Consumer Application that demonstrates how to perform a distributed query with Visual C++ 6.0 ATL Consumer templates to retrieve data from SQL Server (using SQLOLEDB Provider) and Microsoft Access Database (using Jet OLEDB Provider) by joining the two NorthWind databases.

The application extracts the PRODUCT ID and PRODUCT NAME from a Products table stored in NorthWind Jet database, joins it with the Products table stored in NorthWind Microsoft SQL Server 7.0 Database, and applies SQL Server ORDER BY to sort the final results.

Briefly, create a .cpp file using the code given below in Visual C++ 6.0 and compile/build from the Build menu (it will prompt for creating a new workspace--this should build and run without adding new code). Change the initialization properties to connect to your SQL 7.0 server. In the OpenRoswset call, change the path to point to NorthWind ("nwind.mdb") sample database on your machine. The sample database is installed by OLE DB SDK, Access, and Visual Basic.



MORE INFORMATION
Microsoft SQL Server 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example,   sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1') The SELECT statement performs the SQL statement against the OLE DB Provider.

The following sample code is an OLE DB Consumer Application that uses OLE DB Consumer Template classes. It uses OpenRowset Transact-SQL function and does not require a linked server definition.

Sample code
#include using namespace std ;

#include   #include  // obsolete in versions later than 6.0

CComModule _Module; #define DBINITCONSTANTS

#include   #include    #include 

//Accessor Class class CProductsAccessor {  public: LONG m_ProductID; TCHAR m_ProductName[41];

//output binding map BEGIN_COLUMN_MAP(CProductsAccessor) COLUMN_ENTRY(1, m_ProductID) COLUMN_ENTRY(2, m_ProductName) END_COLUMN_MAP

};

void main {     HRESULT hr ;

//Initialize COM hr = CoInitialize(NULL);

CDataSource connection ; CSession session ; CCommand > product ;

// Open connection and session // ===================================================     // Setting OLE DB Initialization Properties // ===================================================     CDBPropSet propset(DBPROPSET_DBINIT); propset.AddProperty(DBPROP_INIT_DATASOURCE, L""); propset.AddProperty(DBPROP_INIT_CATALOG, L"NorthWind"); propset.AddProperty(DBPROP_AUTH_USERID, L""); propset.AddProperty(DBPROP_AUTH_PASSWORD, L""); hr = connection.Open("SQLOLEDB", &propset);

// Open a session. hr = session.Open(connection) ;

// Perform a distributed join using OpenRowset T-SQL function. //Note the drive C:\ referenced in the query resides on the server.

hr = product.Open(session, "select P1.ProductID, P1.ProductName     from NorthWind..Products P1,      OpenRowset('Microsoft.Jet.OLEDB.4.0','';      '';'<Password>', 'select ProductID, ProductName from products      where UnitsInStock between 1 and 5')P2      where P1.ProductID = P2.ProductID order by P1.ProductName");

//Get data from the rowset. while(product.MoveNext == S_OK) {        cout << product.m_ProductID << " "; cout << product.m_ProductName << endl ; }  }

<div class="references_section">