SQL Server 2000
Fast Answers
for DBAs and Developers
Joseph Sack
SQL Server 2000 Fast Answers
for DBAs and Developers
(This book was originally published by Curlingstone in 2003.)
All rights reserved. No part of this work may be reproduced or transmitted in any form
or by any means, electronic or mechanical, including photocopying, recording, or by any
information storage or retrieval system, without the prior written permission of the copyright
owner and the publisher.
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Distributed to the book trade in the United States by Springer-Verlag New York, Inc.,
233 Spring Street, 6th Floor, New York, NY 10013, and outside the United States by
Springer-Verlag GmbH & Co. KG, Tiergartenstr. 17, 69112 Heidelberg, Germany.
In the United States: phone 1-800-SPRINGER, fax 201-348-4505, e-mail

, or visit

.
Outside the United States: fax +49 6221 345229, e-mail

,
or visit

.
For information on translations, please contact Apress directly at 2560 Ninth Street,

Duncan Black Massimo Nardone
Justin Crozier Alexzander Nepomnjashiy
Matthew Moodie Baya Pavliashvili
Indexer Production Manager
Bill Johncocks Zuned Kasu
Proofreader
Figures
Pauline Briggs Rachel Taylor
Pip Wonson
Cover Design
Dawn Chellingworth
Production Coordinators
Natalie O'Donnell Rachel Taylor
Corey Stewart Pip Wonson

About the Author
Joseph Sack is a database administrator and consultant based in
Minneapolis, Minnesota. Since 1997, he has been developing
and supporting SQL Server environments for clients in financial
services, IT consulting, and manufacturing. He is a Microsoft
Certified Database Administrator (MCDBA). Joe has a BA in
Psychology from the University of Minnesota, and so is
somewhat qualified to listen to what troubles you. You can visit
him at

.
Acknowledgments
Thank you to David Hatch, for your advice, encouragement, and patience in hearing me discuss the book
constantly.
Thank you to Barb Sorensen, who gave me my first break at becoming a DBA and developer. Barb sent me to

1.13 How to… Troubleshoot MDAC Installation 22
Ensuring Client Connectivity to Your Server 23
Table of Contents
ii
Upgrading to SQL Server 2000 from SQL Server 6.5 and 7.0 25
Database Compatibility Settings 25
Service Packs and Security Patches 26
1.14 How to… Find the Latest SQL Server Service Packs and Security Patches 27
1.15 How to… Install a Service Pack 27
1.16 How to… Uninstall a SQL Server Service Pack 29
1.17 How to… Install a Security Patch 32
1.18 How to… Roll Back a Security Patch 33
Post-Installation Best Practices 33
Database Migration Techniques 37
1.19 How to… Move Logins 37
1.20 How to… Fix Orphaned Logins 41
1.21 How to… Restore Another SQL Server's Database from Backup
to SQL Server 2000 42
1.22 How to…
……
… Use BCP 50
1.23 How to…
……
… Use BULK INSERT 54
Database Migration – Bringing It All Together 55
1.24 How to… Migrate Databases from SQL Server 6.5 to SQL Server 2000 55
1.25 How to… Migrate Data from Microsoft Access 60
1.26 How to… Migrate Data from Microsoft Excel 64
Chapter 2: Configuring SQL Server 67
2.1 How to… Configure Server Options with Enterprise Manager 67

