SQL Server 2000 - stored procedures and XML programming

Discover the key concepts, techniques, tips, and tricks professional developers need to know in order to take full advantage of stored procedures in the SQL Server environment. SQL Server 2000 Stored Procedure & XML Programming, Second Edition teaches you to manage SQL Server resources using Enterprise Manager, Query Analyzer, SQL Profiler, Visual SourceSafe, Visual Basic, and Visual Studio .NET. Learn basic and advanced XML with special focus on features that are in use in Microsoft database and development tools. Develop dynamic, Web-based applications using SQL Server's XML capabilities. This helpful resource contains simple explanations for developers who are just starting to work with stored procedures and XML, as well as advanced recommendations and techniques for experienced developers.Essential Skills for Database Professionals\* Build user-defined, system, temporary, global temporary, extended, remote, and nested stored procedures \* Write code to interact with COM objects, DTS packages, executables, mail, the Registry, jobs, full-text indexes, and security \* Manage source code and deploy databases using T-SQL scripts or Visual Studio .NET tools \* Learn the finer points of using identity, timestamp, dynamically constructed queries, temporary tables, and more \* Master the latest features of XML for SQL Web Releases-XML BulkLoad, UpdateGrams, DiffGrams, SOAP, Web Services, XML Views, and OPENXML \* Access and modify databases through URL, POST, XML template, XPath queries, mapped XML schemas, and Web Services \* Develop .NET applications for SQL Server 2000 databases using SQLXML managed classes \* Sample scripts, .NET projects, databases, XML templates, and development and deployment tools available online Knowledge iS Power SQL Server 2000 Stored Procedure & XML Programming 1 Cover 1 Contents at a Glance 6 Contents 8 Chapter 1 Introduction 18 Who Should Read This Book 20 What You Will Find in This Book 20 Requirements 22 Stored Procedure Programming Requirements 22 XML Programming Requirements 23 Sample Database and Other Resources 23 Sample Database Installation 24 Purpose and Design of the Sample Database 26 Database Diagram 27 Chapter 2 The SQL Server Environment 36 SQL Server 2000 Tools 37 Service Manager 38 Query Analyzer 39 Enterprise Manager 41 DTS and Import/Export Data 43 osql and isql 44 SQL Server Profiler 44 Client Network Utility 45 Server Network Utility 46 The Help Subsystem and SQL Server Books Online 46 SQL Server on the Web 47 Basic Operations with Stored Procedures 48 What Are Stored Procedures? 48 Execution of Stored Procedures from Query Analyzer 49 Managing Stored Procedures from Enterprise Manager 53 Editing Stored Procedures in Enterprise Manager 58 Editing Stored Procedures in Query Analyzer 58 Syntax Errors 60 Naming Conventions 61 Why Bother? 62 Naming Objects and Variables 63 Suggested Convention 66 Chapter 3 Stored Procedure Design Concepts 70 Anatomy of a Stored Procedure 71 Composition 71 Functionality 74 Syntax 81 Types of Stored Procedures 82 Compilation 84 The Compilation and Execution Process 84 Reuse of Execution Plans 85 Recompiling Stored Procedures 88 Storing Stored Procedures 89 Managing Stored Procedures 91 Listing Stored Procedures 92 Viewing Code of Stored Procedures 94 Renaming Stored Procedures 96 Deleting Stored Procedures 96 Listing Dependent and Depending Objects 97 The Role of Stored Procedures in the Development of Database Applications 99 Enforcement of Data Integrity 99 Consistent Implementation of Complex Business Rules and Constraints 100 Modular Design 100 Maintainability 100 Reduced Network Traffic 101 Faster Execution 101 Enforcement of Security 101 Chapter 4 Basic Transact-SQL Programming Constructs 102 T-SQL Identifiers 103 Database Object Qualifiers 105 Data Types 106 Character Strings 106 Unicode Character Strings 107 Date and Time Data Types 108 Integer Numbers 109 Approximate Numbers 110 Exact Numbers 111 Monetary Data Types 111 Binary Data Types 112 Special Data Types 112 Variables 116 Local Variables 116 Global Variables 120 Table Variables 123 Flow-Control Statements 124 Comments 125 Statement Blocks: Begin...End 128 Conditional Execution: The If Statement 129 Looping: The While Statement 134 Unconditional Execution: The GoTo Statement 136 Scheduled Execution: The WaitFor Statement 138 Cursors 138 Transact-SQL Cursors 139 Cursor-Related Statements and Functions 143 Problems with Cursors 144 The Justified Uses of Cursors 145 Chapter 5 Functions 148 Using Functions 149 In Selection and Assignment 149 As Part of the Selection Criteria 150 In Expressions 150 As Check and Default Constraints 151 Instead of Tables 151 Types of Functions 152 Scalar Functions 153 Aggregate Functions 181 Rowset Functions 182 Chapter 6 Composite Transact-SQL Constructs: Batches, Scripts, and Transactions 186 Batches 187 Using Batches 189 Batches and Errors 189 DDL Batches 193 Self-Sufficient Content 193 Scripts 196 Database Scripting 197 Transactions 199 Autocommit Transactions 200 Explicit Transactions 201 Implicit Transactions 203 Transaction Processing Architecture 203 Nested Transactions 205 Named Transactions 209 Savepoints 210 Locking 213 Distributed Transactions 217 Typical Locking Problems 220 Chapter 7 Debugging and Error Handling 224 Debugging 225 What Is a "Bug"? 225 The Debugging Process 226 Debugging Tools and Techniques 229 SQL Profiler 244 Typical Errors 248 Error Handling 252 Raiserror 253 Using Error Handling 255 Why Bother? 255 Tactics of Error Handling 256 A Coherent Error Handling Methodology 260 Xact_Abort 265 Another Coherent Error Handling Methodology 269 Chapter 8 Special Types of Procedures 276 Types of Stored Procedures 277 User-Defined Stored Procedures 277 System Stored Procedures 277 Extended Stored Procedures 279 Temporary Stored Procedures 286 Global Temporary Stored Procedures 287 Remote Stored Procedures 288 User-Defined Functions 288 Design of User-Defined Functions 288 Table-Valued User-Defined Functions 293 Inline Table-Valued User-Defined Functions 296 Managing User-Defined Functions in Enterprise Manager 298 Triggers 300 Physical Design of After Triggers 301 Handling Changes on Multiple Records 308 Nested and Recursive Triggers 310 Trigger Restrictions 310 Instead-of Triggers 311 Triggers on Views 313 Trigger Order of Execution 315 Managing Triggers 315 Trigger Design Recommendations 319 Transaction Management in Triggers 321 Using Triggers 322 Views 328 Design of Standard SQL Views 328 Dynamic Views 334 INFORMATION_SCHEMA Views 334 Indexed Views 335 Partitioned Views 338 Using SQL Views 353 Chapter 9 Advanced Stored Procedure Programming 358 Dynamically Constructed Queries 359 Executing a String 359 Query By Form 361 Data Script Generator 364 Using the sp_executesql Stored Procedure 368 Security Implications 370 Optimistic Locking Using timestamp Values 373 timestamp 374 TSEqual() Function 376 timestamp Conversion 378 Full-Text Search and Indexes 380 Nested Stored Procedures 382 Using Temporary Tables to Pass a Recordset to a Nested Stored Procedure 382 Using a Cursor to Pass a Recordset to a Nested Stored Procedure 385 How to Process the Result Set of a Stored Procedure 388 Using Identity Values 395 A Standard Problem and Solution 395 Identity Values and Triggers 396 Last Identity Value in the Scope 397 GUIDs 398 A While Loop with Min() or Max() Functions 400 Looping with sp_MSForEachTable and sp_MSForEachDb 402 Property Management 403 Chapter 10 Interaction with the SQL Server Environment 406 Execution of OLE Automation/COM Objects 407 Data Type Conversion 410 Running Programs 411 Running Windows Script Files 412 Running/Looping Through DTS Packages 412 Interacting with the NT Registry 415 xp_regread 415 xp_regwrite 416 Jobs 417 Administration of Jobs 417 An Alternative to Job Scheduler 421 Stored Procedures for Maintaining Jobs 422 Operators and Alerts 423 SQL Server and the Web 424 Web Assistant 424 Web Task Stored Procedures 428 Web Page Templates 430 E-Mail 432 Extended Stored Procedures for Working with E-Mail 433 Security 436 Security Architecture 436 Implementing Security 439 Synchronization of Login and Usernames 447 Managing Application Security Using Stored Procedures, User-Defined Functions, and Views 449 Managing Application Security Using a Proxy User 451 Managing Application Security Using Application Roles 453 Chapter 11 Source Code Management and Database Deployment 454 The Concept of Source Code Management 455 Introduction to Microsoft Visual SourceSafe 456 Administering the Visual SourceSafe Database 457 Adding Database Objects to Visual SourceSafe in Visual StudioNET 457 Managing Create Scripts in Visual StudioNET 460 Visual SourceSafe Explorer 463 Adding Database Objects to Visual SourceSafe: Traditional Approach 468 Database Deployment 470 Deployment of a Complete Database: Traditional Approach 470 Deployment of Individual Objects 472 Chapter 12 Stored Procedures for Web Search Engines 484 Characteristics of the Environment 485 A Simple Solution... 485 ...and Its Disadvantages 487 Available Solutions 488 Result Splitting 489 Quick Queries 498 Advanced Queries 503 Chapter 13 Introduction to XML for Database Developers 506 XML (R)evolution 507 Introduction to XML 509 Introduction to Markup Languages 509 Building Blocks of Markup Languages 509 XML Elements and Attributes 510 Processing Instructions 511 Document Type Definition 512 XML Comments and CDATA sections 513 Character and Entity References 514 XML Namespaces 515 Structure of XML Documents 516 XML Parsers and DOM 517 XML Document Quality 518 XML Schema and XML Schemas 518 XML–Data Reduced (XDR) Schema 519 XML Schema (XSD) 524 Linking and Querying in XML 535 XPointer 536 XPath 537 Transforming XML 539 XSL 540 XSLT 541 Why XML? 541 Exchange of Information Between Organizations 541 Information Publishing 543 Chapter 14 Publishing Information Using SQLXML 544 For XML Clause 545 Auto Mode 546 Aggregate Functions 551 Computed Columns 551 The Elements Option 551 The XMLData Option 552 The BINARY Base64 Option 553 Raw Mode 554 Explicit Mode 555 Publishing Database Information Using HTTP 564 Configuring Database Access Through HTTP 565 Accessing Database Information Using a URL 569 Troubleshooting Virtual Directories 571 Executing a Stored Procedure Through HTTP 573 Accessing Database Information Using Templates 574 POSTing Queries to the Server 584 XML Views Based on Annotated XDR Schemas 588 XML Views Based on Annotated XSD Schemas 599 Programmatic Database Access 605 Retrieving XML Data Using SQLXML Managed Classes 605 Retrieving XML Data Using ADO.NET 614 Using SqlCommand 614 Using DataSet Objects 615 Client-Side XML Processing 616 Using URL Queries 617 Using Templates 617 Using SQLXML Managed Classes 618 Processing of Queries with the For XML Clause 619 Chapter 15 Modifying Databases Using SQLXML 622 OpenXML() 623 Document Preparation 623 Closing the Document 624 Retrieving the XML Information 625 Metaproperties in OpenXML() 628 What if an XML Document Is Longer Than 8000 Characters? 630 UpdateGrams 632 Executing UpdateGrams 634 Element-centric vs. Attribute-centric UpdateGram 636 UpdateGrams with Parameters 636 Setting Parameters to Null 638 Returning Identifier Values 639 Special Characters 641 UpdateGrams Behind the Scene 641 Optimistic Locking with UpdateGrams 642 Multiple Records and Multiple Tables in a Single UpdateGram 644 DiffGrams 645 Using DiffGrams to Insert Data 646 Using DiffGrams to Update Data 648 Using DiffGrams to Delete Data 650 Processing Multiple Records Using DiffGrams 651 DiffGrams Behind the Scene 651 Executing DiffGrams Programmatically Using SqlXmlCommand 653 Executing DiffGrams Using URLs 653 Generating DiffGrams After DataSet Change 654 Debugging DiffGrams 655 SQLXML BulkLoad 656 Executing SQLXML BulkLoad from aNET Application 656 Error Log File 657 Executing BulkLoad from DTS (Using VBScript) 658 Schema Generation 659 BulkLoad Transactions 660 Data Integrity 660 Table Lock 661 Using SQLXML BulkLoad 661 Mapping Schema 661 XML Web Services 664 SOAP Messages and XML Web Services Architecture 664 Using SQLXML to Create XML Web Services 665 CreatingNET SOAP Clients 667 Appendix T-SQL and XML Data Types in SQL Server 2000 676 Index 686

Get thorough coverage of SQL Server 2000—including extended XML capabilities and VS.NET updates with this expertly written resource. Learn to harness the full benefits of modular programming, and achieve faster execution and a reduction of network traffic. Included are a sample database and an end-to-end solution for a Web search engine.