Merisel Technical Deployment

White Paper

By Mark Kapczynski, Brian Wren, Joseph Ruedlinger, and Rick Shahid (Microsoft Consulting Services, Southern California District)

Other contributions from: Michael Ohata, Lisa Wollin, Robert Haynie, Nathan Niederhausern, and Mukesh Agarwal

Special Thanks to:  Chris Cale, Beverly Jones

Release Version:  2.0

August 1999

Microsoft Corporation

Introduction

Merisel, one of the leading distributors of computer products in North America, moved more than $4 billion worth of computer hardware and software through a channel of 25,000 resellers to millions of customers in 1997. They were doing all of their business through phones, faxes, and electronic data interchange (EDI), with a proprietary Web-based solution. The company contracted their corporate Web site (www.merisel.com) with a third party and, aside from a file transfer protocol (FTP) server, had no in-house Internet applications.

They soon realized that contracting all electronic commerce applications and services put the company at a considerable competitive disadvantage in electronic commerce—enhancements and maintenance were costly, performance was slow, and Web usage reporting was non-existent. To regain a competitive advantage, Merisel decided to develop its own customized electronic commerce application and to purchase and configure the necessary host hardware.

Merisel worked with Microsoft Consulting Services to build a state-of-the-art electronic commerce application. This application is referred to as “Mer-EC-Soln” in this paper. The Mer-EC-Soln application was designed to improve and extend the existing system and develop a strong Internet connection with Merisel's customers. Their initial goals were to enable customers to perform quick searches of their online product catalog and encourage them to place orders over the Web.

Note   The names of servers, applications, functions, Web pages, COM objects,  referred to in this paper are fictitious in order to maintain the integrity of this solution.

Return on Investment

Four weeks after implementing the new application, Merisel averaged twice the number of orders received over the Web as the original system. Currently, the Web is still the smallest percentage of their business (as compared to phones, faxes, and EDI), but as they continue to develop the system, they anticipate it will become a larger portion of their business. As a result, customer service employees can focus on their intended purpose—selling.

Project Overview

Merisel’s business requirements presented some challenges to a Web application. Merisel has two large redistribution operations: one for the United States and the other for Canada. Each operation maintains a separate order processing system—an IBM mainframe for the United States and SAP on HP UNIX for Canada. However, since the company plans to move the U.S. system to SAP by the end of 1998, long-term plans focused on this unification.

The two operations handle business processes and data management differently, but they each have 70,000 products, over 15,000 customers, and special customer pricing schemas based on customer classification and purchasing volume. While the obvious choice for an electronic commerce application was to leverage Web connectivity, the systems which contain the product and pricing data were never designed to handle the amount of traffic and processor demand that can occur when a system is accessible through the Web. The development of Mer-EC-Soln took into consideration the logic of the existing system but used none of its parts.

Timeline

There were two phases to this project, which are outlined below. The first phase took approximately 600 hours to complete. Partial completion of the second phase is expected in early December 1998.

Phase I

The first phase of development involved creating a base system that would provide customers with the functionality of Merisel’s current system, but in a new environment. The following table describes the different projects involved in completing the various elements of Phase I, the estimated duration of each project, and the number of persons required to develop and maintain the new application.

Web Site Development

Three persons worked on Web site development, including the following tasks:

Two persons are involved in maintenance.

Electronic Commerce

Two and a half persons were involved, including the following tasks.

One half person is responsible for maintenance.

Order Entry System

Two persons worked on developing the order entry system, including the following tasks:

Two persons are involved in maintenance.

Phase II

The second phase involved development of the Commerce Interchange Pipeline (CIP) and personalization, promoting functionality to their resellers in terms of the CIP, increasing real-time connectivity to the host, and additional personalization features. The elements regarding CIP are discussed in the section on the "Commerce Interchange Pipeline" later in this document.

Mer-EC-Soln allows customers to log onto a Web site, check product availability and pricing, place an order, and check the status of an order, all in real time. This functionality is currently being prototyped for Phase II.

System Requirements

One requirement of Mer-EC-Soln was to use all product, pricing, and customer information from the existing systems without altering or interrupting it to accommodate the new application. Further, orders taken by Mer-EC-Soln needed to be uploaded into the existing systems and filled as any other order.

Performance/Usage Requirements

Order Placement Requirements

Additional Requirements

System Architecture

Merisel chose a Windows-based Distributed Network Architecture (DNA) solution using Internet Information Server (IIS) and Active Server Pages (ASP) to provide dynamic content and user interface. Custom-built component object model (COM) objects were used to process and encapsulate Merisel business logic. Commerce Server objects were used to create and build customer order forms, add items to an order, and process orders. SQL Server was used to store product and customer information and process advanced queries. Distributed COM (DCOM) and Microsoft SNA Server were used to perform real-time requests against foreign systems.

Physical Architecture

The Merisel physical Web architecture consists of six main servers (two Web servers, two SQL servers, one membership server, and one host integration server). The hardware requirements and services used for these servers are explained in the following table.

Table 1 Physical Architecture

Server Qty Services Explanation
Web Servers 2 IIS 4.0

Microsoft Transaction Server (MTS) 2.0

Commerce Pipeline Objects

Site Server

Authentication Service

Commerce Server

Contain the site's ASP files. Each server has an identical configuration with a single site for Mer-EC-Soln and separate virtual directories for the U.S. and Canadian sites.
SQL Servers 2 SQL Server Provides all database services for the site including customer, product, and pricing information as well as the Commerce tables for shopping baskets and receipts. One server is dedicated to the U.S. site, the other to the Canadian site.
Membership Server 1 SQL Server

Site Server

Membership Directory

Authentication Service

LDAP Service

MTS 2.0

Membership Objects

Provides all membership services including the Membership Directory and Membership COM objects.
Host Integration Server 1 SQL Server

MTS 2.0

SAP DCOM objects

SNA COMTI objects

Site Server

Usage Analyst

SNA Server 4.0

Provides connectivity to mainframe and SAP systems by housing the appropriate objects, provides all batch processing for exchanging data between the production SQL Servers and host systems, and contains the SQL database used to import IIS logs and run Usage Analysis reports.
Staging Server 1 IIS 4.0

Site Server

Publishing

Provides a staging location for testing programming changes, after which they can be moved to the production Web servers.

The only servers accessible to users are the Web servers. In order to have these servers accessible through the DNS name www.selline2.merisel.com, IP round-robining is used. With this technique, the DNS servers with the appropriate domain name rotate the returned IP address at regular intervals. When a user resolves the name to an address, they have an equal chance of receiving the address for either server. This effectively distributes the load between the two servers. Merisel’s ISP, IKon, provides the DNS servers with the appropriate round-robin configuration.

Merisel is planning on purchasing a Cisco LocalDirector, which will remove both the need for DNS round-robining and the process for manually configuring the site for a failed server. This router will distribute all requests among available Web servers. If a server is not available, the router detects this and does not direct any traffic to it.

