Cover image for Microsoft SQL server 7.0 programming unleashed
Title:
Microsoft SQL server 7.0 programming unleashed
Author:
Papa, John.
Publication Information:
Indianapolis, Ind. : Sams Pub., [1999]

©1999
Physical Description:
xxii, 946 pages : illustrations ; 24 cm
General Note:
Includes index.
Language:
English
Title Subject:
Subject Term:
ISBN:
9780672312939
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Central Library QA76.9.C55 M54 1999 Adult Non-Fiction Central Closed Stacks
Searching...

On Order

Summary

Summary

"The book presents focused explanations of the core features and complexities of SQL Server 7.0 programming, including advanced scripting techniques, real-world data warehousing, building scalable applications, creating complex stored procedures, and maximizing data integrity and security."--BOOK JACKET.Title Summary field provided by Blackwell North America, Inc. All Rights Reserved


Table of Contents

Introductionp. 1
Why Read This Book?p. 1
What Will I Learn from This Book?p. 2
Conventions Used in This Bookp. 3
Part I Advanced Programming Techniquesp. 5
1 Taking Advantage of the Toolsp. 7
Microsoft Management Consolep. 8
Managing SQL Server with Enterprise Managerp. 9
Querying SQL Server with Query Analyzerp. 15
Summaryp. 19
2 Beyond the Basics of Data Manipulation Languagep. 21
Naming Conventionsp. 22
Using Subqueries and Inline Viewsp. 27
Outer Joins, Cross Joins, and Self-Joinsp. 37
More Advanced DELETE and UPDATE Statementsp. 45
Aggregate Functionsp. 49
The GROUP BY and HAVING Clausesp. 53
Unionsp. 54
Summaryp. 54
3 Optimizing Queriesp. 57
A Closer Look at Index Structuresp. 58
Indexes, Transactions, and Data Manipulationp. 73
How Transactions and Indexes Affect Lockingp. 75
Optimizing the Query Optimizerp. 79
Using the SQL Server Profilerp. 84
Summaryp. 89
4 Advanced Transact-SQL Statementsp. 91
Using CAST and CONVERTp. 92
Printing Informationp. 93
The sysmessages System Tablep. 96
Purposely Causing Errorsp. 100
The CASE Expressionp. 102
Distributed Transactionsp. 109
Summaryp. 121
5 Effective Use of Built-in Functionsp. 123
Making Computations Work with Mathematical Functionsp. 124
Calculating Dates with Date Functionsp. 132
Using String Functions to Manipulate Stringsp. 137
Using Text and Image Functions in SQLp. 144
Using SQL Functions to Retrieve System Informationp. 145
Summaryp. 150
6 Using Cursorsp. 151
Cursor Declarationp. 152
Rules of Cursor Usep. 155
Opening, Closing, and Moving Through Cursorsp. 156
Some Advanced Cursor Usesp. 161
Using Cursors to Modify Datap. 172
Summaryp. 175
7 Stored Proceduresp. 177
Why Use Procedures?p. 178
System Stored Proceduresp. 178
Extended Stored Proceduresp. 184
Creating Your Own Stored Proceduresp. 186
Integrating Messages into Stored Proceduresp. 191
Advanced Uses of Stored Proceduresp. 193
Summaryp. 203
8 Advanced String Manipulation and Bitwise Operatorsp. 205
Introductionp. 206
Advanced String Manipulation to Spell Currencyp. 206
Binary Operatorsp. 219
Summaryp. 225
9 Dynamic Executionp. 227
What Is Dynamic Execution?p. 228
Creating the Executable Stringp. 230
Using Cursors with Dynamic Executionp. 242
The Pros and Cons of Dynamic Executionp. 245
Summaryp. 249
10 Security Issuesp. 251
Implementing an Authentication Modep. 252
Assigning Logins to Users and Rolesp. 258
Assigning Permissions to Users and Rolesp. 264
Planning Securityp. 268
Managing Application Securityp. 269
Summaryp. 271
11 Specialized Triggersp. 273
Nesting, Recursion, and Trigger Basicsp. 274
How to Use Triggers to Enforce Business Rulesp. 284
Auditing Triggersp. 289
Storing System Informationp. 290
Referential Integrity with Triggersp. 294
Cascade Delete Triggersp. 297
Cascade Update Triggersp. 300
Summaryp. 303
12 Updating Table Indexes and Statisticsp. 305
Index Performance and Fine-Tuningp. 306
Creating a Utility to Optimize Table Indexesp. 309
Verifying When the Last Optimization Took Placep. 311
Running Tasks Automaticallyp. 312
Using DBCC Statements to Monitor Your Databasep. 317
Summaryp. 331
13 Crosstabulationp. 333
A Description of Crosstabulationp. 334
Verifying Object Existencep. 338
Checking Column Securityp. 344
Generating the Column Header Listp. 347
Generating Cross-tabbed Reportsp. 352
Summaryp. 362
Part II SQL Server - Essential Informationp. 363
14 Writing Effective Codep. 365
Simple, Effective Code Is Elegant Codep. 366
Modularity of Stored Proceduresp. 388
Summaryp. 389
15 Using Multiple Tiers and Client/Server Architecturep. 391
Understanding Client/Server Architecturesp. 392
Evolution of Client/Server Computing Architecturesp. 394
Web Browswers, Web Servers, and the Client/Server Modelp. 400
Microsoft's Three-Tier Services Modelp. 404
N-Tier Applications Using RAD Development Toolsp. 406
Scalability and SQL Serverp. 413
Summaryp. 415
16 Replicationp. 417
Replication Concepts for Programmersp. 419
Advanced Replicationp. 429
Summaryp. 440
Part III Migrating to SQL Server: Data Conversion and Integrationp. 441
17 Outgrowing Accessp. 443
Determining the Need to Move from Access to SQL Serverp. 444
Making the Move from Access to SQL Serverp. 453
Summaryp. 461
18 Making the Switch from Sybase SQL Serverp. 463
Starting with 7.0, the Roads Divergep. 464
Looking Forwardp. 464
The Remaining Similaritiesp. 465
Differences Between Microsoft SQL 7.0 and Sybase Adaptive Serverp. 466
Summaryp. 483
19 Optimizing ODBC Using Visual C++p. 485
ODBC Architecturep. 486
Database Abstractionp. 488
Why Use ODBC?p. 488
Security Issues in ODBCp. 490
Methods of Connecting with ODBCp. 492
Call-Level Interfacep. 505
Simplifying ODBC with Microsoft Foundation Classesp. 549
20 Connecting to SQL Server from Visual Basicp. 563
Developing Efficient Visual Basic Applications for SQL Serverp. 564
Using Open Database Connectivity (ODBC)p. 571
Data Access Objectsp. 581
Remote Data Objects (RDO)p. 592
Summaryp. 604
21 Using SQL-DMO via Visual Basic to Manage SQL Serverp. 605
DMO Conceptsp. 606
Implementing SQL-DMO Objectsp. 607
Using SQL-DMO to Manage Databasesp. 609
Using SQL-DMO to Manage Serversp. 610
Exploring the SQL-DMO Object Modelp. 611
Common Practical Uses of SQL-DMOp. 626
Building Database Management Toolsp. 631
Summaryp. 632
22 Automating Web Publication of SQL Server Datap. 633
Components of the Web Assistantp. 634
Using the Web Assistant Wizardp. 635
Using xp_makewebtask and sp_makewebtaskp. 636
Refining Web Pages with sp_makewebtask and xp_makewebtaskp. 638
Automating Web Publishing with Job-Related Parametersp. 654
Summaryp. 660
23 SQL Server, ADO, and the Webp. 661
The Evolution of Data Accessp. 662
The ADO Object Modelp. 662
The Connection Objectp. 664
Using Stored Procedures with the Command Objectp. 670
The Recordset Objectp. 672
RDS: Moving ADO to the Browserp. 681
Summaryp. 684
24 Microsoft's COM/DCOMp. 685
DCOM Architecturep. 686
Common Techniquesp. 698
Summaryp. 710
25 Why the Future Is Microsoft Transaction Serverp. 713
Using Three-Tiered Architecture with the Internetp. 714
Understanding MTSp. 716
Programming MTS Transactionsp. 718
Deploying MTS Componentsp. 721
The Importance of Stateless Environmentsp. 722
Maximizing Performance with MTSp. 728
26 Other Development Clients and SQL Server Connectivityp. 731
Delphi's Database Modelsp. 732
Programming Databases in Delphip. 732
PowerBuilder and Microsoft SQL Serverp. 741
Delphi Versus PowerBuilder: Which Should I Use?p. 760
27 Integrating SQL with Other Microsoft Productsp. 761
Using Access as a Front-End to SQL Server 7.0p. 762
Using Excel with SQL Server 7p. 769
Using SQL Server with Internet Information Server and Internet Explorerp. 771
Integration with Microsoft Transaction Serverp. 772
Summaryp. 779
28 Scheduling Jobs and Using ActiveX Servers to Implement Business Solutionsp. 781
Using the SQL Server Agentp. 782
Implementing Jobsp. 783
T-SQL Jobsp. 784
Running ActivX Servers Through VBScript Jobsp. 789
Putting It All Togetherp. 791
Summaryp. 792
29 Programming a Line-of-Business Solutionp. 793
Infrastructurep. 794
Source Code Managementp. 794
Source Code Managementp. 794
Database Requirementsp. 795
Design and Developmentp. 796
Programming for Performancep. 798
Testing the Database Applicationp. 801
Modifying Third-Party SQL Server Applicationsp. 802
Summaryp. 803
30 Data Warehousing and Online Transaction Processing (OLTP)p. 805
SQL Server as a Data Warehousep. 806
Implementing a Data Warehousep. 807
How to Scrub (Prepare) Data for Warehousesp. 819
Large Queries and Performance Optimizationp. 832
Warehouse Maintenance Tasksp. 837
SQL Server and OLTP Systemsp. 838
Optimizing SQL Server as an OLTP Systemp. 851
Monitoring Performancep. 853
Summaryp. 854
Appendixesp. 855
A The System Tables of the Master Databasep. 857
Login Tablesp. 858
Database Tablesp. 861
Systemwide Tablesp. 866
Special Tablesp. 873
B The System Tables of All Databasesp. 875
Object Tablesp. 876
User, Group, and Security Tablesp. 888
Replication Tablesp. 891
C Common Error Messagesp. 899
Error Message #103: Severity Level 15p. 900
Error Message #105: Severity Level 15p. 900
Error Message #109: Severity Level 15p. 900
Error Message #110: Severity Level 15p. 901
Error Message #113: Severity Level 15p. 901
Error Message #137: Severity Level 15p. 901
Error Message #156: Severity Level 15p. 902
Error Message #170: Severity Level 15p. 902
Error Message #174: Severity Level 15p. 902
Error Message #193: Severity Level 15p. 902
Error Message #201: Severity Level 16p. 903
Error Message #206: Severity Level 16p. 903
Error Message #207: Severity Level 16p. 903
Error Message #208: Severity Level 16p. 903
Error Message #229: Severity Level 14p. 904
Error Message #230: Severity Level 14p. 904
Error Message #232: Severity Level 16p. 904
Error Message #235: Severity Level 16p. 904
Error Message #241: Severity Level 16p. 905
Error Message #243: Severity Level 16p. 905
Error Message #257: Severity Level 16p. 905
Error Message #259: Severity Level 16p. 905
Error Message #268: Severity Level 16p. 906
Error Message #270: Severity Level 16p. 906
Error Message #284: Severity Level 16p. 906
Error Message #308: Severity Level 16p. 907
Error Message #511: Severity Level 16p. 907
Error Message #515: Severity Level 16p. 907
Error Message #544: Severity Level 16p. 908
Error Message #601: Severity Level 12p. 908
Error Message #602: Severity Level 21p. 908
Error Message #605: Severity Level 21p. 909
Error Message #624: Severity Level 21p. 909
Error Message #625: Severity Level 21p. 909
Error Message #701: Severity Level 19p. 910
Error Message #1023: Severity Level 15p. 910
Error Message #1204: Severity Level 19p. 910
Error Message #1205: Severity Level 13p. 911
Error Message #1505: Severity Level 14p. 911
Error Message #1508: Severity Level 14p. 911
Error Message #1510: Severity Level 17p. 912
Error Message #1530: Severity Level 16p. 912
Error Message #1702: Severity Level 16p. 912
Error Message #1803: Severity Level 17p. 913
Error Message #1814: Severity Level 10p. 913
Error Message #1902: Severity Level 16p. 913
Error Message #1903: Severity Level 16p. 913
Error Message #1904: Severity Level 16p. 914
Error Message #2601: Severity Level 14p. 914
Error Message #2714: Severity Level 16p. 914
Error Message #2715: Severity Level 16p. 914
Error Message #2729: Severity Level 16p. 915
Error Message #2750: Severity Level 16p. 915
Error Message #2751: Severity Level 16p. 915
Error Message #2812: Severity Level 16p. 915
Error Message #3101: Severity Level 16p. 916
Error Message #3604: Severity Level 10p. 916
Error Message #5701: Severity Level 10p. 916
Error Message #5808: Severity Level 16p. 916
Error Message #8101: Severity Level 16p. 917
Error Message #8102: Severity Level 16p. 917
Error Message #8106: Severity Level 16p. 917
Error Message #8146: Severity Level 16p. 917
Indexp. 919

Google Preview