| Click here to support our advertisers

SOFTWARE FOR SALE
BOOKS FOR SALE
SEARCH CENTRAL
JOB BANK
CLASSIFIEDS
DIRECTORIES
REFERENCE Online Library
LEARNING CENTER
JOURNAL
NEWS CENTRAL
DOWNLOADS
COMMUNITY
CALENDAR
ABOUT US
Journal:
Get the weekly email highlights from the most popular journal for developers! Current issue
> developer.com developerdirect.com htmlgoodies.com javagoodies.com jars.com intranetjournal.com javascripts.com | All Categories : C/C++ vcg20.htm Using Microsoft SQL Server for Windows NT 6.0 Networking Features and Server Performance Connections, Threads, and Symmetric Multiprocessing An Overview of SQL Server 6.0 Features Using the Microsoft Enterprise Manager to Create a New Database Using SQL Object Manager to Add a Test Table to NorthWind Creating ODBC Datasources from Client-Server Databases Adding the nwind Database as an ODBC Datasource Testing the ODBC Datasource with the MS Query Application Using Visual C++'s SQLConfigDataSource() Function Creating Tables in ODBC Datasources Exporting Tables in NorthWind.MDB to the NorthWind Database on SQL Server Displaying the Result of the Export Operation with SQL Object Manager Adding an Index to an SQL Server Table with the SQL Object Manager Using ODBCTEST to Display the Capabilities of an ODBC Driver Summary # - 20 - Creating Front Ends for Client-Server Databases Up to this point, this book has used desktop database files in all the examples of Visual C++ database applications. Sample applications that use Access database files predominated in the preceding chapters because the .MDB file format is Microsoft's (and the authors') preferred database file structure for applications that don't need to maintain interoperability with existing DOS applications that share dBASE, FoxPro, or Paradox 3+ table files. Another reason for using the .MDB database file structure is that .MDB files resemble the file structures used by client-server databases. This chapter introduces you to the principles employed to create Visual C++ database applications that serve as front ends for client-server RDBMS back ends. The RDBMS used for the majority of the examples in this chapter is Microsoft SQL Server for Windows NT 6.0 (SQL Server) running as a service of Windows NT Server 3.51 (NTS). Therefore, this chapter begins with a description of SQL Server and the use of the new SQL Administrator and SQL Object Manager database tools provided with SQL Server 6.0. The examples in this chapter use the Microsoft ODBC API (version 2.0) and use either the MFC database classes or the SQL...() functions. The examples are equally applicable to most client-server RDBMSs for which ODBC drivers that support core-level SQL grammar and offer level-1 ODBC compliance are available. For example, a program that interacts with an Access .MDB file could easily interact with an xBase file as well. Chapter 7, "Using the Open Database Connectivity API," describes Visual C++ 's interface to the ODBC API in detail and explains how to use single-tier ODBC drivers. Therefore, this chapter discusses only those elements of the ODBC API, such as creating client-server ODBC datasources, that are specific to multiple-tier ODBC drivers and Visual C++ front ends for client-server RDBMSs. This chapter includes an example of exporting data from Access databases to client-server tables because Visual C++ developers often are called on to upgrade Visual C++ (and Access) applications from desktop RDBMSs to client-server database front ends. Designing Visual C++ front ends for client-server databases follows the same methodology as designing applications that manipulate tables of conventional desktop databases, as described in earlier chapters. If you have an RDBMS table structure that duplicates the structure of the tables that compose your desktop database, you can simply change the open statements to open tables in the ODBC datasource instead of those in the desktop database. In most cases, however, using the SQLExecDirect() function instead of the SQLExecute() method greatly improves the performance of client-server front ends when the query is a one-time execution. Thus, this chapter ends with a discussion of how to use SQL pass-through with client-server RDBMSs. ### Using Microsoft SQL Server for Windows NT 6.0 Microsoft SQL Server for Windows NT is a major upgrade from Microsoft SQL Server version 4.21. In this chapter, the term SQL Server refers to SQL Server for Windows NT version 6.0; references to the Sybase version for UNIX computers are preceded by the word "Sybase." You can run SQL Server for Windows NT 6.0 either under Windows NT Workstation 3.5 or Windows NT Server 3.5. The SQL Server Setup program can install client software on SQL Server, MS-DOS, Windows, and Windows NT operating environments. NOTE Prices and configurations of products such as SQL Server change frequently. You should think of the prices and configurations described next as a quick reference only. Before you make any decisions about these products, contact your favorite reseller. SQL Server is available in the following license versions (the prices are based on MSRP and were verified in early 1995): Any number of clients may be used with the server, but each client must also be licensed (see the following note). Single users (16 or fewer) are licensed at a per-user fee, typically less than $200. Microsoft defines the break point as 20 users; however, one 20-user license costs less than 16 single-user licenses. A 20-user license pack costs about $2,000. Licenses for more than 50 users are negotiated on a case-by-case basis. SQL Workstation is a single-user version that allows up to 15 simultaneous connections. This version contains the ODBC SDK and programming files for the DB-Library. SQL Workstation is intended as a development platform. NOTE In mid-1995, Oracle began giving away its product to anyone who wanted to pay shipping and handling or was willing to spend the time downloading the product from Oracle's Internet site for a 30-day evaluation. Only time will tell whether this approach will be successful. However, it's difficult to compete against a free product, especially when that product is as high-quality as Oracle's. #### Networking Features and Server Performance The networking features of Windows NT Server 3.5 and SQL Server are designed for maximum user convenience. For example, you can set up SQL Server so that users are automatically logged on to it when they log on to Windows NT Server 3.5. Automatic logon to the SQL Server server also is provided across remote servers in Windows NT Server 3.5 domains that share a trust relationship. SQL Server supports simultaneous NetBEUI, IPX/SPX, and TCP/IP connections. #### Connections, Threads, and Symmetric Multiprocessing A conversation between an application and SQL Server takes place through one or more connections established by the named-pipes service that originated in OS/2. A connection is similar in concept to that of the Windows Sockets for TCP/IP communication described in the preceding chapter. The default named-pipes protocol for communicating with SQL Server is provided by the DBNMP3.DLL library, which must be present in your \WINDOWS\SYSTEM or WINNT\SYSTEM directory or the current directory for the application. Named-pipes protocols for the other network operating systems are included on the SQL Server disks. NOTE The following information relating to the Access database engine's use of SQL Server connections is based on Microsoft's "Jet Database Engine ODBC Connectivity" white paper, dated July 19, 1993, and written by Neil Black of Microsoft's Jet Program Management group and Stephen Hecht of the Jet Development group. You can download this white paper in Word for Windows format as RJETWP.ZIP from the ODBC Library 11 of the MSACCESS forum on CompuServe. If you use or intend to use ODBC with Visual C++ database applications, you need a copy of this white paper to understand how the Access database engine interacts with the ODBC drivers for client-server databases. When you use the Access database engine to connect to a server back end with the ODBC API and create a Dynaset object from a query, the operation usually requires at least two connections. (One connection obtains information about the query from the server, and the other passes the data to your application.) If your query is based on a complex SQL statement that involves multiple JOINs or GROUP BYs or creates a crosstab query, it's likely that your application will open several connections at once. Although the Access database engine attempts to share connections when it can, there are many situations in which your application might need several simultaneous connections. TIP In a multiuser environment, it's possible to run out of connections with substantially fewer simultaneous users than you've specified as the maximum number of users for SQL Server. It's a common practice to set the number of users as low as practical to conserve memory resources, which are required for each user, for data caching. If you run out of connections, as indicated by an error message returned when your Visual C++ application attempts to run a query, your only solution is to increase the number of users. To do this, you might need to upgrade your SQL Server user license. With today's trend toward more and more memory in servers, the practice of limiting connection counts to conserve memory might not be meaningful. Also, in regard to licensing, SQL Server will count each workstation as a single connection regardless of how many actual connections there are between the workstation and the server. OS/2, Windows 95, and Windows NT are multithreaded operating systems. A thread is a single task that is executed by an application or the operating system. Multithreaded operating systems enable multiple tasks to appear to run simultaneously; the operating system determines the priority of the task and enables tasks with the highest priority to execute first and most often. Tasks with higher priorities can suspend the processing of lower-priority tasks. Operating systems that let task priorities govern tasks' flow of execution are called preemptive multitasking operating systems. Windows NT provides a thread for each connection from a worker pool of threads whose population is set by the sp_configure stored procedure of SQL Server. When a client workstation makes a request from SQL Server, one of the threads from the worker pool is assigned to the workstation's connection. When the request is fulfilled and the connection is terminated, the connection thread is returned to the pool. If a client workstation requests a thread and none is available in the pool, the first thread released by another connection is assigned to the request. When a fault occurs during the execution of a thread, only the connection associated with the thread is affected. Thus, a page fault on a single connection doesn't bring down the server. The advantages of a multithreaded operation are particularly evident when you add more than one microprocessor unit to the server. SQL Server supports thread-level multiprocessing, usually called symmetric multiprocessing (SMP), and is referred to as a scalable operating system. These terms mean that multiple processors that share the same memory region can execute any thread. This process is also called symmetric load balancing. If a thread is executing on a processor that is running near capacity, the thread can be transferred so that it executes on another processor. Windows NT Server, and thus SQL Server, supports up to eight 80x86 processors. Multithreaded SMP operation and the capability to run on a variety of microprocessors, not just members of the Intel 80x86 series, are the two most distinguishing features of Windows NT. NOTE Finding an eight-CPU computer to run Windows NT Server might be an interesting task. When this book was written, four CPUs was the practical limit. #### An Overview of SQL Server 6.0 Features SQL Server 6.0 has new features that simplify installation and aid in the administration of SQL Server servers and clients. Here are the most important new SQL Server features: The capability to address up to 2GB of RAM. Increasing the amount of RAM to create large data caches greatly improves SQL Server's performance. 32M is the smallest amount of RAM you should consider for an SQL Server server. Installations with more than 10 users should use servers with 48M to 64M of RAM. SMP servers should start with 128M of RAM. NOTE In addition to having sufficient RAM, it's important to have a very fast data path to the system's hard disks. Pentium systems with the PCI bus structure, using the Adaptec 2940w PCI SCSI controller, offer an incredible rate of transfer between the hard disks and the system. Graphical database management tools. SQL Server includes 16-bit Windows and 32-bit Windows NT versions of the SQL Administrator, SQL Security Manager, SQL Enterprise Manager, SQL Transfer Manager, and the ISQL command-line SQL processor that you can execute from Windows for Workgroups, Windows 95, or Windows NT workstations. Figure 20.1 shows the SQL Server for Windows NT application group that appears in the server computer's Program Manager window. Windows NT provides additional Setup, Service Manager, Tape Utility, Security Manager, and Performance monitoring tools. These management tools are discussed in the sections that follow. Figure 20.1. The SQL Server application group on an SQL Server. Extended stored procedures. Extended stored procedures that carry the prefix xp_ instead of sp_ let you call functions in Windows DLLs or execute Windows NT command-line instructions directly from SQL Server rather than from your front-end application. As an example, you can use the command-line instruction execute master..xp_cmdshell "net send groupname 'Message'" to transmit a message to members of a group based on a conditional expression in a Transact SQL trigger. (Transact SQL is the dialect of SQL used by both the Microsoft and Sybase flavors of ANSI SQL. SQL Server triggers are explained later in this chapter.) Simplified installation and setup. As with Windows NT Server 3.5, installing SQL is a simple process. You can perform a basic installation of Windows NT Server 3.5 from a CD-ROM, add SQL Server (from its own CD-ROM or the distribution disks) as a Windows NT Server 3.5 process, and get one or two WfWg or Windows 95 workstations running in an hour or less. Installation time doesn't include the time required to fully configure SQL Server or Windows NT Server 3.5. The capability to store the tempdb database in RAM. SQL Server uses the temporary database, tempdb, for workspace and temporary tables that are needed for some sorting and JOIN operations. If you have sufficient RAM for tempdb and the data cache, you can specify that the tempdb database be stored in memory. Storing tempdb in RAM speeds operations that use temporary tables when SQL Server needs to use tempdb. Tape-backup procedures. Automatic timed backup of the database and transaction log is provided as a function of the SQL Object Manager application. You can perform a dump of more than one object to a single tape. This is an important consideration when you use certified tapes for DAT back-up drives. These tapes are expensive. The following two sections demonstrate how to use the Microsoft Enterprise Manager to create a new database object, nwind, in the MASTER.DAT device, and how to add a sample table, test, to the nwind database. ##### Using the Microsoft Enterprise Manager to Create a New Database The Microsoft Enterprise Manager lets you create and manage the following types of objects: Devices, such as new database (.DAT) files and data dump devices that save data to fixed disk and diskette files as well as to tape drives. When you install SQL Server, default master database file (MASTER.DAT), diskdump, diskettedumpa (drive A), and diskettedumpb (drive B) devices are created for you. Databases that exist within database file devices. You can add new databases or drop databases from .DAT files. During installation, SQL Server creates the master, model, pubs, and tempdb databases within the MASTER.DAT device. Segments that constitute a portion of one or more databases. You use segmented databases to spread a database over more than one disk drive. Segmentation improves the performance of large databases stored on RAID 0 or RAID 5 disk arrays. (Segments actually are pointers to the location of the segment.) The advantage of the Microsoft Enterprise Manager is that you don't need to type Transact SQL statements at the ISQL command prompt or in the ISQL/w application's query pane to create or modify devices. The following example shows you how to use SQL Administrator to create an nwind database in the MASTER.DAT device: Launch the Microsoft Enterprise Manager from the SQL Server application group. The Register Server dialog box, shown in Figure 20.2, appears. If you have more than one server in your domain, go to the Server combo box and select the server to which you want to connect. Figure 20.2. The Register Server dialog box in the Microsoft Enterprise Manager. Type your server user ID and password in the Login ID and Password text boxes. If you haven't established an account for yourself, type sa (for "system administrator") in the Login ID text box and leave the Password text box empty. Click the Register button to create a connection to the server and activate SQL Administrator's main window. Click the DB button on the toolbar or choose Manage | Database to display the Manage Databases MDI child window, shown in Figure 20.3. Figure 20.3. The default databases in MASTER.DAT. Click the New Database icon to display the New Database dialog box, shown in Figure 20.4. Figure 20.4. The New Database dialog box for adding a new database and log to the MASTER.DAT device. Type NorthWind in the Name text box and accept the default master as the Data Device and (none) as the Log Device. Type 2 in the Size (MB) text box to create a database object with a maximum size of 2M. Then click the OK button. (NorthWind.MDB is more than 1M in size, and you can specify database sizes only in 1M increments.) NOTE Most client-server RDBMSs require that you specify a maximum size for a database and its corresponding transaction log file. This is a sample database, so you don't need a transaction log file to restore the database in case of hardware failure. (In order to store the log file, you need to create a new device located on a disk drive other than the one on which the database file device is located.) The default size of an SQL Server database is the remaining space in the database file device. After a few seconds, the New Database Dialog disappears and the Manage Databases dialog appears, showing the new database, NorthWind (see Figure 20.5). Figure 20.5. The Manage Databases dialog box, confirming the creation of a new database in the MASTER.DAT device. Close Enterprise Manager by double-clicking the application control menu box or by choosing File | Exit. Closing SQL Administrator also closes the connection to the server. NOTE RDBMSs that have their roots in UNIX conventionally use lowercase letters to identify all database devices and objects, just as it is the norm (actually, a requirement) to use uppercase letters to identify database objects in mainframe databases such as IBM DB2. ##### Using SQL Object Manager to Add a Test Table to NorthWind You can use Enterprise Manager to create or modify objects in database containers. Using Enterprise Manager isn't much different from creating a new table in Access's table-design mode or using the table-design function of the MSQuery sample application. To add a sample table called "test" to the NorthWind database and define some sample fields, follow these steps: Using the procedure described in steps 2, 3, and 4 in the preceding section, launch Enterprise Manager and connect to the server to display the Enterprise Server Manager window, shown in Figure 20.6. Figure 20.6. The Enterprise Server Manager Window. Select the desired server and expand its outline. From the expanded outline, choose the NorthWind database. Choose Manage | Tables to open the Manage Tables window, shown in Figure 20.7. The default name for a new database table is, not surprisingly, <New>. Figure 20.7. Adding fields to a new table definition with SQL Object Manager. Add some fields to the table by entering a field name in the Column Name cell, and then choose a data type for the field by selecting the Datatype cell. Figure 20.7 shows typical field names and Transact SQL data types for the test table. After you've added a representative collection of fields to the test table, click the Save icon. The Specify Table Name dialog, shown in Figure 20.8, appears. Figure 20.8. The Specify Table Name dialog box, in which you name a new table. Type test in the New table name text box and click the OK button to add the fields to the table. Notice that the outline under the NorthWind database has changed to include the new table (see Figure 20.9). Other database objects, Rules, Stored Procedures, and Views, can be manipulated in a similar way. Figure 20.9. The Server Manager window after you complete the design of the test table. Close the Enterprise Manager application. The test table created in the preceding steps is used later in this chapter to demonstrate the correspondence between Transact SQL data types and the data types supported by the Access database engine. ### Creating ODBC Datasources from Client-Server Databases Examples in Chapter 7 explain the procedure for creating ODBC datasources from desktop databases using the ODBC database drivers included with Microsoft Office, Excel, and the Microsoft ODBC Desktop Database Drivers kit. The procedure described in the following section for adding the NorthWind database as an ODBC datasource is typical of the method of adding a datasource from the majority of popular client-server RDBMSs for which ODBC drivers are available. Each supplier has a slightly different approach to the design and collection of control objects in the dialog boxes used to set RDBMS-specific options for the datasource. The following two sections describe how to add the NorthWind database as an SQL datasource and how to test the validity of your new ODBC datasource with the MS Query application. #### Adding the nwind Database as an ODBC Datasource To use the SQL Server ODBC database driver to add the NorthWind database as an ODBC, follow these steps: Launch the ODBC Administrator application. If you installed the ODBC Desktop Database Drivers kit, Microsoft Office, or Microsoft Excel (or almost any ODBC application, such as ReportSmith), the ODBC Administrator's icon is located in Control Panel's window to display the Data Sources dialog box. Click the Add button to display the Add Data Source dialog box, shown in Figure 20.10. Figure 20.10. The ODBC Administrator application's Add Data Source dialog box. Choose SQL Server from the Installed ODBC Drivers list box and click the OK button to display the ODBC SQL Server Setup dialog box, shown in Figure 20.11. Figure 20.11. Creating a NorthWind ODBC datasource. Click the Options button to expand the ODBC SQL Server Setup dialog box and type NorthWind in the Data Source Name text box. Add an optional description in the Description text box. Go to the Server combo box and select the server for the NorthWind database. If no entries appear, type in the server name. Accept the (Default) entries for the Network Address and Network Library if you're using NetBEUI (the default) as your network protocol. Type NorthWind in the Database Name text box and select (Default) in the Language Name combo box. Click the OK button to close the ODBC SQL Server Setup dialog box. Your new datasource appears in the Data Sources dialog box, as shown in Figure 20.12. Figure 20.12. The Data Sources dialog box with the new Northwind datasource added. Click the Close button to close the Data Sources dialog box. #### Testing the ODBC Datasource with the MS Query Application The ODBC Administrator application doesn't test the validity of your ODBC datasource when you create the datasource within the ODBC Server Setup dialog box. Therefore, you need to verify that the entries created by the ODBC Administrator application in your ODBC.INI file are correct. The entries in the [ODBC Data Sources] and [NorthWind] sections of ODBC.INI in your \WINDOWS directory (assuming that you're using WfWg or Windows 95 and not Windows NT) are as follows. A more thorough discussion of ODBC and the Windows 95 registry is found in Chapter 7.
>     [ODBC Data Sources]

>     Northwind=Access Data (*.mdb)

>     Bibliography=Access Data (*.mdb)

>     MSQueryDBF=dBase Files (*.dbf)

>     AccessCrosstab=Access Data (*.mdb)

>     nwind=SQL Server

>     [NorthWind]

>     Driver=D:\WINDOWS\SYSTEM\sqlsrvr.dll

>     Description=Northwind Traders on SQL Server

>     Server=oakleaf0

>     Database=nwind

>     OemToAnsi=No

>     LastUser=sa

>     34Cancel=
> NOTE The first four entries in the [ODBC Data Sources] section of ODBC.INI result from the examples of using the desktop database drivers that are described in Chapter 9, "Designing a Decision-Support Application." All the entries in the [NorthWind] section result from the entries in the ODBC SQL Setup dialog box described in the preceding section. To test your SQL Server ODBC datasource, follow these steps: Launch MS Query using the MS Query icon or the Microsoft Office Toolbar button for MS Query. From MS Query, choose File | New Query. Choose NorthWind from the Select Data Source dialog box, shown in Figure 20.13, and click the Use button. Figure 20.13. The Select Data Source dialog box. A list of all the tables in the NorthWind database appears in MS Query's Select Table dialog. Getting this far validates your connection to the NorthWind database. To display and modify the structure of the test table, click the Close button. Select File | Table Structure, choose the NorthWind database, and click the View button. The View Table Definition dialog box appears, as shown in Figure 20.14. Figure 20.14. The View Table Definition dialog box. NOTE The Access database engine doesn't convert some of the Transact SQL field data types to the expected Access field data types. The primary problem is that the longbinary field, specified as Transact SQL's image data type, which is not supported by Access, converts to a binary field of 0 length instead of the expected longbinary field of variable length. Access also doesn't support fixed-length binary fields. These problems are expected to be resolved by future versions of the SQL Server ODBC driver and the Access database engine. For more information on type conversions, refer to the following topics found in the ODBC 2.0 Programmer's Reference: Converting Data from SQL to C Data Types Core C Data Types Extended C Data Types Click the Cancel button in the View Table Definition dialog box to return to MS Query's main window, and then close MS Query. Regardless of the problems relating to conversion between Transact SQL and Access data types, you have verified the operability of your SQL Server ODBC datasource. #### Using Visual C++'s SQLConfigDataSource() Function The SQLConfigDataSource() function lets you perform the same functionality as making entries in an ODBC setup dialog box (such as the ODBC SQL Server Setup dialog box, shown in Figure 20.11). You can use the SQLConfigDataSource() function to create, delete, or modify ODBC datasource entries in ODBC.INI, identical to those you create with the ODBC Administrator's setup procedure. Your code needs to execute the SQLConfigDataSource() function before executing the OpenDatabase() function that refers to the ODBC datasource that has been modified by the SQLConfigDataSource() function. The syntax of the SQLConfigDataSource() instruction is as follows:
>     //      Add the datasource to ODBC for the user:

>         sprintf(szBuffer,

>             "DESCRIPTION=STARmanager saved DB.|"

>             "DSN=StarDatabase files|"

>             "FileType=RedISAM|"

>             "SingleUser=True|"

>             "UseSystemDB=FALSE|"

>             "DataDirectory=%s|"

>             "DATABASE=%s||", szOutFileName, szOutFileName);

>             nLength = (int)strlen(szBuffer);

>             for (i = 0; i < nLength; i++)

>             {

>                 if (szBuffer[i] == '|')

>                 {

>                      szBuffer[i] = '\0';

>                 }

>             }

>             SQLConfigDataSource(NULL, ODBC_ADD_DSN, "Access Data (*.mdb)",

>                 szBuffer);
> SQLConfigDataSource()'s prototype is
>     SQLConfigDataSource(hwndParent, fRequest, lpszDriver, lpszAttributes);
> The hWndParent argument is the parent window's handle. The fRequest argument is one of the following manifest constants: ODBC_ADD_DSN, ODBC_CONFIG_DSN, or ODBC_REMOVE_DSN. The lpszDriver argument points to a string that contains the driver description (typically the name of the associated DBMS) that is presented to the user in lieu of the physical driver's name. The lpszAttributes argument points to a string containing a list of keyword-value pairs. A typical lpszAttributes string might contain this:
>     DSN=Personel Data\0OUID=Jones\0OPWD=Password\0DATABASE=Personel\0
This code fragment shows the keyword-value pairs for an Access database. These keyword-value pairs are described further in the SQLDriverConnect() function's description and in the documentation for each specific ODBC driver. When an ODBC_CONFIG_DSN request is made, any of the values in the lpszAttributes argument that don't match current entries in the ODBC.INI topic for the datasource are updated. Attribute values aren't case-sensitive. ### Creating Tables in ODBC Datasources You can create new tables in an ODBC database by exporting the structure and data of Access tables to the ODBC datasource using the retail version of Access. The most common situation in which you need to export tables is when you upgrade a Visual C++ database application that uses the Access .MDB file structure to a client-server RDBMS front end. Exporting Access tables is often a more convenient method of creating RDBMS tables than using SQL Object Manager or writing SQL CREATE TABLE statements to define the table's structure. Exporting table data is simpler but slower than using SQL Server's bulk copy program, BCP, or equivalent applications of other RDBMSs to add records to the table. The following sections describe how to use Access to export tables from NorthWind.MDB to the NorthWind database on SQL Server. #### Exporting Tables in NorthWind.MDB to the NorthWind Database on SQL Server The Northwind Traders sample database supplied with Access provides a variety of tables that you can use to test the capabilities of both Access and the ODBC API to create tables in client-server RDBMSs. However, using Access's export feature with the ODBC API and the latest version of the SQL Server ODBC driver isn't a rapid process when it involves tables that contain a large number of records. Be prepared for a very long wait if you export NorthWind.MDB's Order Details table to SQL Server. If you have less than 8M of RAM on the workstation running Access, don't even think about trying the following example; the wait becomes interminable. To export an Access table to SQL Server, follow these steps: Open Access and NorthWind.MDB. From Access, choose File | Save As to display the Save As dialog box. Select To an external File or Database and click the OK button. The Save Table...In... dialog box, shown in Figure 20.15, appears. Figure 20.15. Access 7's Save Table...In... dialog box. Select ODBC Databases from the Save as type list box and click the Export button to display the Export dialog box, shown in Figure 20.16. This dialog box confirms the table that will be exported. Click OK to display the SQL Data Sources dialog box, shown in Figure 20.17. Figure 20.16. Selecting the Customers table to export to an ODBC database. Figure 20.17. Access's SQL Data Sources dialog box for ODBC datasources. Select the SQL Server datasource. If you don't have an SQL Server datasource configured, click the New button to add one. Click the OK button to display the SQL Server Login dialog box, shown in Figure 20.18. Your own login user ID automatically appears in the Login ID text box, but you can change it if you want to. Enter your password if you aren't using the default system administrator user ID. Figure 20.18. The SQL Server driver's SQL Server Login dialog box. Click the OK button to begin the export process. Access's status bar displays the completion percentage of the process, so you can estimate how long it takes to export all of the records. #### Displaying the Result of the Export Operation with SQL Object Manager You can use the MS Query application to display the structure of the table you exported in the preceding section. MS Query, however, creates a Snapshot object of the entire contents of the table when you open your query. This can take an appreciable amount of time for a table with many records. Using Enterprise Manager to display the table structure is a faster approach. Follow these steps to display the structure of the SQL Server version of one of NorthWind.MDB's tables you exported to the temp database and run a simple query to verify that its records are present: Launch Enterprise Manager and connect to the server. Select NorthWind in the Server Manager window and select the Objects folder under the NorthWind database. Open the Tables folder to display the exported tables, as shown in Figure 20.19. Figure 20.19. The Server Manager window showing all the objects, including the tables in the NorthWind database. Double-click one of the new tables you exported to display the structure of the table, as shown in Figure 20.20. Figure 20.20. Displaying the structure of an exported Access table in the Manage Tables dialog. NOTE SQL Server, as well as other client-server RDBMSs, doesn't allow spaces in table or field names. The SQL Server ODBC driver substitutes underscores (_) for spaces and any other illegal punctuation in the names of Access tables or fields. Although field names in SQL Server databases traditionally use lowercase letters, capitalization of Access field names is preserved in the export operation. Select the Query Analyzer to display the Query window. Enter a simple query, such as the one shown in Figure 20.21. Figure 20.21. Entering a simple SELECT query in SQL Administrator's Query window. NOTE Table and field names are case-sensitive in SQL Server. Therefore, you need to type them exactly as they appear in the Column Name column of the Manage Tables window. Click the Execute button to display the query result in the Results window for query #1, as shown in Figure 20.22. Figure 20.22. The result of the SELECT query shown in Figure 20.21. TIP Use the SQL Server's BCP utilities to speed the import of data from large Access tables to SQL Server databases. Create copies of the Access tables using the clipboard and then delete all but a few records in the cloned tables. Export the table structure and the few records to the SQL Server database to create the new table structure and then delete the records in the SQL Server tables. Export the data from the Access tables to a comma-delimited text file. Then use SQL Object Manager's BCP utility to run a bulk copy operation to each SQL Server table. Michael J. Smith, a software engineer and DBA, reported in the article "SQL Server for Windows NT: A Case Study" in the May/June 1993 issue of SQL Forum that he was able to achieve a bulk copy rate of 530 records per second over a busy network running the BCP utility from a workstation. (Running BCP from the server reduces the bulk copy rate by about 50 percent.) #### Adding an Index to an SQL Server Table with the SQL Object Manager When you export an Access table to a client-server database, Access doesn't create indexes on the client-server table. You need to use the SQL CREATE INDEX statement or the Enterprise Manager to manually add indexes to the client-server table. You can add one clustered or as many nonclustered indexes as you want to a table with Enterprise Manager. Clustered indexes, which physically place the pages of the table in the index order, greatly improve the performance of SQL Server databases. However, you gain a performance improvement with a clustered index only if you specify that the index must be unique. (Clustered indexes are not unique by default.) To use Enterprise Manager to create a clustered primary key index on an imported table or any other table in an SQL Server database, follow these steps, which assume that the table you chose for the preceding example is open: Choose Manage | Indexes to display the Manage Indexes dialog box for a new index, shown in Figure 20.23. Choose the table you want to index from the Table combo box and click the Build button. Figure 20.23. The dialog box to create a new index on an SQL Server table. Enter a name for the index in the New index name field, as shown in Figure 20.24. Figure 20.24. Specifying the name of an index to be added to an SQL Server table. If you're creating an index on the primary key, click the Clustered and Unique cells. Then choose the field(s) you want to index in order to create the primary key in the cells corresponding to the field names, as shown in Figure 20.25. Figure 20.25. Specifying the properties of an index on an SQL Server table. Click the Create button to create the new index. You will then be asked if you want to schedule the build for later or execute it, as shown in Figure 20.26. Figure 20.26. Confirming the addition of a new index to an SQL Server table. When indexing is complete, close the Enterprise Manager application. You can verify that the index is present and is recognized by the SQL Server ODBC driver by attaching the table to Access and then opening the table in design mode. Access assumes that a unique clustered index is the primary key index of an SQL Server table and indicates the key field(s) with the key symbol in the field selector button(s). ### Using ODBCTEST to Display the Capabilities of an ODBC Driver ODBCTEST is an application included with the Microsoft ODBC SDK that lets you inspect and use the low-level functions of the ODBC API. ODBCTEST is used primarily by developers of ODBC database drivers to debug and test the drivers' performance. If you intend to use different types of ODBC datasources or evaluate different drivers for a single data type, ODBCTEST lets you inspect and test each of the driver's functions. NOTE ODBCTEST replaces the GATOR.EXE program that was part of the ODBC version 1.x SDK. There are only minor differences between ODBCTEST and GATOR. Figure 20.27 shows the ODBC Test window of ODBCTEST after you connect to the NorthWind database and then choose Connect | GetInfo All. The hdbc and hstmt combo boxes let you select the handle to the connected datasource(s) and statement(s) you execute either in the upper pane of the ODBC Test window or by choosing options from a menu. Figure 20.27. The first few items returned by ODBCTEST's GetInfo All menu option. The GetInfo All menu choice returns more than you might want to know about the ODBC driver used to create the connection to the ODBC datasource. One of the most important features of the display provided by the GetInfo All command is the capability to determine the functions and data type conversions that the driver supports. You can use only those functions in your SQL statements that the ODBC driver translates into the appropriate syntax for the RDBMS in use. The documentation for supported functions that is supplied with ODBC drivers often doesn't include this information, and other times the information provided is incomplete. Figure 20.28 shows a few of the functions supported by version 1.02.3109 of the SQL Server ODBC driver. Figure 20.28. ODBCTEST's list of some of the functions supported by the Microsoft ODBC driver for SQL Server. TIP ODBCTEST's File menu has a Print option, but it's always disabled. You can copy the information in the lower pane of the ODBCTEST's info window to the clipboard. If you want, you can then paste the data into Notepad in order to format and print the information so that it's easier to read. ### Summary This chapter emphasized that creating Visual C++ front-end client-server applications doesn't differ substantially from designing similar applications that connect to conventional desktop databases with the Access database engine. The principal issues for the Visual C++ database developer are setting up the server database and the ODBC datasource(s) for the client-server back end. Thus, this chapter was devoted primarily to discussing the characteristics of a new, fast, and powerful client-server RDBMS, Microsoft SQL Server for Windows NT, and the Microsoft ODBC API. The next chapter describes how to create mail-enabled Visual C++ applications that use the MAPI interface. Contact reference@earthweb.com with questions or comments. Copyright 1998 EarthWeb Inc., All rights reserved. PLEASE READ THE ACCEPTABLE USAGE STATEMENT. Copyright 1998 Macmillan Computer Publishing. All rights reserved. |