Cover image for 10 projects you can do with Microsoft SQL Server 7
10 projects you can do with Microsoft SQL Server 7
Watterson, Karen L.
Personal Author:
Publication Information:
New York : Wiley, [2000]

Physical Description:
xvi, 686 pages : illustrations ; 24 cm + 2 computer optical discs (4 3/4 in.)
General Note:
Includes index.

"Wiley computer publishing."
Title Subject:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.C55 W378 2000 TEXT Book and Software Set Central Closed Stacks

On Order



Learn Microsoft SQL Server 7 by successfully completing hands-on projects

You don't have to be a fortune teller to realize that Microsoft's SQL Server 7 is going to be the most widely used database product on the planet.

This book, unlike typical reference books, shows you how to use SQL Server to add value to your business operations. Drawing inspiration from the lab manuals you used in biology and earth science classes, the authors provide step-by-step instructions for accomplishing the kinds of projects you'll want to do with SQL Server.

Here's a taste of some of the projects:
* How to build an e-commerce site with Site Server Commerce Edition
* Implementing a data-driven Intranet with SQL Server and IIS
* Creating both single-source and multisource data marts
* Working with OLAP Services and a variety of OLAP clients
* Writing Visual Basic applications that work with SQL Server

Plus two CD-Roms!
* CD-ROM 1 contains Microsoft SQL Server 7.0 120-Day Evaluation Edition
* CD-ROM 2 contains all the code from the book, example applications, 18 third party

Development tools, and links to related Web sites

Author Notes