3.6 How to… View Database Options with DATABASEPROPERTYEX 153
3.7 How to… View User Options for the Current Connection with DBCC
USEROPTIONS 155
3.8 How to… Set User Options in Query Analyzer 156
3.9 How to… Rename a Database with sp_renamedb 160
3.10 How to… Show Database Information with sp_helpdb 160
3.11 How to… Show Log Size and % Space Used for Each Database 161
3.12 How to… Report and Correct Inaccuracies in the sysindexes Table
with DBCC UPDATEUSAGE 162
Table of Contents
iv
3.13 How to… View Space Usage with sp_spaceused 163
3.14 How to… Shrink Data and Log Files in a Database with DBCC
SHRINKDATABASE 164
3.15 How to… Shrink Data Files in a Database using Enterprise Manager 165
3.16 How to… Shrink a Specific Data Or Log File using DBCC SHRINKFILE 167
Filegroups 170
3.17 How to… Add a User-Defined Filegroup 171
3.18 How to… Make a User-Defined Filegroup the Default Filegroup 172
3.19 How to… Make a Filegroup Read-Only 174
3.20 How to… Place a Table into a Filegroup 174
3.21 How to… Change a Filegroup for an Existing Table 177
3.22 How to… Change a Filegroup for an Existing Index 178
3.23 How to… Remove a Filegroup 179
3.24 How to… Create Database Objects using Enterprise Manager 180
Chapter 4: SQL Server Agent and SQL Logs 197
4.1 How to… Configure SQL Server Agent Properties 198
4.2 How to… Use xp_cmdshell 202
4.3 How to… Set up the Proxy Account 203
4.4 How to… Add Alerts 206

4.26 How to… Read the SQL Server Error Log 234
4.27 How to… Setting the Number of SQL Server Error Logs to Keep 235
4.28 How to… Start up a New SQL Server Error Log without Restarting
the SQL Server Service 235
4.29 How to… Change the Logging Status of an Error Message with
sp_altermessage 236
4.30 How to… Log a User-Defined Message with xp_logevent 238
Chapter 5: Transactions, Locking, Blocking, and Deadlocking 241
Understanding Transactions 241
5.1 How to… Use a Transaction Mark 248
Best Practices for using Transactions 249
5.2 How to… Display the Oldest Active Transaction with DBCC OPENTRAN 249
Table of Contents
vi
5.3 How to… Use SET TRANSACTION ISOLATION LEVEL 251
5.4 How to… Use SET CURSOR_CLOSE_ON_COMMIT 252
5.5 How to… Use SET REMOTE_PROC_TRANSACTIONS 253
Lock Types, Compatibility, Isolation Levels, and Hints 254
5.6 How to… View Locking Activity in Enterprise Manager 262
5.7 How to… Use sp_who 263
5.8 How to… Use DBCC INPUTBUFFER and DBCC OUTPUTBUFFER 265
5.9 How to… Use fn_get_sql 266
5.10 How to… Use SET LOCK_TIMEOUT 266
Blocking and Deadlocking Defined 267
Why Blocking and Deadlocking Happen 268
5.11 How to… Identify and Resolve Blocking 268
5.12 How to… Identify Deadlocks with Trace Flag 1204 271
5.13 How to… Enable Trace Flags at SQL Server Startup 273
5.14 How to… SET DEADLOCK_PRIORITY 274
5.15 How to… Capture Deadlock Activity using SQL Profiler 275

Corrupted Binary Files 323
6.17 How to… Reset a Suspect Database with sp_resetstatus 324
6.18 How to… Rebuild the Registry 326
6.19 How to… Start SQL Server in Single-User Mode 326
6.20 How to… Set Up a Database Maintenance Plan 326
6.21 How to… Investigate Maintenance Plan Failures 336
6.22 How to… Use the xp_sqlmaint or SQLMaint Utility 337
6.23 How to… Check Consistency of the Disk Space Allocation
Structures with DBCC CHECKALLOC 340
6.24 How to… Check System Table Consistency with DBCC
CHECKCATALOG 344
6.25 How to… Check Table Integrity with DBCC CHECKCONSTRAINTS 344
6.26 How to… Check Allocation and Structural Integrity of All Database
Objects with DBCC CHECKDB 347
6.27 How to… Check data, index, text, ntext, and image Pages for
Tables or Indexed Views Using DBCC CHECKTABLE 350
Table of Contents
viii
6.28 How to… Check Allocation and Structural Integrity of All Tables in
a Filegroup using DBCC CHECKFILEGROUP 352
6.29 How to… Disable Parallelism in DBCC CHECKDB, DBCC
CHECKFILEGROUP, and DBCC CHECKTABLE 354
6.30 How to… Output DBCC Data in Tabular Form using WITH
TABLERESULTS 355
Index Fragmentation 356
6.31 How to… Rebuild Indexes with DBCC DBREINDEX 356
6.32 How to… Defragment Indexes with DBCC INDEXDEFRAG 358
6.33 How to… Defragment Heaps 360
6.34 How to… Reclaim Space after Dropping Variable Length or Text
Columns with DBCC CLEANTABLE 361

