10 Projects You Can Do with Microsoft,^® SQL Server^TM 7

by Karen Watterson; Bill Shadish; Garth Wells

• ISBN13: 9780471327516
• ISBN10: 0471327514
• Edition: CD
• Format: Paperback
• Copyright: 1999-01-01
• Publisher: Wiley

Summary

Learn Microsoft SQL Server 7 by successfully completing hands-on projects You don2t have to be a fortune teller to realize that Microsoft2s 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 you2ll want to do with SQL Server. Here2s 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 Biography

KAREN WATTERSON is an independent San Diego&#150;based writer and consultant specializing in data warehouse and knowledge management issues. She is the editor of Pinnacle Publishing&#146;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.<BR>

Table of Contents

Foreword	xii
Preface	xiii
Acknowledgments	xv
About the Authors	xvi
Part One Getting Started	1	(118)
Introduction to SQL Server	3	(32)
How SQL Server Fits into Microsoft's Enterprise Architecture	5	(11)
Choices, Choices	8	(2)
Data Warehousing and OLAP	10	(1)
DNA	11	(2)
Digital Nervous System	13	(2)
Microsoft Framework	15	(1)
SQL Server Architecture	16	(11)
Databases Consist of Tables	20	(4)
Databases Contain Other Objects, Too	24	(3)
Is SQL Server Really a Seventh Generation Product?	27	(1)
The ``SQL'' in SQL Server	28	(6)
The SQL SELECT Statement	29	(5)
Summary	34	(1)
Installing SQL Server	35	(40)
Plan Ahead	37	(7)
Hardware	38	(4)
Software	42	(2)
Pre-Setup Checklist	44	(5)
More on Assigning NT Accounts to SQL Server Services	46	(3)
Autorun Installation	49	(11)
The Prerequisites	49	(3)
Installation Options	52	(7)
What if?	59	(1)
Proof Positive	60	(11)
Running SQL Server Enterprise Manager	62	(9)
Refining Your Installation	71	(3)
Download the Latest SP	74	(1)
Launching Projects	74	(1)
Backup and Recovery	75	(44)
Before We Get Started	76	(1)
Who Needs Backup and Recovery?	76	(1)
You Look Like You Need an Overview	77	(1)
How Much Can You Afford to Lose?	78	(2)
Backup Devices	80	(5)
Creating a Backup Device	81	(2)
Deleting a Backup Device	83	(2)
Backup Options	85	(18)
Database Backup	85	(9)
Differential Database Backup	94	(2)
Transaction Log Backup	96	(7)
How to View and Verify the Contents of a Backup Device	103	(2)
Enterprise Manager	103	(1)
Transact-SQL	104	(1)
Restore Operations	105	(7)
Restoring a Database Backup	105	(5)
Restore to a Point of Failure	110	(2)
Implementing a Plan	112	(5)
Testing and Implementing Your Plan	117	(2)
Part Two Projects	119	(504)
Project #1: Upsizing Your Desktop Database	121	(48)
Upsizing to SQL Server---The Issues	124	(1)
How Do I Convert My Data?	125	(1)
The Excel Project	125	(11)
Create a New Database (and Let the Wizard Do the Walking)	126	(3)
Import the Data into SQL Server	129	(7)
Upsizing Access Databases	136	(3)
What about Data Types?	137	(1)
Object and Method Changes	138	(1)
Conclusion	139	(1)
The Access Projects: Upsizing the WorkOrd Database	139	(23)
The Source Data	139	(1)
Use the Upsizing Wizard	140	(11)
Use DTS	151	(11)
In and Out with bcp	162	(5)
Summary	167	(2)
Project #2: Publishing SQL Server Data to HTML	169	(32)
Intranet Basics	170	(1)
Start Thinking Ahead	171	(1)
Easiest First	171	(24)
Running the Web Assistant Wizard	173	(4)
Back to the Main Road	177	(8)
Using Our Own Code	185	(7)
Running the Web Assistant Wizard Using Stored Procedures	192	(3)
Taking Stock	195	(1)
Using Excel to Publish SQL Server Data	195	(3)
Summary	198	(3)
Project #3: Building a Corporate Intranet with Internet Information Server	201	(40)
Setting Up Internet Information Server (IIS)	202	(5)
Installing IIS	203	(1)
Configuring IIS	203	(4)
Managing Security	207	(9)
Physical Security	208	(1)
Network Security	208	(2)
Understanding User Accounts and User Groups	210	(1)
Understanding Domains (NT4)	211	(1)
Aliased or Virtual Directories	211	(1)
Creating an Intranet Web-Based Solution	212	(4)
Creating the Human Resources Web Site	216	(23)
HTML	216	(4)
Active Server Pages (ASPs)	220	(19)
Summary	239	(2)
Project #4: Create a Single-Source Data Mart	241	(30)
The ``Classic'' Definition for Data Warehousing	242	(5)
Data Marts	247	(1)
Buy or Build?	247	(1)
Populating a Data Warehouse or Data Mart	248	(3)
Inventory Your Data	248	(1)
Design the New Database	249	(1)
Extract the Data	249	(1)
Data Cleansing and Transformation	249	(1)
Load	250	(1)
Other Methodologies	250	(1)
Microsoft Enters the Fray	251	(4)
Practice Makes Perfect	254	(1)
Creating a Data Mart	255	(13)
Analysis	255	(1)
Business Needs First	256	(1)
``Seeing'' What You Have	256	(3)
Creating the SQL to Load the DM	259	(2)
Following up the Sale	261	(2)
Visually Connecting	263	(5)
Loading the workOrdDM from the CD	268	(1)
Closing the Loop	268	(1)
Summary	269	(2)
Project #5: Creating a Multi-Source Data Mart with Data Transformation Services	271	(38)
Overview	272	(10)
OLTP Design, Star Schema, and Dimensional Design	274	(8)
Project	282	(25)
Business Scenario	282	(2)
Transferring and Transforming the Data	284	(14)
Sales Data	298	(8)
Verifying the Data	306	(1)
Summary	307	(2)
Project #6: Working with OLAP Services	309	(46)
Where Did OLAP Come From?	311	(2)
ROLAP, MOLAP, HOLAP, (SCHMOLAP)	313	(3)
OLAP-Speak	316	(4)
Creating a Multi-Dimensional Database	320	(1)
Investigating an Existing Multi-Dimensional Database	320	(17)
Exploring the Sales Cube	325	(9)
The Warehouse Cube	334	(1)
Run the OLAP Manager Tutorial	335	(2)
OLAP: The Client Side	337	(4)
Excel's Pivot Table	337	(4)
The MDX Sample Application	341	(11)
The MDX Language	343	(9)
Exploring Cubes with Third-Party Tools	352	(4)
Maximal Innovative Intelligence Max 1.0	352	(2)
Cognos NovaView 2.0	354	(2)
Summary	356
Project #7: Implementing Replication	355	(44)
Background	356	(12)
Distributed Data	358	(1)
Introduction to the Replication Process	359	(3)
Replication Components	362	(6)
Log Shipping	368	(1)
About the Projects	368	(1)
Project 7A: Snapshot Replication	368	(26)
Troubleshooting	394	(1)
Summary	395	(4)
Project #8: Getting Started with E-Commerce	399	(58)
The Components of E-Commerce	400	(11)
Domain Name	401	(1)
Site Location	401	(3)
Security	404	(4)
Handling Online Transactions	408	(2)
Security Re-Visited	410	(1)
The Shopping Cart	410	(1)
The First Project	411	(7)
Examining the V-Nursery Site	411	(7)
What Microsoft Site Server Brings to the Table	418	(37)
Site Server Standard Edition	421	(3)
Site Server Commerce Edition	424	(15)
Creating and Configuring A Virtual Root	439	(9)
VisualCommerce Constructor	448	(7)
Summary	455	(2)
Project #9: Encapsulating Business Logic with Stored Procedures and Triggers	457	(32)
What Are Stored Procedures and Triggers?	459	(2)
Central Management of Code	459	(1)
Stored Procedures to Simplify Security	459	(2)
How to Create, Edit, and Delete Stored Procedures	461	(11)
Creating a Stored Procedure	461	(5)
Editing a Stored Procedure	466	(2)
Deleting a Stored Procedure	468	(1)
Debugging Stored Procedures	468	(4)
Triggers	472	(16)
Creating a Trigger	473	(1)
Modifying a Trigger	474	(2)
Deleting a Trigger	476	(1)
Specifications	477	(1)
Business Logic/Pseudocode	478	(2)
Converting the Pseudocode to SQL	480	(8)
Summary	488	(1)
Project #10: Using Visual Basic to Write SQL Applications	489	(90)
Ancient History	490	(1)
Where We Are Today	491	(1)
E-I-E-I-O	491	(5)
The Big Three	496	(39)
The Basics	496	(3)
Drilling Down into DAO	499	(21)
Sample Code to Access SQL Server with RDO	520	(5)
Drilling Down into ADO	525	(10)
Error Handling	535	(1)
ADO Error Handler	535	(1)
The Dynamic SQL Project	535	(35)
Project Specs	536	(15)
Creating the Survey Web Site	551	(1)
Active Server Pages	552	(18)
Creating Server Components with Visual Basic	570	(7)
Server Components	570	(7)
Summary	577	(2)
More Project Ideas	579	(44)
Knowledge Management: Beyond the Hype	579	(6)
KM and Microsoft's Digital Nervous System	581	(1)
Overhead and Power Struggles	581	(1)
Microsoft's Approach to KM	582	(3)
Working with Full-Text Indexing	585	(9)
Installing Full-Text Indexing	587	(2)
Implementing Full-Text Indexing	589	(5)
Data Mining	594	(5)
Data Mining Techniques	597	(1)
Examples of Data Mining	598	(1)
Natural Language Processing	599	(5)
English Query	601	(3)
The Evolving World of Windows CE and Its Devices	604	(11)
What's Windows CE?	605	(1)
CE Services	606	(2)
Getting Your Ducks in a Row	608	(1)
Working with VBCE	609	(6)
Mapping Applications	615	(6)
Mapping Engines	616	(5)
Conclusion	621	(2)
Part Three Appendices	623	(44)
Appendix A SQL Survival Guide	625	(26)
Appendix B More Information	651	(12)
Appendix C What's on the CD-ROM	663	(4)
Index	667