Archive for April, 2006

Useful SQL 2000 Links – Bookmarked April 25th, 2006

Vinod Kumar

Please find below a consolidated list of some SQL Server 2000 links. I am sure you will not be able to accomodate all the links as part of favourites, but surely can use this page as a master link to all the interesting articles for reference.

269882 HOWTO: Use ADO to Connect to a SQL Server That Is Behind a Firewall 
183059 PRB: ODBC Syntax Required for Retrieving Parameter Information
229564 PRB: SQL Application Role Errors with OLE DB Resource Pooling
262447 HOWTO: Persist/Load OLE DB Rowset to/from XML with ATL OLE DB Consumer
247931 INF: Authentication Methods for Connections to SQL Server in Active
190991 HOWTO: Trap for ADO Connection Errors Using WithEvents
191572 INFO: Connection Pool Management by ADO Objects Called From ASP
237844 HOWTO: Enable ODBC Connection Pooling in a Visual Basic ADO Application
307002 PRB: ASP/ODBC/SQL Server Error 0x80040E4D “Login Failed for User
325338 OLE DB Provider for SQL Server Does Not Cancel a Batch Statement When
306216 PRB: Error “Unable to Load Communication Module” in ASP/ADO/SQL Server
253240 PRB: Errors Not Returned When Stored Procedure Statement Fails

Analysis Services
279489 INF: How to Connect to Analysis Server 2000 By Using HTTP Connection
294287 INF: How to Automate an Archive or Backup of OLAP Database
840667 PRB: Unexpected errors with ADO and ADO MD in .NET Framework applications
301901 INF: TCP Ports Used by OLAP Services when Connecting Through a Firewall
308023 HOW TO: Cluster SQL Server 2000 Analysis Services in Windows 2000
323048 INF: Using Data Definition Language with SQL Server 2000 Analysis
322706 INF: Microsoft Corp. SQL Server Analysis Services (White Paper)
325484 Support WebCast: Introduction to XML for Analysis Services
327396 Support WebCast: Performance Tuning Analysis Services
324961 Support WebCast: Microsoft SQL Server 2000 Analysis Services: How to
828343 Support WebCast: Dynamic Dimension Security in Microsoft SQL Server 2000
303408 INF: The Impact of Using Changing Dimensions
306990 INF: When to Create a Virtual Dimension Instead of a Physical Dimension
322596 INF: Creating Large-Scale, Highly Available OLAP Sites (White Paper)
322719 INF: Powerful and Accessible Analysis for Business (White Paper)
231951 INF: Permissions That You Must Have to Administer an OLAP Server
306408 INF: Use of Multiple Hierarchies Versus the Creation of Separate

224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
832524 SQL Server technical bulletin – How to resolve deadlocks
263889 INF: SQL Blocking Due to [[COMPILE]] Locks
75722 INF: Reducing Lock Contention in SQL Server
323630 INF: Resolving Blocking Problems That Are Caused by Lock Escalation in
295108 INF: Incomplete Transaction May Hold Large Number of Locks and Cause
317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
324885 Support WebCast: Microsoft SQL Server: Rapid Blocker Script Analysis
215520 INFO: COM+ and MTS Always Use SERIALIZABLE Transaction Isolation Level
48712 INF: Handling Timeouts Correctly in DB-Library