7.17 How to... Display Permissions for Fixed Server Roles with
sp_srvrolepermission 389
7.18 How to... List Members of a Fixed Server Role using
sp_helpsrvrolemember 390
7.19 How to... Return a List of Fixed Database Roles with
sp_helpdbfixedrole 391
7.20 How to... Display Fixed Database Role Permissions 392
SQL Server Permission Types 392
7.21 How to... GRANT, REVOKE, or DENY Statement and Object Permissions 394
7.22 How to... Report User Permissions and Statement Permissions
using sp_helprotect 402
7.23 How to... Audit Security with SQL Profiler 406
7.24 How to... Audit Logins at the OS Level 411
7.25 How to... Monitor Updates with Triggers 412
Chapter 8: Replication 419
Replication Topology 420
Replication Types 421
Replication Configuration Methods 426
Data Type Considerations 426
SQL Server Version Compatibility 427
Pre-Replication Checklist 428
8.1 How to… Configure Snapshot Replication 429
8.2 How to… Create a Transactional Replication Publication 440
8.3 How to… Create a Merge Replication Publication 448
Table of Contents
x
8.4 How to… Push a Subscription 456
8.5 How to… Create a Pull Subscription 460
8.6 How to… Transform Data Within Your Publication 464
8.7 How to… Use Dynamic Filters and Dynamic Snapshots for a Merge

sp_column_privileges_ex 541
9.8 How to... List Linked Servers Defined on Your Local Server 542
9.9 How to… Troubleshoot Linked Servers and Distributed Queries 543
Chapter 10: Failover Clustering 549
Cluster Meta Data 553
Cluster Administrator 553
Pre-Installation Checklist for SQL Server Failover Clustering 554
10.1 How to… Install a SQL Server 2000 Virtual Server 555
10.2 How to… Install a Service Pack for a SQL Server 2000 Virtual Server 557
10.3 How to… Implement Post-Installation Steps 558
Adding Additional Disk Resources 559
Other Post-Installation Configurations to Monitor 559
10.4 How to… Troubleshoot a Failed SQL Server Virtual Server 560
10.5 How to… Uninstall a SQL Server 2000 Virtual Server 560
Chapter 11: DTS 563
11.1 How to... Use DTS Designer 564
11.2 How to... Add and Configure a Connection Object 565
11.3 How to... Add a New Task 568
11.4 How to... Configure the File Transfer Protocol Task 568
11.5 How to... Configure the ActiveX Script Task 570
11.6 How to... Configure the Transform Data Task 572
Configuring the Execute Process Task 587
11.7 How to... Configure the Execute SQL Task 588
11.8 How to... Configure the Data Driven Query Task 589
11.9 How to... Configure the Copy SQL Server Objects Task 594
11.10 How to... Configure the Send Mail Task 595
11.11 How to... Configure the Bulk Insert Task 596
Table of Contents
xii
11.12 How to... Configure the Execute Package Task 597