Logical Web Architecture

Merisel adopted a 3-tier approach to developing their business application. This is a logical division of the application and not a physical one. The following diagram shows the flow of information through these three tiers.

Figure 1: Logical Web Architecture

Benefits of 3-tier Architecture

The logical architecture can map to multiple physical architectures by keeping objects stateless (or state-full only at the ASP file level). It can, therefore, easily be scaled up or down, from one machine for low load to as many machines as are required to balance the load.

Production Environment

An initial load study of 1000 concurrent users with typical usage led to this production implementation

Figure 2: Production Implementation

Load Simulation

To validate the server capacity and configuration, the development team performed load simulation using InetMonitor 3.0, developing a script (SHOP.TXT) to simulate a user performing standard operations. In order to simplify the process, not all operations on the site were included in the simulation. For example, it was assumed that a quick product query would produce approximately the same server load as a detailed product query (based on development phase experience). In addition, several Membership operations were not included for general complexity in the script and because most internal operations are performed through a custom control. The goal of the simulation is not to test every aspect of the site, but to load the servers with sufficient traffic to simulate standard user traffic.

As the primary script, SHOP.TXT drives the simulation. The development team created it prior to significant actual user traffic and, upon further analysis of user tendencies, will modify it to more accurately reflect expected traffic and usage patterns. It performs the following core operations:

The team created the following text files in order to randomize the data for user logons, queries, and products ordered:

Although InetMonitor includes several counters for monitoring the Web servers, Performance Monitor was used to monitor key counters on both the Web and SQL Server-based servers and for creating logs and measuring performance during the load simulation. This was due to technical personnel’s familiarity with the tool and its ability to measure performance on the SQL Server-based servers.

Security

Implementation of Site Server Membership for Mer-EC-Soln is based on the following application security requirements:

Certain user rights on each server have been restricted in order to provide extra security. The following user rights have been modified from the default setting.

Table 1

Right Grant To
Access this computer from network Administrators

Authenticated Users

Web users

Force shutdown from a remote system <Empty>
Log on locally Administrators

Authenticated Users

Backup Operators

Web users


To minimize the potential for hacking, the following services have been disabled:

Encrypting Data

Secure Sockets Layer (SSL) provides secure connections on the purchase authorizations and logon pages using a Verisign certificate loaded on each of the Web servers. Several pages on Mer-EC-Soln use SSL in order to encrypt sensitive information, such as user passwords. The following tasks were completed in order to use SSL:

The certificate may be loaded on any Web server but will only work properly if that server is accessed using the certificate’s common name (www.selline2.merisel.com). If a server is to be accessed using another name, another certificate should be obtained.

With a certificate loaded on a Web server and the appropriate configuration in IIS, any page on the site may be accessed securely by simply specifying https:// instead of http:// in the browser address. In the case of connecting to a page from a link on the site, the link usually contains the specification to access the page securely.

Using the IIS Administration MMC, any page on a site may be specified to only allow secure access. In this case, if the page is not accessed via SSL, it will not be returned to the user. For Mer-EC-Soln, appropriate pages are specified as only allowing secure access. The user accesses these pages through links on the site that specify the appropriate security.

Database Access

The product table databases on both the U.S. and Canadian sites were designed to match (as closely as possible) the structure of the corresponding host data. This simplifies data transfer between platforms and makes it easier to replicate business logic from the host applications. The Commerce tables are independent of the host structures and are identical for both systems.

Since the design had to match as closely as possible the code at the U.S. and Canadian sites, the database structures had to be abstracted from the application so that the databases look identical to the application regardless of their very different structures. This was accomplished by accessing all product tables using stored procedures.

Equivalent stored procedures were created on each database with the same name, parameters, and return values. The details of equivalent procedures on each site are different due to the differences in database structure and, in some cases, business logic, but the Web application only needs access to the stored procedures—not information about the database structure.

This strategy provides an added benefit by separating programming tasks.

Connection pooling within MTS allows the application developers to generalize and centralize database access. All selections and updates are encapsulated within one Visual Basic® module, and when an object needs to perform database access, it simply sets up the string for calling the stored procedure and passes it to either DoSearch() to get a disconnected recordset, or to DoAppend to do an update.

An object running within MTS can easily set up a string representing a stored procedure call and invoke DoSearch(). An example call to Dosearch is:

vErrorCode = DoSearch(CStr(vDB), True, "EC-sp_ByMerPart" & vCustNo & ", " & vMerPartNo & "," & vMaxRows, vResultSet, vbErr, vbErrDescription)

In this case, the stored procedure EC-sp_ByMerPart is called with the parameters: Customer Number, Merisel Part Number, and the maximum number of rows to return. DoSearch returns a disconnected recordset, which allows objects running within MTS to remain stateless since a disconnected recordset copies its data to the caller. If the caller is a client in a separate process or another computer, the recordset marshals its data to the client’s process. When going across a network, it compresses the data to use less bandwidth.

Performing Batch Transfers

Data services in Mer-EC-Soln (aside from the real-time connections to host systems) are provided by SQL Server. Product, pricing, and customer information from Merisel’s hosts are moved at periodic intervals into SQL databases, while orders are uploaded back to the hosts. The following tables list the different types of data required by the system and their batch transfer requirements.

Table 2: Data Required by the System

Data Description
Product Product information including vendor information and product descriptions.
Pricing All tables required for calculation of prices.
Customer Merisel reseller information.
Inventory Bulk Inventory by warehouse for each product.
Inventory Delta Inventory by warehouse for subsets of products. This information is updated throughout the day with only information for products whose inventory is modified.

Table 3: Batch Transfer Requirements

Data Direction Frequency
Product

Pricing

Customer

Inventory Bulk*

Host to SQL Server Nightly
Inventory Delta* Host to SQL Server Hourly
Order Detail SQL Server to Host Every 15 minutes

A single database stores the tables specific to the Commerce Server (including the basket and receipts tables). Two identical databases, which are transferred regularly from the hosts, were created for the product, pricing, and customer tables. The data in these databases is read-only to the application. Each Web server has a separate DSN referring to each of the product databases.

A separate server is used to drive this process rather than incur processing on the Production Servers. In addition to processing advantages, this method allows Merisel to provide access between the DMZ (de-militarized zone) and corporate network only to the single Host Integration Server rather than to the Production Servers.

An application written with the Visual Basic 6.0 development system and that is scheduled to run through the SQL Server Executive on the Host Integration Server drives the data transfer process. This application:

The data transfer application provides considerable error checking and data validation to ensure data integrity. FTP transfer functionality is provided by the Internet Transfer Control included in Visual Basic 5.0. DB-Library for Visual Basic was used to access BCP and SQL functions.

Accessing the Host in Real Time