325229 Support WebCast: Microsoft SQL Server 2000 Windows CE Edition Overview
273580 HOWTO: Look Up Error Codes Related to SQL Server CE
814852 INF: How to Enable Encryption Support for SQL Server CE Databases
320785 HOW TO: Create a Database, a Table, and Run a Query (SQL Server CE
279552 HOWTO: Delete a SQL Server CE Database in an Application
279761 HOWTO: Use the ADOCE 3.1 Seek Method with SQL Server CE
814851 FIX: SQL Server CE Application Fails on Pocket PC 2002 Emulator on
272210 INFO: System Tables in SQL Server CE Database
274112 INFO: SQL Server CE Performance Tips and Efficient Memory Handling
276985 HOWTO: Use SQL Server CE OLE DB Provider-Specific Properties in eVB
325818 PRB: SQL Server CE 2.0 in Visual Studio .NET 2003 Does Not Support
301217 HOW TO: Prebuild a Database in SQL Server CE
814850 INF: Compacting a SQL Server 2000 CE Database by Using Visual Basic .NET
330971 SQL Server 2000 Windows CE Edition 2.0 for Visual Studio .NET 2003
304058 HOWTO: Perform Replication by Using SSCERelay and ActiveSync
CE Main Page <blocked::blocked::> 
SQL Server CE: New Version Lets You Store and Update Data on Handheld Devices
Troubleshooting Microsoft SQL Server 2000 Windows CE Edition Connectivity Issues
Pocket PC 2002 SDK
323143 FIX: SQL Server CE Is Missing Oledb.lib File for eMbedded Visual C++ 4.0

SQL Server Books Online (BOL)
SQL Server 2000 Clustering Whitepaper,
815431 PRB: Installation of a Named Instance of SQL Server 2000 Virtual Server
327322 PRB: SQL Server 2000 Enterprise Manager Polling May Cause ClientToken
325485 WebCast: Microsoft SQL Server 2000 Virtual Server Basic Setup
274446 INF: Upgrade to SQL Server 2000 Failover Solution Recommended for All….
233332 INF: How To Run SQLDIAG On a Clustered/Virtual SQL Server
254321 INF: Clustered SQL Server Do’s, Don’ts, and Basic Warnings
243218 INF: Installation Order for SQL Server 2000 Enterprise Edition
260758 INF: Frequently Asked Questions – SQL Server 2000 – Failover Clustering
319723 INF: Kerberos Support for SQL Virtual Servers on Server Clusters
307336 INF: How to Change a Clustered SQL Server Network Name
239885 INF: How to Change Service Accounts on a SQL Virtual Server
244980 HOWTO: Change the Network IP Addresses on a Virtual SQL Server
319016 HOW TO: Change Domains for a SQL Server 2000 Failover Cluster
304282 PRB: Full Text Resource Fails to Come Online on a Cluster with “Event ID…
270126 PRB: How to Manage Client Connectivity to Both SQL Server 2000 Virtual
298723 BUG: SQL Mail Not Fully Supported for Use in Conjunction with Cluster
815431 PRB: You Cannot Successfully Install a Named Instance of SQL Server 2000
305793 How to Replace a Disk That Is on a Windows 2000 or a Windows 2003 Server
216964 How to Administer a Server Cluster Remotely
168801 How to Turn On Cluster Logging in Microsoft Cluster Server
278007 Available Features in Windows Server 2003 Clusters
258750 Recommended Private “Heartbeat” Configuration on a Cluster Server

812817 Support WebCast: Microsoft SQL Server 2000: Troubleshooting Connectivity
324608 Support WebCast: Troubleshooting Database Connectivity Issues in
270126 PRB: How to Manage Client Connectivity to Both SQL Server 2000 Virtual
138541 HOW TO: Odbcping.exe to Verify ODBC Connectivity to SQL Server
293107 PRB: TCP\IP Port in Use by Another Application
328306 INF: Potential Causes of the “SQL Server Does Not Exist or Access
811889 HOW TO: Troubleshoot the “Cannot Generate SSPI Context” Error Message

Disaster Recovery:
325257 Support WebCast: SQL Server 2000 Database Recovery: Backup and Restore
Designing a Backup and Restore Strategy,
829024 INFO: Microsoft SQL Server 2000 Operations Guide
307775 INF: Disaster Recovery Articles for Microsoft SQL Server
321836 HOW TO: Retrieve a Table or Rows from Database or Transaction Log Backups

269074 INF: How to Run a DTS Package as a Scheduled Job
316043 HOW TO: Log Data Transformation Services Package Execution to a Text File
240221 HOW TO: Handle Errors in Data Transformation Services “Package” and….
221193 HOW TO: Install Data Transformation Services (DTS) Event Handlers in….
241249 HOW TO: Obtain a List of DTS Packages
249236 HOW TO: Return Multiple Columns in a DTS Lookup Query
315661 HOW TO: Run a SQL Server Data Transformation Services Package from….
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation
323685 HOW TO: Troubleshoot DTS Packages That You Run from Visual Basic
321525 HOW TO: Use DTS Package Events in Visual Basic .NET
242543 INF: DTS Row Level Restartability After an Unexpected Failure
239454 INF: ScriptPkg Tool Generates DTS Object Model Code to Help Use DTS
286124 INF: How to Reschedule a DTS Package
252987 INF: Execute a SQL Server DTS Package from Active Server Pages  (not a Microsoft affiliated site)

Log Shipping:
314515 INF: Frequently Asked Questions – SQL Server 2000 – Log Shipping
323135 INF: Microsoft SQL Server 2000 – How to Set Up Log Shipping (White Paper)
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
321247 HOW TO: Configure Security for Log Shipping
329133 INF: Troubleshooting SQL Server 2000 Log Shipping “Out of Sync” Errors

Move a Database:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
221465 INF: Using the WITH MOVE Option with the RESTORE Statement
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
246133 INF: How To Transfer Logins and Passwords Between SQL Servers
274463 INF: Understanding and Troubleshooting the Copy Database Wizard in SQL
168001 PRB: User Logon and/or Permission Errors After Restoring Dump
264474 PRB: System Database Backups Cannot be Restored on a Different Build of
321836 HOW TO: Retrieve a Table or Rows from Database or Transaction Log Backups

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
815013 HOWTO: Upgrade an Instance of Microsoft SQL Server 2000 Desktop Engine
325023 INFO: Upsize SQL Server 2000 Desktop Engine to SQL Server
325003 HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the
321518 INFO: When to Choose MSDE 2000 as the Database Engine for Your
324998 HOW TO: Obtain and Install SQL Server 2000 Desktop Engine (MSDE 2000)
241397 HOWTO: Back Up a Microsoft Data Engine Database with Transact-SQL
817788 Support WebCast: Microsoft SQL Server 2000 Desktop Engine (MSDE)
285097 INF: How to Change the Default Login Authentication Mode to SQL While
328747 INF: Recovery Planning for Microsoft SQL Server 2000 Desktop Engine
319930 HOW TO: Connect to Microsoft Desktop Engine
325022 INFO: MSDE Security and Authentication
318124 HOW TO: Set Up Development Environment from MSDN Professional
322336 HOW TO: Verify and Change the MSDE System Administrator Password

Performance References:
Inside SQL Server 2000 by Kalen Delaney 
The Guru’s Guides (Transact-SQL, Architecture and Internals, Stored Procedures and XML)
224587 INF: Troubleshooting Application Performance with SQL Server
243586 INF: Troubleshooting Stored Procedure Recompilation
298475 INF: Information Required to Successfully Troubleshoot Application
175658 How To Gather Information for Effective Troubleshooting of Performance
319942 HOW TO: Determine Proper SQL Server Configuration Settings
297864 INF: Performance Considerations for an Upgrade from SQL Server 6.5
314648 INF: Differences in STATISTICS IO, SQL Profiler and Sysprocesses IO
311826 INF: Index Tuning Wizard Best Practices
Index Tuning Wizard 2000,
Index Tuning Wizard 7.0,  
Troubleshooting the Index Tuning Wizard,  
195565 INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
197297 INF: Comparison of Join Techniques
324692 Support WebCast: How to Collect and Analyze Performance Data in
325263 Support WebCast: SQL Server 2000 Profiler: What’s New and How to
815337 Support WebCast: Microsoft SQL Server 2000: Reading Execution Plans in
283886 HOW TO: View SQL Server 2000 Performance Data
139609 PerfMon: High Number of Pages/Sec Not Necessarily Low Memory
268343 Umdhtools.exe: How to Use Umdh.exe to Find Memory Leaks
286350 HOWTO: Use Autodump+ to Troubleshoot “Hangs” and “Crashes”
316365 INFO: ROADMAP for How to Use the .NET Performance Counters
150934 How to Create a Performance Monitor Log for NT Troubleshooting
130926 Using Performance Monitor to Identify a Pool Leak
146005 Optimizing Windows NT for Performance
248345 How to Create a Log Using System Monitor in Windows 2000
275685 INF: Importing Performance Monitor Logs into SQL Server

Replication Overview
315642 INF: Information That PSS Needs to Troubleshoot SQL Server Replication
312292 HOW TO: Enable Replication Agents for Logging to Output Files in SQL
321822 HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
320499 HOW TO: Manually Synchronize Replication Subscriptions by Using Backup
320123 HOW TO: Add Columns in a Rollup Subscriber Topology
321176 HOW TO: Set Up Alternate Synchronization Partner in SQL Server 2000
323046 INF: Transactional Replication Performance Tuning and Optimization
322324 INF: Transactional Replication Performance Tuning and Optimization
241149 INF: How to Automate Replication over a Dial-Up Connection
291001 INF: How to Use sp_scriptdynamicupdproc to Improve the Performance of
305127 PRB: How to Generate a Snapshot of a Published Table and Apply It to the
285106 PRB: SQL Server Enterprise Manager Reports That the Replication Agent Is
304535 PRB: ANSI Warnings May Incorrectly Show Replication Agents As Failed
301217 HOW TO: Prebuild a Database in SQL Server CE
324992 HOW TO: Use Replication with SQL Server 2000 Desktop Engine (MSDE 2000)
303789 INFO: Using MSDE 2000 With SQL Server CE RDA and Replication

Merge Replication
322233 INF: Diagnosing and Troubleshooting Slow Partitioned Merge Replication
325120 Support WebCast: Microsoft SQL Server 2000: Merge Replication
315521 INF: How to Troubleshoot SQL Server Merge Replication Problems
322910 INF: Managed Range Identities in Merge Replication
307356 INF: Understanding Merge Replication Article Processing Order
315514 INF: Understanding Merge Replication Conflict Detection and Resolution
319646 HOW TO: Program the SQL Merge Control by Using Visual C# .NET
309400 FIX: Merge Replication Non-Convergence Fixes in SQL Server 2000 SP2

Security Resources
10 Steps to Help Secure SQL Server 2000 322712 Microsoft SQL Server 2000 SP3 Security Features and Best Practices
SQL Server 2000 Administrator’s Pocket Consultant: SQL Server 2000 Security
813944 SQL Server 2000 Security Tools
309422 INF: Consideration for a Virus Scanner on a Computer That Is Running SQL
829024 INFO: Microsoft SQL Server 2000 Operations Guide
SQL Server 2000 C2 Administrator’s and User’s Security Guide
SQL Server 2000 Resource Kit, Chapter 10–Implementing Security
817384 HOWTO: Use Kerberos Authentication for Microsoft SQL Server 2000
Troubleshooting Kerberos Delegation White Paper 
811889 How to troubleshoot the “Cannot generate SSPI context” error message
Download the latest version of books online: (303839 Updates to SQL Server Books Online for SQL Server 2000 ). Sample topics from SQL Server 7.0/2000 Books Online (BOL): Managing Security, Planning Security, Security Architecture, Security Audit Event Classes, Password Protection, Authentication Modes, Replication Security, Security and Authentication Permissions

SQL Mail:
321183 HOW TO: Troubleshoot Your SQL Mail Issue
311231 INF: Frequently Asked Questions – SQL Server – SQL Mail
263556 INF: How to Configure SQL Mail
315886 INF: Common SQL Mail Problems
Per SQL Server 2000 Books Online: “SQL Server uses two services to handle mail. MSSQLServer processes mail for all of the mail stored procedures. SQLServerAgent does not use SQL Mail to send e-mail. Instead, SQLServerAgent uses its own mail capabilities that are configured and operated separately from SQL Mail.”
Per SQL Server 2000 Books Online regarding the DTS Send Mail task (which does not use SQL Mail or SQL Agent Mail): “With the Send Mail task, you can send an e-mail message as a task. For example, if you want to notify a database administrator about the success or failure of a backup operation, you can link a Send Mail task to the preceding backup task. To use a Send Mail task, you need to install MAPI with a valid user profile on the instance of Microsoft® SQL Server(tm) you are running.”

SSL Encryption:
314636 FIX: Cannot Use Non-Administrator Account to Start SQL Server and Force….
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
322144 SECDoClientHandShake Cannot Connect to SQL Server
257591 Description of the Secure Sockets Layer (SSL) Handshake
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft….
324777 WebCast: Microsoft SQL Server 2000: How to Configure SSL Encryption
318605 INF: How SQL Server Uses a Certificate When the Force Protocol
325757 INF: Using SQL Server 2000 with FIPS 140-1 Ciphers
302409 FIX: Unable to Connect to SQL Server 2000 When Certificate Authority….
311111 FIX: RPC Clients Unable to Login to SQL Server with Windows….

Stored Procedures:
The Guru’s Guides (Transact-SQL, Architecture and Internals, Stored Procedures and XML)
262499 INF: Using Output Parameters with sp_executesql
183001 Retrieving Output Parameters From Stored Procedure
152174 INF: Output Parameters, Return Codes and the ODBC Driver
811482 INF: Handling SQL Server Error Messages When a Stored Procedure is Used
321903 HOW TO: Return Errors and Warnings from a SQL Server Stored Procedure in
321902 HOW TO: Call Stored Procedures with Optional Values in ADO.NET
300488 HOWTO: Run SQL Server Stored Procedures from an ASP Page
245179 HOWTO: Process Multiple Recordsets and Messages in ADO
306574 HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual
310070 HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
310130 HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC
311274 HOW TO: Handle Multiple Results by Using the DataReader in Visual C# .NET
310650 INFO: Known Issues with the Visual Basic T-SQL Debugger and SQL Server
243428 HOW TO: Move Extended Stored Procedures Out of Process

Transaction Logs:
873235 How to stop the transaction log of a SQL Server database from growing
317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
256650 INF: How to Shrink the SQL Server 7.0 Transaction Log
315512 INF: Considerations for Autogrow and Autoshrink Configuration
307487 HOW TO: Shrink the Tempdb Database in SQL Server
281122 INF: Restore File and Filegroup Backups in SQL Server
303229 PRB: Transaction Log Backup Job Created with Database Maintenance
321836 HOW TO: Retrieve a Table or Rows from Database or Transaction Log Backups
272093 INF: Effects of Nonlogged and Minimally Logged Operations on Transaction
286280 INF: Effect of Database File Additions or Deletions on Database Recovery
230785 INF: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage

Transaction Units:
317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
198024 INFO: Understanding ADO Transactions with MS SQL Server
238163 HOWTO: Implement Nested Transactions with ADO and SQL Server
295108 INF: Incomplete Transaction May Hold Large Number of Locks and Cause
295027 INF: Limited Support for Savepoint in Distributed Transactions in SQL

Windows 2003
815431 PRB: Installation of a Named Instance of SQL Server 2000 Virtual Server
329329 PRB: Windows Server 2003 Support for SQL Server 2000
815430 INF: How to Suppress the Appshelp Message in an Unattended Installation
817064 HOW TO: Enable Network DTC Access in Windows Server 2003
301520 SQL Server 2000 Cluster Does Not Install on Windows Server 2003-Based
313037 INF: Upgrading SQL Server Clusters to Windows Server 2003
327270 SQL Server 2000 Is Not Supported on Windows Server 2003 Terminal Server
811430 INF: SQL Server 2000 Supports IPSec on Windows Server 2003 Cluster
281662 Windows 2000 and Windows Server 2003 Cluster Nodes As Domain Controllers
Windows Server 2003 Clustering Services 821751 INFO: Availability of Windows Server 2003 COM+ 1.5 Rollup Package 1

Courtesy: Deepak C (PFE Team, Microsoft)

Continue reading...


Old yet a hot news April 25th, 2006

Vinod Kumar

Looking forward to the BI enhancements coming our way from MS after they acquire ProClarity :) … Interesting … Read the announcement

Continue reading...


Professional SQL Server 2005 Integration Services – Sample Chapter April 25th, 2006

Vinod Kumar

Get a sample chapter from this book on creating packages. Download it


Continue reading...


Moving data from one FG to another April 25th, 2006

Vinod Kumar

Erik outlines the steps of this simple looking yet not so straight forward activity in SQL Server 2005. Check his notes here

Continue reading...


Microsoft Releases SQL Server 2005 Service Pack 1 April 20th, 2006

Vinod Kumar

Read the story here.

Continue reading...