13.5 How to… Use SQLDIAG 659
13.6 How to… Check the Port Number of a Named Instance using the
Server Network Utility 661
13.7 How to… Create a Client-Side Alias using the Client Network Utility 662
13.8 How to… Configure Full-Text Indexing 663
13.9 How to… Use Full-Text Index Stored Procedures 668
13.10 How to… Query a Full-Text Index 672
13.11 How to… Create Custom Messages 677
Chapter 14: DDL 681
14.1 How to… Create a Table 681
14.2 How to… Create a Table with a Primary Key 684
14.3 How to… Create a Unique Constraint 686
14.4 How to… Create a Computed Column 686
14.5 How to… Place a Table on a Filegroup 688
14.6 How to… Place Text or Images on a Separate Filegroup 688
14.7 How to… Create a Table with a Foreign Key Reference 689
14.8 How to... Set Recursive Foreign Key References 690
14.9 How to... Create a Cascading Change with ON DELETE 691
14.10 How to... Create a Cascading Change with ON UPDATE 691
14.11 How to… Use CHECK Constraints 692
14.12 How to… Use the DEFAULT Property During Table Creation 693
14.13 How to… Use the IDENTITY Property During Table Creation 694
14.14 How to… Use DBCC CHECKIDENT to View and Correct IDENTITY
Seed Values 697
14.15 How to… Use ROWGUIDCOL 698
14.16 How to… Specify Column Collation 699
14.17 How to… Add a Column to an Existing Table 699
14.18 How to… Drop a Column and Constraints from a Table 700
14.19 How to… Change a Column Definition 700
Table of Contents

14.44 How to… Print the Text of the Stored Procedure 723
Table of Contents
xv
14.45 How to… Force a Flush of the Procedure Cache 723
14.46 How to… Add an Extended Stored Procedure 724
14.47 How to… Show the Name of the DLL Associated with an Extended
Stored Procedure 724
14.48 How to… Unload the Extended Stored Procedure DLL from Memory 725
14.49 How to… Remove an Extended Stored Procedure 725
User-Defined Functions 725
14.50 How to… Create Scalar User-Defined Functions 727
14.51 How to… Create Inline User-Defined Functions 728
14.52 How to… Create Multistatement User-Defined Functions 729
14.53 How to… Modify User-Defined Functions 731
14.54 How to… Drop User-Defined Functions 731
Indexes 731
14.55 How to… Create an Index 733
14.56 How to… Drop an Index 735
14.57 How to… Display Indexes Defined for a Table 736
Triggers 736
14.58 How to… Create a Trigger 737
14.59 How to… Alter a Trigger 740
14.60 How to… Drop a Trigger 740
14.61 How to… Monitor the Nesting Level within a Trigger 740
14.62 How to… Specify the Firing Order of AFTER Triggers 741
14.63 How to… List Triggers for Table 741
14.64 How to… Enable and Disable Table Triggers 742
Working with Database Objects 743
14.65 How to… Change the Name of a User-Created Database Object 743
14.66 How to… Display Information About a Database Object 744

15.23 How to… Use Hints 789
Table of Contents
xvii
Chapter 16: Transact-SQL Techniques 793
16.1 How to… Use Aggregate Functions 793
16.2 How to… Use String Functions 800
16.3 How to… Use Date Functions 811
16.4 How to… Use Display Information with @@ Functions 814
16.5 How to… Use Mathematical Functions 818
16.6 How to… Use Security functions 825
16.7 How to… Use Meta Data Functions 827
16.8 How to… Use System Functions 830
16.9 How to… Use IMAGE, TEXT, and NTEXT Functions 836
16.10 How to… Use Information Schema Views 840
Normalization in a Nutshell 841
16.11 How to… Use CASE 844
BEGIN and END 845
16.12 How to… Use IF…ELSE 846
16.13 How to… Use WHILE, BREAK, and CONTINUE 847
16.14 How to… Use RETURN 849
16.15 How to... Use RAISERROR 850
Transact-SQL Cursors 852
16.16 How to… Create and use Transact- SQL Cursors 852
16.17 How to… Create Extended Properties 857
16.18 How to… Create a Script that Creates a Script 861
16.19 How to… Use sp_executesql 861
16.20 How to… Use String Concatenation 862
Batches 863
16.21 How to… Use EXEC 863
16.22 How to… Bind a Connection to Other Transactions using

18.2 How to… Use sp_makewebtask to Output XML Documents 939
18.3 How to… Use OPENXML 940
Index 945