KAREN WATTERSON is an independent San Diego-based writer and consultant specializing in data warehouse and knowledge management issues. She is the editor of Pinnacle Publishing's monthly SQL Server Professional and Visual Basic Developer newsletters, coauthor for Windows 2000 magazine, and author of four books. She has been using SQL Server since 1988.
BILL SHADISH is a principal of Fundamental Objects, Inc.( where he works with ActiveX controls, COM server, project management tools, and handheld technology. Bill writes for a number of Visual Basic trade journals including Visual Basic Developer and Inside Visual Basic. He has coauthored three books.
GARTH WELLS is an independent consultant who has worked in database design and development for more than six years and with SQL Server since version 4.2. He has successfully implemented and/or managed database solutions in the following industries: Computer Design and manufacturing, Automobile Finance, Software Development, and Commercial Real Estate Management and Financial Services. Visit his company's Web site at www.

Table of Contents

Forewordp. xii
Prefacep. xiii
Acknowledgmentsp. xv
About the Authorsp. xvi
Part 1 Getting Startedp. 1
Chapter 1 Introduction to SQL Serverp. 3
How SQL Server Fits into Microsoft's Enterprise Architecturep. 5
Choices, Choicesp. 8
Data Warehousing and OLAPp. 10
DNAp. 11
Digital Nervous Systemp. 13
Microsoft Frameworkp. 15
SQL Server Architecturep. 16
Databases Consist of Tablesp. 20
Databases Contain Other Objects, Toop. 24
Is SQL Server Really a Seventh Generation Product?p. 27
The "SQL" in SQL Serverp. 28
The SQL SELECT Statementp. 29
Summaryp. 34
Chapter 2 Installing SQL Serverp. 35
Plan Aheadp. 37
Hardwarep. 38
Softwarep. 42
Pre-Setup Checklistp. 44
More on Assigning NT Accounts to SQL Server Servicesp. 46
Autorun Installationp. 49
The Prerequisitesp. 49
Installation Optionsp. 52
What if?p. 59
Proof Positivep. 60
Running SQL Server Enterprise Managerp. 62
Refining Your Installationp. 71
Download the Latest SPp. 74
Launching Projectsp. 74
Chapter 3 Backup and Recoveryp. 75
Before We Get Startedp. 76
Who Needs Backup and Recovery?p. 76
You Look Like You Need an Overviewp. 77
How Much Can You Afford to Lose?p. 78
Backup Devicesp. 80
Creating a Backup Devicep. 81
Deleting a Backup Devicep. 83
Backup Optionsp. 85
Database Backupp. 85
Differential Database Backupp. 94
Transaction Log Backupp. 96
How to View and Verify the Contents of a Backup Devicep. 103
Enterprise Managerp. 103
Transact-SQLp. 104
Restore Operationsp. 105
Restoring a Database Backupp. 105
Restore to a Point of Failurep. 110
Implementing a Planp. 112
Testing and Implementing Your Planp. 117
Part 2 Projectsp. 119
Chapter 4 Project #1: Upsizing Your Desktop Databasep. 121
Upsizing to SQL Server--The Issuesp. 124
How Do I Convert My Data?p. 125
The Excel Projectp. 125
Step 1 Create a New Database (and Let the Wizard Do the Walking)p. 126
Step 2 Import the Data into SQL Serverp. 129
Upsizing Access Databasesp. 136
What about Data Types?p. 137
Object and Method Changesp. 138
Conclusionp. 139
The Access Projects: Upsizing the WorkOrd Databasep. 139
The Source Datap. 139
Method 1 Use the Upsizing Wizardp. 140
Method 2 Use DTSp. 151
In and Out with bcpp. 162
Summaryp. 167
Chapter 5 Project #2: Publishing SQL Server Data to HTMLp. 169
Intranet Basicsp. 170
Start Thinking Aheadp. 171
Easiest Firstp. 171
Running the Web Assistant Wizardp. 173
Back to the Main Roadp. 177
Using Our Own Codep. 185
Running the Web Assistant Wizard Using Stored Proceduresp. 192
Taking Stockp. 195
Using Excel to Publish SQL Server Datap. 195
Summaryp. 198
Chapter 6 Project #3: Building a Corporate Intranet with Internet Information Serverp. 201
Setting Up Internet Information Server (IIS)p. 202
Installing IISp. 203
Configuring IISp. 203
Managing Securityp. 207
Physical Securityp. 208
Network Securityp. 208
Understanding User Accounts and User Groupsp. 210
Understanding Domains (NT4)p. 211
Aliased or Virtual Directoriesp. 211
Creating an Intranet Web-Based Solutionp. 212
Creating the Human Resources Web Sitep. 216
HTMLp. 216
Active Server Pages (ASPs)p. 220
Summaryp. 239
Chapter 7 Project #4: Create a Single-Source Data Martp. 241
The "Classic" Definition for Data Warehousingp. 242
Data Martsp. 247
Buy or Build?p. 247
Populating a Data Warehouse or Data Martp. 248
Inventory Your Datap. 248
Design the New Databasep. 249
Extract the Datap. 249
Data Cleansing and Transformationp. 249
Loadp. 250
Other Methodologiesp. 250
Microsoft Enters the Frayp. 251
Practice Makes Perfectp. 254
Creating a Data Martp. 255
Analysisp. 255
Business Needs Firstp. 256
"Seeing" What You Havep. 256
Creating the SQL to Load the DMp. 259
Following up the Salep. 261
Visually Connectingp. 263
Loading the workOrdDM from the CDp. 268
Closing the Loopp. 268
Summaryp. 269
Chapter 8 Project #5: Creating a Multi-Source Data Mart with Data Transformation Servicesp. 271
Overviewp. 272
OLTP Design, Star Schema, and Dimensional Designp. 274
Projectp. 282
Business Scenariop. 282
Transferring and Transforming the Datap. 284
Sales Datap. 298
Verifying the Datap. 306
Summaryp. 307
Chapter 9 Project #6: Working with OLAP Servicesp. 309
Where Did OLAP Come From?p. 311
OLAP-Speakp. 316
Creating a Multi-Dimensional Databasep. 320
Investigating an Existing Multi-Dimensional Databasep. 320
Exploring the Sales Cubep. 325
The Warehouse Cubep. 334
Run the OLAP Manager Tutorialp. 335
OLAP: The Client Sidep. 337
Excel's Pivot Tablep. 337
The MDX Sample Applicationp. 341
The MDX Languagep. 343
Exploring Cubes with Third-Party Toolsp. 352
Maximal Innovative Intelligence Max 1.0p. 352
Cognos NovaView 2.0p. 354
Summaryp. 356
Chapter 10 Project #7: Implementing Replicationp. 355
Backgroundp. 356
Distributed Datap. 358
Introduction to the Replication Processp. 359
Replication Componentsp. 362
Log Shippingp. 368
About the Projectsp. 368
Project 7A: Snapshot Replicationp. 368
Troubleshootingp. 394
Summaryp. 395
Chapter 11 Project #8: Getting Started with E-Commercep. 399
The Components of E-Commercep. 400
Domain Namep. 401
Site Locationp. 401
Securityp. 404
Handling Online Transactionsp. 408
Security Re-Visitedp. 410
The Shopping Cartp. 410
The First Projectp. 411
Examining the V-Nursery Sitep. 411
What Microsoft Site Server Brings to the Tablep. 418
Site Server Standard Editionp. 421
Site Server Commerce Editionp. 424
Creating and Configuring A Virtual Rootp. 439
VisualCommerce Constructorp. 448
Summaryp. 455
Chapter 12 Project #9: Encapsulating Business Logic with Stored Procedures and Triggersp. 457
What Are Stored Procedures and Triggers?p. 459
Central Management of Codep. 459
Stored Procedures to Simplify Securityp. 459
How to Create, Edit, and Delete Stored Proceduresp. 461
Creating a Stored Procedurep. 461
Editing a Stored Procedurep. 466
Deleting a Stored Procedurep. 468
Debugging Stored Proceduresp. 468
Triggersp. 472
Creating a Triggerp. 473
Modifying a Triggerp. 474
Deleting a Triggerp. 476
Specificationsp. 477
Business Logic/Pseudocodep. 478
Converting the Pseudocode to SQLp. 480
Summaryp. 488
Chapter 13 Project #10: Using Visual Basic to Write SQL Applicationsp. 489
Ancient Historyp. 490
Where We Are Todayp. 491
E-I-E-I-Op. 491
The Big Threep. 496
The Basicsp. 496
Drilling Down into DAOp. 499
Sample Code to Access SQL Server with RDOp. 520
Drilling Down into ADOp. 525
Error Handlingp. 535
ADO Error Handlerp. 535
The Dynamic SQL Projectp. 535
Project Specsp. 536
Creating the Survey Web Sitep. 551
Active Server Pagesp. 552
Creating Server Components with Visual Basicp. 570
Server Componentsp. 570
Summaryp. 577
Chapter 14 More Project Ideasp. 579
Knowledge Management: Beyond the Hypep. 579
KM and Microsoft's Digital Nervous Systemp. 581
Overhead and Power Strugglesp. 581
Microsoft's Approach to KMp. 582
Working with Full-Text Indexingp. 585
Installing Full-Text Indexingp. 587
Implementing Full-Text Indexingp. 589
Data Miningp. 594
Data Mining Techniquesp. 597
Examples of Data Miningp. 598
Natural Language Processingp. 599
English Queryp. 601
The Evolving World of Windows CE and Its Devicesp. 604
What's Windows CE?p. 605
CE Servicesp. 606
Getting Your Ducks in a Rowp. 608
Working with VBCEp. 609
Mapping Applicationsp. 615
Mapping Enginesp. 616
Conclusionp. 621
Part 3 Appendicesp. 623
Appendix A SQL Survival Guidep. 625
Appendix B More Informationp. 651
Appendix C What's on the CD-ROMp. 663
Indexp. 667