Merisel required two pieces of information to be retrieved real-time from the host systems: product inventory and order status. During product queries, the system reports on inventory that has been in the SQL tables for up to an hour and determines whether an item is in stock. The user can request a detailed listing of how many items are in stock at each warehouse. This feature requires real-time information due to the volatile nature of product inventory. As for order status, users can track orders to see their status in real time.

Accessing the Mainframe

COM Transaction Integrator (COMTI) is shipped with SNA Server and allows a COM component to access an existing host transaction. An object written with COMTI for CICS (or IMS) allows real-time access to the Merisel IBM mainframe. The required transactions for order status and single product inventory already existed in the original system. The COM component accepts arguments from the Web application, passes them to the host transaction, retrieves the results, and passes them back to the application. The COM component resides on the Host Integration Server with SNA Server.

Accessing SAP

The SAP DCOM Component Connector, which may be downloaded from SAP’s Web site (http://www.sap.com/bfw/interf/bapis/com/com.htm) allows writing a COM component to access existing Requests for Comments (RFC) in SAP R/3 on any platform. Using SAP DCOM Component Connector, the developers wrote a COM component that allows real-time access to SAP on HP UNIX. The original system already had the required RFCs for order status and inventory of a single product. The COM component accessing SAP resides on the Host Integration Server.

Implementation

To provide functionality via the Web without burdening the current systems, the designers built a loader that mirrors product and pricing information from the daily business systems over to high capacity Microsoft SQL Server-based computers. The design also includesseparate Web servers to respond to HTTP requests and serve up Web pages. The loader runs nightly during off-peak hours to keep product offerings and pricing information accurate. This basic strategy makes it possible to build a functionally rich application that adds neither risk nor burden to the current systems.

Tools Used

Technologies Used

Reporting

Reporting on Merisel’s two sites (Mer-EC-Soln for the U.S., Mer-Can-EC-Soln for Canada) is accomplished with the analysis tools in Site Server. The system uses Host Integration Server both to import and store log information and to process reports. One complexity of reporting Web usage is the inability to distinguish user sessions from logs. Site Server Analysis performs analysis logic against log data to estimate usage, including separate user visits and page requests (ignoring all elements on the page) rather than the classic measure of raw hits.

Usage Import brings in log files from both Web servers on a weekly basis. A significant portion of analysis logic is performed at this level: Server Manager (in Usage Import) analyzes traffic between the two sites upon log import. Usage Import also imports batches of files and can backout entire batches as opposed to individual files. Merisel imports all files from a single week as a complete batch, archiving this data in one-week increments.

Creating Log Files

Log files are stored on the Web servers in the d:\weblogs directory. This directory is shared with the share name weblogs so that it may be accessed with the UNC \\servername\weblogs. The log files are imported into the Analysis database so that reports may be run against them. Once they are imported, they may be removed from the local file system. To make sure that data is not lost, log files should be removed from the Web servers approximately once a month to ensure that they have been properly imported into the database.

Importing Web Logs

Site Server Usage Analysis is an application that manages the process of importing Web log files into SQL Server for later access by Report Writer.  It includes a process for creating a database, but this has already been done for Mer-EC-Soln.

Usage Analysis uses the following information on the SQL Server database. The database resides on the same server currently performing the bulk of import logs and reports. However, this does not have to be the case. Import and reporting processes simply need a valid ODBC connection to the server containing the database. The server simply needs to have SQL Server installed.

Table 4: Database Usage Analysis

Server DB02
Database Name Mer-EC-SolnAnalysis
User Name Sellanlaysis

A job scheduled on DB02 imports the log files each night. The scheduled job is configured using the Site Server Scheduler accessed from the Site Server Analysis Usage Import. This tool creates a batch file and schedules the job through the Scheduled Tasks in Windows NT.

Table 5: Sample Import Configuration

Server DB02
Scheduled Task Daily Log Import
Batch File D:\Microsoft Site Server\Data\Analysis\Schedule\Daily Log Import.bat
Log File D:\Microsoft Site Server\Data\Analysis\Message\ReportUI.log
Schedule Time Every day at 1:00 AM

Under normal operation, this imports the previous day’s log files from both Web servers. The imported log may be verified either by checking the log file or by viewing the Import History in Usage Import. Should the automated process fail, the log files can be manually imported by starting Usage Analyst and selecting the appropriate log files to import. Should the automated process fail for several days, multiple days worth of files can be imported at one time.

The log files from both Web servers should be imported at the same time for a particular day (or set of days). However, a single user session may occur across both servers. In this case, the importing process analyzes all files from a particular day as one set of data. Should files from multiple servers be imported separately, the results may display an incorrect increase in the number of visits.

Generating Query Strings

When a user fills out a form, a query string is generated that passes the user’s information to the appropriate Web page. This information, stored in the log files, is valuable for reporting. For example, a sample query string generated from a quick search is:

/Mer-EC-Soln/QSResults.asp QT=5&BPN=0&Crit1=microsoft+press&Crit2=&MR=25
QT Quick Search Type
BPN Beginning Part Number—Used
Crit1 First criteria specified by the user—as defined by the search type
Crit2 Second criteria specified by the user—may be empty depending on the search type
MR Maximum Results—number of matching records to display

In order to use query string information in reports, Usage Import must be configured in the following order:

Table 6: Import Configuration

File system paths (URIs) containing query strings to import: /Mer-EC-Soln/QSResults.asp

/Mer-EC-Soln/DSResults.asp

/Mer-EC-Soln/confirmed.asp

/Mer-EC-Soln/descinquire.asp

Names of single-value query parameters to parse: CL CRIT1 CRIT2 ORDER_ID PARTNO QT

Deleting Data

The initial two months of Mer-EC-Soln usage have not produced an unreasonable amount of data in the usage database (less than 100 MB per month). However, the database size should be monitored and data deleted at regular intervals to ensure that space does not run out. The process to remove data from the database can take significantly longer than adding data. See Site Server’s online documentation for a description of this process.

Generating Reports

Report Writer in Site Server produces reports based on the Usage Import data. To make this data available to Merisel executives and other staff, the reports are configured to generate HTML files posted on an Intranet server, providing up-to-date statistics accessible from any corporate network client. The items in the weekly executive summary reports include:

Currently, commerce-specific data, including most popular products and vendors, is not included in the usage reports. Number of orders is calculated by counting the occurrences of the order confirmation page. The current method under consideration for capturing detailed information on product queries and purchases is to log this information.

Filters may be placed on a report or a section of a report using a relative date to allow running a report for a specified period without modification. For example, a report titled Weekly Mer-EC-Soln Usage uses a filter called LastWeek to report all usage from the previous week. This report is run every week and reports usage from the week prior to the date it is run.

Reports may be scheduled to run automatically using the Scheduler in Report Writer (this is the same scheduler used for import logs—it may be accessed either from Report Writer or Usage Import). For example, the Weekly Mer-EC-Soln Usage report has been scheduled to run every Sunday evening. This reports usage from the previous week and stores its results in an HTML file on the Intranet server. This allows any corporate network client to connect to the Intranet server and view usage statistics.

Publishing

Publishing refers to the act of moving content and applications from development into production. The content is first moved to a staging server where it is tested in a near production environment. Once it has been approved, it is moved into production.

The following table shows the servers involved in the Mer-EC-Soln publication process.

Server Role Directory
DEV1 Development e:\inetpub\wwwroot\Mer-EC-Soln2
STAGE1 Staging d:\inetpub\Mer-EC-Soln2
PROD02

PROD03

Production e:\wwwroot\Mer-EC-Soln2

Content starts on the development server, DEV1, as shown, and is published out to the staging server STAGE1. STAGE1 is configured with the entire site and is accessible to the Merisel corporate network. Users connect to this server to test the new content.

When finished with a series of changes, a developer checks them into the development server for testing by another developer. This allows the developer freedom to make changes without destabilizing the system.

After the changes are passed, the application (ASP, HTML, COM objects, and stored procedures) is transferred to the staging server using a combination of CRS (Site Server Content Replication Services) and manually copying the DLLs and creating the MTS packages.

The QA team performs further testing and stress analysis against the staging server. When the new content is approved, it is moved to the production servers. These servers are not accessible from the corporate network.

This process is diagrammed below:

Understanding the Different Types of Accounts

In order for the publication process to work appropriately, a common account must be used throughout the process with sufficient rights to perform the necessary activities. The following three accounts have been set up at Merisel.

The Site Server Content Deployment service runs under the Local System account on all servers including development, staging, and destination. An account called Mer-EC-Soln_Publish has been created on each server. This account has been specified as the Default Authentication Account. A Project Level Authentication Account was not used for Mer-EC-Soln.

Managing the Publishing Process

You can administer the publishing service from any server with a connection to the server that you want to administer. Management of the Mer-EC-Soln publishing process is most easily done by adding the four servers in the process (development, staging, two production) to a single MMC.

To connect to a server in MMC

  1. In the MMC, right-click Publishing, and then click Add Host.

  2. Type in the name or IP address of the server.

  3. Click Connect Using Authentication Account and type in the name and password of an account with Site Server Administrator privileges on the remote server.

Initiating the Publishing Process

The Mer-EC-Soln publishing projects have been configured to operate manually, so an operator must initiate the process. Projects can also be configured to occur automatically according to a schedule, but no Mer-EC-Soln project was configured in this fashion.

The projects created to publish Mer-EC-Soln information are as follows.

Name Origin Destination
Mer-EC-Soln Content Staging DEV1 STAGE1
Mer-EC-Soln Content Production STAGE1 PROD02

PROD03


The project must be created with the same name on each server participating in the project. It may only be started from the origin server. Since you can connect to a server from another computer using the MMC, you can start a project while physically working at another computer.

To start a project

  1. Select the project in the MMC (again on the origin server).

  2. Click  Action, and then Start.

    The job shows a status of Started and then Running. If it completes successfully, it will show a status of Complete. If not, it will show Error. If you do receive an error, check the Application Event Log using the Windows NT Event Viewer for more information.

Code Components

Application functions involve all operations from initialization to order processing and real-time order status checks.

Initializing Mer-EC-Soln

Mer-EC-Soln initialization follows the basic rules of ASP. All application processing begins in the GLOBAL.ASA the first time a user visits the site. Application initialization includes:

Upon completion of processing the GLOBAL.ASA, the ASP files have access to the Application object and the associated Mer-EC-Soln and Commerce objects.

Registering and Authenticating Users

The Merisel Mer-EC-Soln system provides an online registration process for new members. The process begins by explicitly requesting membership profile information from the current user. In addition, a user must provide the account number and access credentials to be enrolled as a member of that account.

The core Mer-EC-Soln Membership features include:

Figure 3: New Member Registration Page

Once a user supplies all the requested information and submits it to the system, the following process flow takes place. This customized process flow represents the Mer-EC-Soln online registration process.

Figure 4: Registration and Authentication Process

The application uses HTML Forms Authentication to authenticate a user’s access to the site. Security is further enhanced within the .ASP pages where the .ASP scripts instantiate an Active User Object (AUO) to authenticate the user prior to instantiating objects or dynamically writing out privileged HTML options.

For example, during the order purchase process, the OrderFormCust.asp, which is used to dynamically write out the ordering terms, first instantiates the AU object, checks to see if this customer has drop-ship privileges and then optionally writes the HTML to allow the order to be drop-shipped. It is also important to note that the .ASP that checks the fields on the submission of this form also verifies the AU object for drop-ship since the sending HTML form could have been manually saved, altered, and then resubmitted.

Users authenticated through HTML Forms Authentication come into the ASP pages as the anonymous user IUSR_MACHINENAME. Since all privileged functionality is restricted within the .ASP pages, the objects within MTS run without any defined security or roles. Data access from the objects to SQL Server is done using the standard security model and ADO. A small set of restricted and reusable IDs were created in SQL Server. When an object needs to access the data server, it goes through the following steps: 

  1. Gets an ADO Connection using the user ID ‘webaccount’

  2. Performs the select, insert, or update (via a stored procedure)

  3. Releases the connection

Objects running within MTS and using ADO (ODBC 3.0 or higher) to SQL Server get connection pooling for free. That is, MTS pools the connections, automatically freeing the objects from having to implement a connection strategy.

Whenever unauthenticated users request a secure page, Site Server automatically redirects them to the following customized login page:

Figure 5: Membership Login Page

Redirecting Unauthenticated Users

Site Server automatically redirects an unauthenticated user to ../_mem_bin/formslogin.asp once the Web site is secured with HTML Forms Authentication. The Mer-EC-Soln system adds a second level of redirection from FORMSLOGIN.ASP to FORMSLOGIN_SSL.ASP for additional security. Site Server automatically sends the originally requested URL as the login query string.

  1. Whenever an unauthenticated user requests a page from the Mer-EC-Soln Membership-enabled Web site, a series of validations and process redirections take place before the requested page is delivered to the end user. The steps below represent the Mer-EC-Soln Membership authentication process, which is a customized version of the HTML Forms Authentication process based on the custom AUTHENTICATE.ASP file. The diagram shows the flow of these steps.An unauthenticated user requests the http://www.selline2.merisel.com/Selline/default.asp page, which is part of a Membership-enabled virtual directory called Selline.

  2. Site Server recognizes that an unauthenticated user has requested a secure page and redirects to http://www.selline2.merisel.com/_mem_bin/formslogin.asp?/Selline.default.asp. Notice that the page originally requested is passed into the FORMSLOGIN.ASP page as a query string. The page originally requested is referred to as the URLRequested.

  3. The Mer-EC-Soln implementation of FORMSLOGIN.ASP simply provides a Secure Sockets Layer (SSL) redirection to FORMSLOGIN_SSL.ASP. The URLRequested passed into FORMSLOGIN.ASP is also passed into the FORMSLOGIN_SSL.ASP page as a query string.

  4. The FORMSLOGIN_SSL.ASP page presents the user with a secure, encrypted channel to enter their personal Member ID and Password for authentication into Mer-EC-Soln.

  5. The entered Member ID and Password along with the passed-in URLRequested are then posted to the AUTHENTICATE.ASP page for online validation.

  6. The AUTHENTICATE.ASP page performs this sequence:

    If the end user passes the validations above, then AUTHENTICATE.ASP generates the FormsAuth cookie and redirects the user to the page that was originally requested (URLRequested), which completes the Membership authentication process.

Figure 6

Site Server Membership uses the FormsAuth cookie to determine the scope and lifetime of an authenticated user’s login session. Since the Mer-EC-Soln membership-enabled Web site consists of two independent IIS virtual directories (Mer-EC-Soln and Mer-Can-EC-Soln), the FormsAuth cookie has been customized to secure access to Mer-EC-Soln at the virtual directory level. Basically, the scope of the FormsAuth cookie has been limited to the virtual directory containing the AUTHENTICATE.ASP file in which the FormsAuth cookie is generated. If the FormsAuth cookie expires due to inactivity, the user is once again redirected to the FORMSLOGIN.ASP page and the authentication process is repeated.

Application Authentication and Database Access

The Member schema class is the fundamental structure that supports the Active User Object (AUO)— the object that contains information about the currently authenticated user. The Mer-EC-Soln implementation of the Member schema class consists of the following standard and custom member attributes.

Table 7: Member Schema Customizations

Member Attribute AUO Property Custom Data Type Required
GUID Guid N String Y
Member ID Cn N String Y
Member Password UserPassword N String Y
Account ID AccountID Y String Y
Account Telephone AccountTelephone Y String Y
First Name FirstName N String Y
Last Name SurName N String Y
Company Name CompanyName N String Y
Job Title JobTitle N String N
Office Address officePostalAddress N String Y
City l N String Y
State / Province st N String Y
Postal Code postalCode N String Y
Country c N String Y
Office Phone officePhone N String Y
Office Extension officeExtension Y String N
Office Fax officeFax N String N
Additional Contact additionalContact Y String N
Email Address mail N String Y
Mailing List mailerNoEmail N Integer (0-1) Y
Place Orders placeOrders Y Integer (0-1) N
Drop Shipments dropShipments Y Integer (0-1) N
Account Admin accountAdmin Y Integer (0-1) N
Merisel User meriselUser Y Integer (0-1) N
Merisel Admin meriselAdmin Y Integer (0-1) N
Organization Name ou N String N
Accounts groups N Distinguished Name (DN) Y
Date Last Visited LastVisit N Generalized Time N
Member Status accountStatus N Integer (1-4) Y
DS Privileges privileges N String N

The customizations for the Account (mGroup) and the Organization (organizationalUnit) schema classes are noted in the following tables.

Table 8: Account Schema Customizations

Account Attribute AUO Property Custom Data Type Required
Account ID AccountID Y String Y
Account Name DisplayName N String N
Description Description N String N

Table 9: Organization Schema Customizations

Organization Attribute AUO Property Custom Data Type Required
Organization Name ou N String Y
Description description N String N

Implementing Functionality

The set of files listed below are used to implement the core Mer-EC-Soln Membership functions. The table that follows identifies the function of each of the different Membership files. Some of fundamental relationships associated with the Membership files include:

Table 10

File Function
FORMSLOGIN.ASP Redirects an unauthenticated user to the secure login page
FORMSLOGIN_SSL.ASP Provides a secure login page against the Membership Directory
AGREEMENT.ASP Standard membership agreement for online registration
REGISTRATION.ASP New member online data entry, validation and registration
WELCOME.ASP Successful online registration and welcome page
AUTHENTICATE.ASP Customized member login/authentication process
ACCTMAINT.ASP Online membership account access and maintenance
PROFILE.ASP Online modification of a member’s personal profile
SWITCHACCTS.ASP Custom account switching process for privileged members
ACCTADMIN.ASP Online account administration for privileged members
PRIVILEGES.ASP Updates member privileges within the Membership Directory
MEMACCTS.ASP Add/remove member from multiple membership accounts
MDADMIN.ASP Membership Directory administration for privileged members
FORMembership.DLL Custom Membership Directory API based on FastADS.dll
ForMEMBERSHIP.OCX Custom Membership Directory administration control
ACTIVEUSER.INC Standard interface for accessing the Active User Object (AUO)
DTCLIB.INC Customized design-time control library for online registration
MEMBERSHIP.INC Standard constants for accessing Membership Directory attributes

Navigating the Mer-EC-Soln Site

DEFAULT.ASP, the main navigation page, includes command buttons and hyperlinks that navigate to the page hosting the desired function: QuickSearch, DetailedSearch, View Current Order & Purchase, Check Order Status, Account Maintenance, and Help.

Figure 7: Mer-EC-Soln’s Main Navigation Page

Each functional page also includes two files, TOPNAV.ASP (at the top) and TEXTNAV.ASP (at the bottom) and HTML to provide hyperlinks to the other functional areas on the site.

Figure 8: TOPNAV.ASP and TEXTNAV.ASP

Performing Product Queries

Users can perform quick product queries by navigating to the above page.  The above form’s Action attribute is set to QSRESULTS.ASP. Mer-EC-Soln processes the query submitted by the above form as follows:

  1. QSRESULTS.ASP parses the form and instantiates a quicksearch object.

  2. Based on search criteria, it calls one of these QuickSearch methods:
  3. The QuickSearch method opens an ActiveX Data Object (ADO) connection to the products database and invokes a stored procedure that queries the product table.

  4. The stored procedure passes a recordset back to the method.

  5. The method disconnects the resulting recordset and returns it to QSRESULTS.ASP.

    QSRESULTS.ASP dynamically displays an HTML table to the user showing matching part numbers, descriptions, and a hyperlink for adding it to the shopping basket.

The following diagram illustrates this querying process:

Figure 9: Process for Performing Quick Product Queries

Detailed product queries are similar in processing to the quick search queries, with the following exceptions:

The page then writes the resultset dynamically into an HTML table and sends it back to the user.

Creating Orders and Checking Inventory and Order Status

Real-time inventory and order status checks are handled differently between the U.S. and Canada. The differences between these processes are described below and illustrated in the accompanying diagrams.

For Canadian customers:

Figure 10: Canadian Inventory and Order Status Checking Process

For U.S. customers:

Figure 11: U.S. Inventory and Order Status Checking Process

In either case, an identical HTML page is returned to the user with the inventory and order status information.

Figure 12: Status Page Returned to the User

The process for creating an order or checking on the status of orders is described below and illustrated in the diagram following:

Figure 13: Site Navigation for Creating an Order and Checking Status

Processing Order Forms

Each product query page that returns a matching set of results includes a hyperlink to a page (ADDITEM_XT.ASP) that adds the product item to the customer’s basket. This is implemented using the Commerce Server OrderForm (Commerce.OrderForm) and DBStorage (Commerce.DBStorage) objects.

When customers request to update their order form:

Figure 14: Order Form Processing

Prior to invoking the Order Processing Pipeline, BASKET.ASP invokes a Mer-EC-Soln object and calls the GetReceiptNum method to generate a Merisel receipt number. It then instantiates a MTS Pipeline object (Commerce.MtsPipeline) to execute each of the stages of order form preprocessing, which include:

Placing an Order to Complete the Purchasing Process

The final stage of the ordering process allows the customer to purchase the items added to the order form. From the basket page (BASKET.ASP), a customer can submit the order and purchase the items selected. When customers select purchase, the BASKET.ASP directs them to ORDERFORMCUST.ASP, which invokes an OrderHelper object and also uses the global application objects to dynamically build an order page customized for each customer. This includes:

In the same fashion as the search objects, the OrderHelper object uses ADO to get a connection to the product database and invokes stored procedures that return recordsets back to the client (in this case ORDERFORMCUST.ASP), which dynamically writes HTML option boxes to the HTTP stream.

The action of ORDERFORMCUST.ASP directs the submission to PREPARE_XT.ASP, which checks each of the fields entered, updates changed fields, and re-saves the order form. Based upon the options specified, ORDERFORMCUST.ASP then either redirects to pages to get credit card and/or drop-ship information and/or it directs to a page (PURCHASE_XT) which invokes the order purchasing pipeline to complete the purchase.

After the customer reviews and accepts (submits) this page, they are directed to a page (PURCHASE_XT.asp) which invokes the order purchasing pipeline to complete the purchase.

The final purchase step invokes the OPP to check the credit card and make sure the information submitted is well formed, assigns credit for the order to the appropriate sales representative based on account identification, and moves the order within the Orders database from the basket table to the receipts table.

The navigation to purchase an order is described by the diagram below: 

Figure 15: Merisel Site Navigation for Completing the Purchase of an Order

Order Processing Pipelines

The application uses two different order processing pipelines: one for order preprocessing and subtotaling (plan.pcf) and the other to complete the purchase (purchase.pcf).

The preprocessing and subtotaling pipeline performs the following steps:

Upon completion of executing the preprocessing and subtotaling pipeline, the user can display the order form for review, continue shopping, or purchase. The purchase pipeline executes the following steps:

Custom-built pipeline components were built in Visual Basic as ActiveX-based DLLs and are hosted inside of MTS. The components are fairly standard and implement the IPipelineComponent interface.

Commerce Interchange Pipeline

In the follow-up release, Mer-EC-Soln will use the Commerce Interchange Pipeline (CIP) for remote connectivity from reseller e-commerce solutions back to Mer-EC-Soln’s site. The system will provide the following advantages:

For an example, a consumer shops on a local reseller’s Web site, such as PCMall.com. As the consumer checks the reseller’s inventory, the reseller’s system first checks their local inventory. If there is no inventory, the reseller’s system connects back to Mer-EC-Soln using the CIP and checks Merisel’s inventory. (The consumer doesn’t see this process.) When the consumer places an order with the reseller site, the reseller commerce site commits the inventory and order with Merisel, again using the CIP. The following diagram shows how this process works.

Figure 16: The CIP Process

Application Data Objects

The Mer-EC-Soln object model is flat, and objects can be allocated, called, and de-allocated without any side effects or the expectation that state needs to be held between calls. To allow high scalability and performance when being run locally or remotely, objects are kept stateless and return either simple standard Visual Basic automation types or ADO disconnected recordsets. For transactional support, process isolation, and remote capabilities, business objects are placed into packages to run under MTS.

The philosophy used in building the application is to:

The Mer-EC-Soln objects fall into five categories: shop, pipeline, formatting, membership, and real-time.

Shop Objects

Each of the shop objects is implemented as COM objects written as a Visual Basic-based ActiveX DLL. The objects are all contained physically in the same DLL (Mer-EC-Soln.dll).

Customer

CustCheck—Checks to see that the passed in AccountID is a valid Merisel account number.

GetCommissionAcc—Based on the passed in AccountID, returns the ID of the Merisel account representative who receives credit for the order.

GetDropAddShip—Based on the passed in AccountID, returns a disconnected recordset of drop-ship addresses previously shipped to for this customer. Order is based on most recently used.

PutDropAddShip—Saves the passed in drop-ship address based on account identification. Does not save duplicates or number of times used.

DetailedSearch

CanSrchExt—Passed in the search Criteria for a detailed Canadian search along with the beginning part number and the maximum matches to return, returns a disconnected recordset of matches. When called with a beginning part number of 0, also returns the maximum number of matches. This function can be called iteratively, bumping up the beginning part number each time to return large sets, one piece at a time.

SrchExt—Passed in the search Criteria for a detailed U.S. search along with the beginning part number and the maximum matches to return, returns a disconnected recordset of matches. When called with a beginning part number of 0, also returns the maximum number of matches. This function can be called iteratively, bumping up the beginning part number each time to return large sets, one piece at a time.

DownloadLib

GetProdImg—Passed in a Merisel PartNumber, returns the location on disk of the corresponding image file.

GetProdDesc—Passed in a Merisel PartNumber, returns a detailed description of the product.

GetMetalLvl—Passed in a customer ID, checks the customer’s retailer class and returns the ftp location of a downloadable price book.

DumpForm

BasketDump—Used for debugging. Passed in an AccountID, ShopperID and a file name, checks the basket table to see if the user has a current order and if so, dumps the contents to the specified file.

ReceiptDump—Used for debugging. Passed in an OrderID and a file name, checks the receipt table to see if the order exists and if so, dumps the contents to the specified file.

Inventory

GetInvLocal—Passed in a part number, checks against the local SQL databases for inventory levels at each of the Merisel warehouses. Inventory information from this call is not real-time and is up to 24-hours old. This call is used if access to the real-time systems is not available.

MerPricing

GetCstmPrice—Passed in a part number, a customer number, and a quantity, calls stored procedures to run through an elaborate pricing algorithm and returns both the list price and the custom price.

GetCstmPrice_Rebate—The same as above, but also returns any rebate amount the customer may receive for the product.

OrderHelper

CheckShipDrop—Passed in a an account identification, verifies that the customer has drop-ship capability.

CheckShipDropCd—Passed in an account identification, and a drop-ship password, authenticates the correctness of the password.

GetBill_To—Passed in an account identification, returns a disconnected recordset of registered bill-to addresses.

GetContact_info—Passed in an account identification, returns a disconnected recordset of registered contacts.

GetCustDiv—Passed in an account identification, returns the division of the customer (Canada only).

GetCustOrdr—Passed in an account identification and a shopper identification, checks the current basket table and returns the unique order identification for that account identification/shopper identification.

GetCustOrdrTrms—Passed in an account identification, returns a disconnected recordset of the registered and approved ordering terms for that customer (e.g. net 15, net 30, credit card, and so on).

GetCustViaShip—Passed in an account identification, returns the preferred shipping vendors used by that customer.

GetCustWrhs—Passed in an account identification, returns the preferred Merisel warehouses which the customer prefers to ship from.

GetBill_To—Passed in an account identification, returns the set of registered bill-to addresses for the customer.

ProductInfo

GetProdDesc—Passed in a Merisel Part Number, returns a detailed product description in a string suitable for display in html.

GetProdPromoDesc—Passed in a Merisel Part Number, returns a detailed promotion description running for that product in a string suitable for display in html.

QuickSearch

SrchMFCExt—Passed in an account identification, manufacturer criteria, along with the beginning part number and the maximum matches to return, returns a disconnected recordset of matches. When called with a beginning part number of 0, also returns the maximum number of matches. This function can be called iteratively, bumping up the beginning part number each time to return large sets, one piece at a time.

SrchMFCPartExt—Same as above, but uses a Manufacturers part number as the search criteria.

SrchPromotionExt—Same as above, but uses promotion criteria as the search criteria.

SrchTitlExt—Same as above, but uses a partial title as the search criteria.

SrchUPCCode—Passed in an account identification, and a UPC code, returns a single row disconnected recordset matching the request.

SrchMerPartNo—Passed in an account identification, and a Merisel part number, returns a single row disconnected recordset matching the request.

ReceiptSearch

GetReceipts_Date—Passed in an account identification, a beginning date, and an end date, returns a set of matching OrderIDs, amount totals, and submitted dates, which were submitted by that customer within the specified period.

GetReceipt_PONum—Passed in an account identification and a customer PO Number, returns a matching OrderID, amount total, and submitted date.

GetReceipts_Amt—Passed in an account identification, a beginning amount, and an end amount, returns a set of OrderIDs, amount totals, and submitted dates which were submitted by that customer within the specified range.

GetReceipt_OID—Passed in an account identification and an order identification, returns a matching OrderID,amount total, and submitted date.

Scalars

GetReceiptNum—Returns a unique ID which can be used for an order identification. Uses transactions to handle concurrency.

Pipeline Objects

Each of the Mer-EC-Soln pipeline objects are implemented as COM objects written as a Visual Basic ActiveX DLL. They implement the interface IpipelineComponent. Within the IpipelineComponent_Execute() method, the objects retrieve data from the passed in OrderForm and PipeContext and call the Mer-EC-Soln shop objects to perform the actual work. The objects are all contained physically in the same DLL as the shop objects (Mer-EC-Soln.dll).

PipelinePricing

IpipelineComponentExecute—Retrieves the customer number from the order form header and then iterates through the item list calling the MerPricing object with each of the items (quantity and part number). Currently this component is not called in any Merisel pipelines.

PipelinePriceAndRebate

IpipelineComponentExecute—Retrieves the customer number from the order form header and then iterates through the item list calling the MerPricing object with each of the items (quantity and part number) to get the custom price and rebate for each item. Called in the plan pipeline.

PipelineCommission

IpipelineComponentExecute—Retrieves the customer number from the order form header and then calls the Customer object to retrieve the customer’s account representative in order to give that representative credit for the order. Places the account representative identification in the order form header.

Formatting Object

Currently there is only one formatting object: SelPerf.Format. This object has a single method, VBFormat(). which takes an input string and a format string and returns an output string. It is simply used as a wrapper to the Format function of Visual Basic. This is done to work-around the painful limitation of formatting functions available to ASP from VBScript.

This object is placed in its own DLL (selperf.dll) and is run within MTS as its own library package. This allows for faster (in-process) calls to the object for formatting.

Configuration

The Mer-EC-Soln application consists of six servers: two Web servers, two SQL Server-based servers, one membership server, and one host integration server. The hardware requirements for these servers are discussed in detail in the section on “Physical Architecture.” The configuration requirements for these servers are listed below.

Web Servers

The two Web servers in the Mer-EC-Soln environment have been configured identically. The primary function of the Web servers is to process ASP pages for the user interface. The developers made every effort to move functionality, aside from IIS, to the other servers.

Table 11: Platform

Component Version Explanation
Windows NT Server 4.0 SP3  
Internet Information Server (Option Pack) 4.0
Transaction Server (Option Pack) 2.0
Site Server Membership 3.0 Required for mapping of IIS to Membership. All Membership components and services are on the Membership server.
Commerce Server 3.0 Commerce provides the necessary components, as well as extensions to the Site Server administration MMC, specific to Commerce Server.
Mer-EC-Soln ASP pages    

Table 12: Internet Information Server Configuration

Location Setting Value
Server Properties Max ASP Files Cached 1,200
Site Properties

Web Site Tab

IP Address Specific IP address for PROD01, PROD02
  Connections Unlimited
Enable Logging Yes
Active log format W3C Extended Log File Format
Site Properties

ISAPI Filters Tab

Name:  Auth Filter

Executable:  Authfltr.dll

Priority:  Low

Site Properties

Home Directory Tab

Local Path  
  Access Permissions Read
Content Control Log Access
Site Properties

Home Directory Tab

Configuration

Application Options Tab

Enable session state No
  Enable buffering Yes
ASP Script timeout 180 seconds
Virtual Directory Properties

Virtual Directory Tab

Access Permissions Read
  Content Control Log Access
Name <blank>

SQL Servers

The servers running SQL Server contain the databases for interaction with Mer-EC-Soln. There are two servers, one each for the U.S. and Canada. Each database is significantly different and is designed to match, as closely as possible, the structure of the corresponding host data.

Table 13: Platform

Component Version Explanation
Windows NT Server 4.0 SP4  
SQL Server 6.5 SP4 HF297 SP4 and hotfix 297 are required for and included with Site Server.
Transaction Server (Option Pack) 2.0 There are no MTS components on the servers running SQL Server, but this provides the updated version of MSDTC.

Table 14: SQL Server Configuration

Location Setting Value
SQL Server Setup

Network Support

Network Protocols Named Pipes

TCP/IP Sockets

SQL Enterprise Manager

Configuration

Locks 15000
  Memory  
User Connections 10

Membership Server

The Membership server contains all resources for supporting Membership. This includes the membership directory (SQL Server database) and all COM objects supporting membership activities (in MTS).

Table 15: Platform

Component Version Explanation
Windows NT Server 4.0 SP4  
SQL Server 6.5 SP4 HF297 SP4 and hotfix 297 are required for and included with Site Server.
Transaction Server (Option Pack) 2.0 Contains all Membership objects called by the Web servers.
Site Server, Membership 3.0 All Membership components and services.
Membership Directory   The Membership Directory is an SQL Server database containing user credentials and accessed through the LDAP service.

Note   The SQL Server configuration is the similar to above.

Host Integration Server

The Host Integration Server provides several functions.

Table 16: Platform

Component Version Explanation
Windows NT Server 4.0 SP4  
SQL Server 6.5 SP4 HF297 SP4 and hotfix 297 are required for and included with Site Server.
Internet Information Server (Option Pack) 4.0  
Transaction Server (Option Pack) 2.0
SQL Server 6.5 SP4 HF297
Site Server, Usage Analysis 3.0
SNA Server / COMTI 4.0 Required for communication with mainframe.
SAP DCOM Connector 1.0 Required for communication with SAP

Configuration

SNA/COMTI. An SDLC card is resident in the Host Integration Server for communication with the mainframe in Boulder.

Supplemental Information

Do’s

Don’ts

Technologies Avoided

Lessons Learned

Be Aware

Appendix A: Code Fragments for Generalized Database Access

Option Explicit

Private Const bConnectLocal = False
Private Const MER_COMMAND_TIMEOUT = 150
Private Const MER_CONNECTION_TIMEOUT = 30

Public Function GetADOConnection(vDB As String, bProductDB As Boolean, adoConn As ADODB.Connection, vbErr As Variant, vbErrDescription) As Long
  Dim lngErrorCode As Long
  Dim strConn As String
  Dim str As String
  Dim bSet48Ret As Boolean
  Dim lng48Value As Long
  
  On Error Resume Next
  ' Open a connection without using a Data Source Name (DSN).
  
  Set adoConn = New ADODB.Connection
  If Err Then
    lngErrorCode = ERROR_CANT_CREATE_CONNECTION
    GoTo GetADOConnectionExit
  End If
 
  Call GetADOConnString(vDB, bProductDB, strConn)
  adoConn.ConnectionString = strConn
  
  adoConn.CommandTimeout = MER_COMMAND_TIMEOUT
  adoConn.ConnectionTimeout = MER_CONNECTION_TIMEOUT
  
  adoConn.Open
  If Err Then
    lngErrorCode = ERROR_CANT_DBCONNECT
    GoTo GetADOConnectionExit
  End If
    
GetADOConnectionExit:
  If Err And (lngErrorCode = 0) Then
    lngErrorCode = ERROR_UNEXPECTED_GETCONN
  End If
  If Err Then
    vbErr = Err.Number
    vbErrDescription = Err.Description & " " & Err.LastDllError
  End If
  GetADOConnection = lngErrorCode
End Function

Public Function DoSearch(vDB As String, bProductDB As Boolean, strProcCall As Variant, vResultSet As Variant, vbErr As Variant, vbErrDescription) As Long
  Dim lngErrorCode As Long
  Dim rstResultSet As ADODB.Recordset
  Dim adoConn As ADODB.Connection

  On Error Resume Next
  
  lngErrorCode = GetADOConnection(vDB, bProductDB, adoConn, vbErr, vbErrDescription)
  If lngErrorCode Then GoTo DoSearchExit
  
  Set rstResultSet = New ADODB.Recordset
  If Err Then
    vbErr = Err.Number
    vbErrDescription = Err.Description & " " & Err.LastDllError & " " & Err.Source
    lngErrorCode = ERROR_CANT_CREATE_RECORDSET
    GoTo DoSearchExit
  End If
  
  Rem See KB Article Q169210
  Rem rstResultSet.CursorType = adOpenStatic
  Rem rstResultSet.CursorType = adOpenKeyset
  ' The forward only cursor is most optimal
 
  rstResultSet.CursorType = adOpenForwardOnly
  
  Rem Use ReadOnly Recordsets
  rstResultSet.LockType = adLockReadOnly
  Rem rstResultSet.LockType = adLockBatchOptimistic
  'Allows for a disconnected recordset
  rstResultSet.CursorLocation = adUseClientBatch
  rstResultSet.Open strProcCall, adoConn, , , adCmdText
  If Err Then
    vbErr = Err.Number
    vbErrDescription = Err.Description & " " & Err.LastDllError
    lngErrorCode = ERROR_CANT_EXEC_STOREDPROC
    GoTo DoSearchExit
  End If
    
  Set rstResultSet.ActiveConnection = Nothing
  
  Set vResultSet = rstResultSet

DoSearchExit:
  If Err And (lngErrorCode = 0) Then
    vbErr = Err.Number
    vbErrDescription = Err.Description & " " & Err.LastDllError
    lngErrorCode = ERROR_UNEXPECTED_DOSEARCH
  End If
  Set rstResultSet = Nothing
  Set adoConn = Nothing
  DoSearch = lngErrorCode
End Function

Public Function DoAppend(vDB As String, bProductDB As Boolean, strProcCall As Variant, vbErr As Variant, vbErrDescription) As Long
  Dim lngErrorCode As Long
  Dim cmdCommand As ADODB.Command
  Dim adoConn As ADODB.Connection

  On Error Resume Next
  
  lngErrorCode = GetADOConnection(vDB, bProductDB, adoConn, vbErr, vbErrDescription)
  If lngErrorCode Then GoTo DoAppendExit
  
  Set cmdCommand = New ADODB.Command
  If Err Then
    vbErr = Err.Number
    vbErrDescription = Err.Description & " " & Err.LastDllError & " " & Err.Source
    lngErrorCode = ERROR_CANT_CREATE_COMMAND
    GoTo DoAppendExit
  End If
  
  cmdCommand.ActiveConnection = adoConn
  cmdCommand.CommandText = strProcCall
  cmdCommand.Execute
  If Err Then
    vbErr = Err.Number
    vbErrDescription = Err.Description & " " & Err.LastDllError
    lngErrorCode = ERROR_CANT_EXEC_STOREDPROC
    GoTo DoAppendExit
  End If
    
  Set cmdCommand.ActiveConnection = Nothing

DoAppendExit:
  If Err And (lngErrorCode = 0) Then
    vbErr = Err.Number
    vbErrDescription = Err.Description & " " & Err.LastDllError
    lngErrorCode = ERROR_UNEXPECTED_DOAPPEND
  End If

  Set adoConn = Nothing
  Set cmdCommand = Nothing
  DoAppend = lngErrorCode
End Function

© 1999-2000 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only.

MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.

Microsoft, ActiveX, BackOffice, FrontPage, Jscript, Visual Basic, Visual InterDev, Visual SourceSafe, Windows, Windows NT are either registered trademarks of Microsoft Corporation in the United States and/or other countries/regions.

The example companies organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred.

Other product or company names may be the trademarks of their respective owners.