SQL Server Magazine UPDATE, February 10, 2005--Looking for Worst Practices

Subscribe to SQL Server Magazine:

Our domain name has changed! To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

This Issue Sponsored By This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.

High Availability and DR for Your SQL Server

Free .NET or SQL Server Training CD!

DB Ghost For SQL Server

February 10, 2005

1. Perspectives

  • Looking for the Worst is the New Best Practice
  • 2. News and Views

  • Microsoft Fixes Profiler Event Bug in SQL Server 2000
  • Microsoft Provides Workaround for Row-Retrieval Problem
  • Results of Previous Instant Poll: Debugging T-SQL Code
  • New Instant Poll: SQL Server Magazine E-Newsletters
  • 3. Reader Challenge

  • Winners of the February Reader Challenge: Avoiding Application Failure
  • March Reader Challenge: Upgrading Batch-Execution Feature
  • 4. Events and Resources

  • SQL Server Summit 2005 in Houston
  • SQL Server Magazine Connections Conference
  • 5. Peer to Peer

  • Hot Tip: Returning Only the Numeric Part of a String
  • Hot Article: Nonexistent Ranges
  • Kevin Kline's "In a Nutshell" Blog: Wondering About RAID
  • Hot Threads
  • 6. Announcements

  • SQL Server Magazine—Save 30%!
  • Free SQL Server Performance Tips and Articles
  • 7. New and Improved

  • Boost Compliance with Realtime Security Auditing
  • Get Guaranteed One-Button Failover
  • Correction: Crystal Reports XI Pricing
  • Sponsor: High Availability and DR for Your SQL Server Downtime is expensive, disruptive—and unacceptable. Neverfail's easy to use high availability software solutions enable companies of all sizes to keep their users continuously connected to working applications, regardless of the cause of the failure. Neverfail's "cluster-class" solution ensures server reliability, application availability and data protection—at a fraction of the cost and complexity of traditional alternatives. Learn how to keep your users connected to your SQL Server, no matter whether a failure occurs in the operating system, a hardware component, a software application, or somewhere within the network. To view a demo or access a free whitepaper:

    1. Perspectives

  • Looking for the Worst is the New Best Practice
  •     by Brian Moran, [email protected]

    Sometimes focusing on worst practices, rather than best practices, is the best way to tackle a problem. That might sound strange at first glance, but I take that approach more and more often in my consulting work. Here's an example of what I mean:

    I perform a tuning audit and submit a draft of the final report. My customer requests that I include a section about best practices related to performance tuning and scalability. I respond with: I could include a section on best practices, but I'm not going to. I don't think focusing on best practices is a good use of our time. A comprehensive section about best practices would be at least 200 pages, possibly surpassing 500. For example, consider I/O performance—I could churn out 20 pages about RAID storage without even touching hardware or SQL Server best practices.

    Best practices are wonderful, but lately I've come to realize that the best way to tackle a tuning problem is to focus strictly on eliminating the worst practices that you're currently engaged in. Is implementing a best practice the best thing to do if you're not going to get any immediate benefit? Maybe, but you shouldn't fret about it until all your worst practices have been kicked to the curb. Existence of worst practices in your application is what's causing your pain and suffering. Unless lack of adherence to a best practice is causing you pain, it's not a critical path item—unless it's likely to lead to pain in the future. So what about adhering to best practices to avoid problems in the future? That question is worth answering, so I'll cover that in a future commentary. In the meantime, adherence to my new philosophy might be good medicine for a larger percentage of SQL Server customers out there. Let me know what you think.

    Free .NET or SQL Server Training CD!
    See why AppDev was voted THE BEST by readers of SQL Server Magazine...get a FREE .NET or SQL Server training CD-ROM—a $95 value! Click the link below for your free CD-ROM...

    2. News & Views

    Microsoft Fixes Profiler Event Bug in SQL Server 2000
    Microsoft has released a hotfix for a problem that occurs because the start time for the Audit:Login Event is recorded as the time that the login succeeds. The start time for the Audit:Logout Event is recorded when the Process Status Structure (PSS) object is created. So when you view SQL Server Profiler in SQL Server 2000, the start times that are recorded in Profiler are different for the Audit:Login and Audit:Logout Events. You might also see transaction events in Profiler before the Audit:Login Event is recorded. To find out more about this problem and the supported hotfix, read the Microsoft article "FIX: Start times in the SQL Profiler are different for the Audit:Login and Audit:Logout Events in SQL Server 2000" at

    Microsoft Provides Workaround for Row-Retrieval Problem
    Microsoft has provided a workaround for a problem that occurs when you retrieve rows from a SQL Server 2000 database by using browse mode in a client application. If you retrieve rows from a SQL Server database by using browse mode in a client application and the rows contain null values, you might not be able to identify the appropriate rows and perform the correct manipulations to the rows in the result set. Additionally, when you try to update the rows by using the additional key column metadata that the SQL Server program returns, the rows aren't correctly updated in the SQL Server database. This problem occurs when the SELECT query that you try to use to retrieve the rows from the SQL Server database involves an outer join operation, a unique index is defined on the table on the inner side of the outer join statement, and the table's unique key index key columns accept null values. To work around this problem, create the table on the inner side of a query's outer join statement so that the table's unique index key columns can' accept null values. To find out more, read the Microsoft article "You may not be able to identify or modify rows after you retrieve the rows from SQL Server 2000 by using browse mode" at

    Results of Previous Instant Poll: Debugging T-SQL Code
    "How do you debug T-SQL code?" Here are the results from the 135 votes:

  • 35%   Using the Query Analyzer 2000 debugger
  •  4%   Using the Visual Studio T-SQL debugger
  • 14%   Using SQL Server Profiler
  • 46%   By analyzing the results and rereading the code
  •  1% I never need to debug my code
  • New Instant Poll: SQL Server Magazine E-Newsletters
    "How often do you want to receive the SQL Server Magazine UPDATE email newsletter?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=1B79:7B3DB ) and submit your vote for

  • Daily
  • Three times a week
  • Twice a week
  • Once a week
  • Once a month
  • 3. Reader Challenge

  • Winners of the February Challenge: Avoiding Application Failure

  • by Umachandar Jayachandran, [email protected]

    Congratulations to Emmanuel Nanchen, an analyst and developer for Manpower in Switzerland, and Erik McKibben, a DBA for the Port Authority of Allegheny County in Pittsburgh, Pennsylvania. Emmanuel won first prize of $100 for the best solution to the February Reader Challenge, "Avoiding Application Failure." Erik won second prize of $50. You can read a recap of the problem and the solution to the February Reader Challenge at

  • March Reader Challenge: Upgrading Batch-Execution Feature

  • Now, test your SQL Server savvy in the March Reader Challenge, "Upgrading Batch-Execution Feature" (below). Submit your solution in an email message to [email protected] by February 17. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

    Here's the challenge: Mark is a database developer for a company that sells products that run with SQL Server as the back-end database. The company's products currently support all SQL Server 2000 editions and different server collations. One of the company's products consists of a workflow application that has a functionality that lets users create their own batch scripts. Customers use the batch scripts to execute application command-line utilities. The scripts also have tokens that they pass as parameters. After replacing the tokens with their appropriate values, the engine that executes the batch commands processes the tokens. These tokens are case-sensitive and look like the following examples:


    Mark is designing a new version of the feature that simplifies the batch-command execution. As part of the upgrade process, he wants to identify the commands that need to be modified. Help Mark write the query to identify the AppTasks table rows that have the application tokens that must be modified as part of the upgrade process.

    For the sample schema and data for the AppTasks table, go to

    4. Events and Resources

  • SQL Server Summit 2005 in Houston

  • Join Microsoft and our partners in Houston for an in-depth review of SQL Server 2000 and SQL Server 2005 technical presentations and customer success stories for database professionals at the Westin Oaks Hotel in Houston on March 17, from 7:30am-4:30pm. Register with Event Code 1032267447 at the URL below.

  • SQL Server Magazine Connections Conference

  • March 20-23, Orlando, FL. More than 45 sessions presented by Microsoft and industry gurus. Registration includes an exclusive day of presentations about SQL Server 2005 by the Microsoft SQL Server 2005 product team. All attendees receive the latest SQL Server 2005 and Visual Studio 2005 beta. Call 800-438-6720 or 203-268-3204.

    See the complete Windows IT Pro Network guide to Web and live events.

    5. Peer to Peer

  • Hot Tip: Returning Only the Numeric Part of a String

  • by Microsoft's SQL Server Development Team, [email protected]

    Q. How can I retrieve only the numeric part of a string? For example, given the string ZUA123456789, I want only 123456789.

    Read the answer today at

  • Hot Article: Nonexistent Ranges

  • T-SQL querying problems often require you to apply complex logic in your solutions. In his February T-SQL Black Belt column, "Nonexistent Ranges," Itzik Ben-Gan challenges you to identify nonexistent ranges of dates for each store during January 2005—that is, you need to produce a report that shows when stores were inactive during a particular month. Read this article today at

  • In a Nutshell: Wondering About RAID

  • In this week's blog, "Wondering About RAID," Kevin Kline describes a useful article with a good description of all of the types of RAID, as well as many other hardware-setup tips. SQL-Server-Performance.com is a great site to keep up with performance-related information. It's run by hardworking SQL Server DBA and MVP Brad McGehee. When a friend of Kevin's asked about the best types of RAID to use in a variety of SQL Server applications, he naturally thought to send them to Brad's site. Sure enough, it was easy to find an article that had just the sort of information he was looking for. So if you're wondering about RAID and how to set up a SQL Server 2000 database server, check out http://lists.sqlmag.com/t?ctl=1B68:7B3DB for more details. Let Kevin know if the article is useful to you today at

  • Hot Threads: Check out the following hot threads, and see other discussions in our 30 SQL Server forums.

  •      http://lists.sqlmag.com/t?ctl=1B6A:7B3DB

    Administration: Locating SQL Server Program Files and Data on SAN
    DTS: General Network Error When DTS Transfers Data
    Security: Ad hoc Access to OLE DB Provider 'MSDAORA' has been Denied Error
    Performance: Running Same Query in Two Sessions Takes Twice as Long
    T-SQL: Select Records Based on Max in One Column
    Development: How Do I Calculate Column in Query?

    Hot Spot

  • DB Ghost For SQL Server

  • Get the inside scoop on database change management for SQL Server. Find out which product has been coined "the best tool and process seen yet for database change management" and how the associated source code integration and technical support beat the industry standard. Click here:

    6. Announcements

  • SQL Server Magazine—Save 30%!

  • Order now and get 30% off the cover price! As a special bonus, you'll also receive the latest Top SQL Server Tips handbook (includes more than 60 helpful tips) and you'll get free online access to every article published in the magazine since 1999! Sign up now:

  • Free SQL Server Performance Tips and Articles

  • Hundreds of free tips and articles on SQL Server performance tuning and clustering. And get quick and accurate answers to your performance- and cluster-related questions in our forum. All from the SQL Server performance authority: SQL-Server-Performance.Com.

    7. New & Improved

        by Dawn Cyr, [email protected]

  • Boost Compliance with Realtime Security Auditing

  • Application Security announced AppRadar 2.0, realtime intrusion-detection and auditing software. The latest release enables a layered defense of crucial enterprise data that helps you guard against increasingly focused attacks on that data. The product gives you a way to establish controls on the applications that process sensitive information, as well as a means to review and enforce those controls, while still allowing customers, partners, and employees access to the data they need. AppRadar monitors traffic to and from crucial data at the application level and applies extensive security checks while delivering granular user and activity-base monitoring. As a result, AppRadar immediately detects application-level security exploits such as SQL injection and misuse such as a legitimate user attempting to steal credit-card data. New features of the product include network- and host-based sensors, built-in Sarbanes-Oxley and HIPAA policies, wizards for custom rule creation, and support for Oracle database platforms. A beta version of the product is currently available, and the product will be generally available in March. To obtain a beta copy or more information, contact Application Security at 866-927-7732.

  • Get Guaranteed One-Button Failover

  • MessageOne announced OneSwitch, an automated replication and failover service that manages enterprise application availability with the push of just one button. The product's dashboard console gives users a centralized view of SQL Server, Exchange, and other Windows applications. The dashboard contains the controls to initiate failover and failback with one switch, remotely from any location. In 15 minutes or less, the product lets companies recover from outages and resume access to their applications. Customers use their own failover infrastructure, deploying primary and remote secondary servers. Then, MessageOne provides the dashboard control, which automates realtime data replication and point-in-time snapshot archiving. The service continually monitors both the primary and secondary servers to be sure data is always ready. If any problem occurs, the customer receives immediate notification. When a failover is required, the customer can initiate it from anywhere, and application availability is restored in 15 minutes or less. You can take advantage of the failover service anytime, which makes it easy to test your recovery readiness or provide application availability during planned maintenance. Pricing for OneSwitch starts at $300 per server pair per month. For more information, contact MessageOne at 888-367-0777, 512-652-4500, or [email protected]

    Correction: Crystal Reports XI Pricing
    We published incorrect pricing information for Crystal Reports XI in last week's SQL Server Magazine UPDATE. Pricing for Crystal Reports XI ranges from $495 to $595 per user. For complete pricing information, contact Business Objects at 866-681-3435, 604-681-3435, or http://lists.sqlmag.com/t?ctl=1B74:7B3DB.

    Share Your Product Success Story and Get a T-Shirt!
    Have you used a product that saved you time or made your job easier? Tell us your product-success story, and if we print your submission in the Hands On department, we'll send you a SQL Server Magazine t-shirt. Send your product suggestion with information about how the product helped you to [email protected]

    Contact Us

  • About the newsletter—[email protected]
  • About technical questions—http://www.sqlmag.com/forums
  • About product news—[email protected]
  • About your subscription—[email protected]
  • About sponsoring SQL Server Magazine UPDATE—Richard Resnick, [email protected]
  • SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.

    Manage Your Account You are subscribed as #EmailAddr#. To unsubscribe from this email newsletter, click here /#Mailing:UnsubEmail

    To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.

    View the SQL Server Magazine Privacy Policy.

    SQL Server Magazine is a division of Penton Media, Inc., 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

    Copyright 2004, Penton Medi

    Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.