SQL Server Magazine UPDATE, May 4, 2006--New Features in SQL Server 2005 SP1

In This Issue:
SQL Server 2005 SP1 adds important new features for enterprise databases, but some of the coolest enhancements are for SQL Server Express.

Subscribe to SQL Server Magazine:

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.


Scalable Software



May 4, 2006

1. Perspectives

  • New Features in SQL Server 2005 SP1

2. SQL Server 2005 Watch

  • Microsoft Releases Fix for SQL Server 2005 ALTER TABLE Bug

3. News & Views

  • SQL Server 2005 Up and Running Roadshow in a City Near You
  • Use SQL Server 2005 Service Broker Certificates for Remote Security
  • Results of Previous Instant Poll: UPDATE's Best Parts
  • New Instant Poll: Streamlining UPDATE

4. Events and Resources

  • The Essential Guide to Infrastructure Consolidation
  • Configuring and Managing a Virtual Lab for the Enterprise
  • Disaster Recovery for Windows-Based Applications
  • IT Consolidation

5. Featured White Paper

  • 100% Uptime for Mission-Critical Email

6. Peer to Peer

  • Hot Tip: Running .NET Applications with the SQLCLR Tool
  • Hot Article: Setting up an Oracle Linked Server
  • In a Nutshell: Ever Wonder About Torn Pages in SQL Server?
  • Hot Threads

7. Announcements

  • SQL Server Magazine Master CD—SAVE 50%!
  • May Exclusive—Save $100 On the Exchange & Outlook Newsletter

8. New & Improved

  • Simplify Database Comparison and Synchronization
  • Fax Integration Solution for .NET Developers

Sponsor: PatchLink
Automatically Analyze, Deploy and Track Security Patches

Does your patch management solution automatically track and re-deploy to ensure network security? 20% of patches unknowingly become un-patched. Learn more about automating the analysis, distribution and tracking of security patches using PatchLink's security patch & vulnerability management solution—the world's largest repository of tested patches. Request a free trial disk.

1. Perspectives

New Features in SQL Server 2005 SP1
by Brian Moran, [email protected]

Microsoft released SQL Server 2005 Service Pack 1 (SP1) two weeks ago, and the release includes major features that you'll want to take a look at. One of the most important parts of the release, of course, is support for Database Mirroring, which was pulled from the SQL Server 2005 release to manufacturing (RTM) last fall at the last minute.

Another major addition is SQL Server Management Studio Express, a set of GUI management tools for SQL Server Express. These tools are a major upgrade from SQL Server 2000 MSDE, which didn't include any integrated set of GUI management tools.

SP1 also introduces SQL Server 2005 Express Edition with Advanced Services. The name met with great fanfare from journalists like myself, who are often paid by the word, although some folks wonder whether Microsoft had a contest to see who could come up with the longest name. The primary difference between earlier SQL Server 2005 Express Editions and the Advanced Services model is the inclusion of support for SQL Server Reporting Services. The Reporting Services-enabled version is still free, so this edition raises all sorts of interesting options for customers who want an entirely free version of SQL Server for true production needs, albeit on a lower-scale than what SQL Server Enterprise and Standard Editions support. I'm not sure why Microsoft created a new SKU instead of simply adding Reporting Services as an install option for SQL Server Express. It will be interesting to see how this SKU might develop compared to the vanilla-version of SQL Server 2005 Express.

SP1 also extends SQL Server 2005 Reporting Services (SSRS) with a new .NET Data provider and a new MDX Query Designer that directly support enterprise reporting against SAP NetWeaver Business Intelligence. You can read an interesting whitepaper about this new feature at http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/UsingRSwithSAPNetWeaver.doc.

You'll also want to make sure to download the latest version of SQL Server 2005 Books Online (BOL). The April version of BOL was updated for SP1 and can be downloaded from the SP1 Web site. The following quote from the BOL download page is important, so I wanted to draw your attention to it:

"Starting with the April 2006 update of SQL Server 2005 Books Online, a separate Books Online for SQL Server Express is no longer available. SQL Server 2005 Books Online is the primary documentation for SQL Server Express. After installing Books Online, you can use the SQL Server Express filter in the Contents pane to hide the content that does not apply to SQL Server Express edition. For more information about SQL Server Express documentation, see the topic "Using SQL Server Express Books Online."

You can download SP1 and related resources, including the latest version of BOL, at http://www.microsoft.com/sql/sp1.mspx.

Sponsor: Scalable Software
How much are you spending on IT compliance? Streamline and automate the compliance life cycle with this FREE white paper, and reduce your costs today!

2. SQL Server 2005 Watch

Microsoft Releases Fix for SQL Server 2005 ALTER TABLE Bug
Has the ALTER TABLE statement been giving you unexplainable problems such as access violations, assertions, cessation of SQL Server services, or database corruption? In addition to these problems, have you received one or both of the following error messages?

Error Message One:
Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'ObjectName' in database 'DatabaseName' because the 'FileGroupName' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Error Message Two:
Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

Microsoft has determined that the problems occur because the row size for the table is calculated incorrectly when SQL Server changes the column of the table. A fix for this bug is available with the latest SQL Server 2005 service pack, available for download at http://support.microsoft.com/kb/913089.

Meanwhile, you can implement your own workaround by using one of the following methods:

  • If the table is not a heap table, rebuild the indexes on the table. A heap table is a table that does not have a clustered index defined.
  • Use the DBCC CLEANTABLE statement to reclaim the space that is used by the dropped column. For example, use the following statement:
    DBCC CLEANTABLE ('DatabaseName','TableName')

You can find further information about this bug in the Microsoft article "FIX: Error message when you try to change a column of a table by using the ALTER TABLE statement in SQL Server 2005: 'Could not allocate space'" at http://support.microsoft.com/?kbid=916780

Sponsor: Idera
Worst Performing Procedures Giving You a Headache?

Idera SQL diagnostic manager gives you all of the performance monitoring, diagnostic information, and alerts you need to quickly analyze, diagnose, and fix performance issues in your SQL Server environment with ease. Want to pinpoint worst performing procedures, long running queries, or other bottlenecks in seconds? Want to check health of SQL Servers in real-time or 6 months ago? No problem. Stop SQL Server headaches today! Download a free 30-day trial of SQL diagnostic manager:

3. News & Views

SQL Server 2005 Up and Running Roadshow in a City Near You
Get the facts about deploying SQL Server 2005 in one information-packed day. SQL Server experts will present practical, real-world information in three tracks—administration, development, and business intelligence. You'll come away from the SQL Server 2005 Up and Running Roadshow with a clear understanding of how to put SQL Server 2005 into practice and how to use its new capabilities to improve your database computing environment.

Use SQL Server 2005 Service Broker Certificates for Remote Security
Microsoft released a new article about how to secure remote connections for multiple SQL Server instances by using Microsoft SQL Server 2005 Service Broker certificates. This information applies to situations in which you have multiple instances of SQL Server 2005 and are using dialog security or transport security operations. The article provides an example that uses two SQL Server instances, which leads you step-by-step through how to use Service Broker certificates and test remote security. For more information about this topic, see the Microsoft article "How to use certificates for SQL Server 2005 Service Broker remote security on multiple instances of SQL Server 2005" at

Results of Previous Instant Poll: UPDATE's Best Parts
"What part of SQL Server Magazine UPDATE is most valuable to you?" Here are the results from the 50 votes:

  • 60% I always read the commentary.
  • 10% The links to online articles are helpful.
  • 2% I like the monthly Reader Challenge puzzle.
  • 2% I'm always interested in product information.
  • 0% I like the info about events and resources.
  • 26% Nothing particular—I just skim for anything that catches my eye.

New Instant Poll: Streamlining UPDATE
"What part of SQL Server Magazine UPDATE would you cut if you had to?" Go to the SQL Server Magazine home page ( http://lists.sqlmag.com/t?ctl=3D1:7B3DA ) and submit your vote for:

  • The table of contents
  • The news/information stories
  • The monthly puzzle
  • Article summaries
  • All of the above
  • None of the above

Editor's Note: As you can tell, we're looking at ways we can streamline SQL Server Magazine UPDATE to bring you the information you want and need. If you have specific suggestions, please feel free to email them to SQL Server Magazine senior editor Dawn Cyr at [email protected] We respect your time and opinions, and we want to make SQL Server Magazine UPDATE valuable to you!

4. Events and Resources

Learn the essentials about how consolidation and selected technology updates build an infrastructure that can handle change effectively.

Use virtual server technology to consolidate your production environment using only a fraction of the server hardware in the data center. Live Event: Thursday, May 18

Learn to differentiate alternative solutions to disaster recovery for your Windows-based applications to determine what works for you and ensure seamless recovery of your key systems—whether a disaster strikes just one server or the whole site. Live event: Thursday, May 11

Increase administration efficiency, build flexible yet inexpensive file-server environments, and maximize potential through consolidation of your SQL Server environment. Make the most of your resources today!

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

5. Featured White Paper

Learn how to address challenges such as making email truly available 24x7x365, securing against viruses, comprehensively backing up email data, and more.

6. Peer to Peer

Hot Tip:
Running .NET Applications with the SQLCLR Tool

by Brian Moran, [email protected]

Q: My company recently upgraded to SQL Server 2005. I'm the DBA, and I'm concerned about enabling the SQL Server Common Language Runtime (SQLCLR). I know that by default, the SQL Server clr enabled option is off. Is it an all-or-nothing proposition? I'm concerned about losing control of the applications running on the server because I'm not proficient with using .NET-based languages?
A: Read the answer to this question today at

Hot Article:
Setting up an Oracle Linked Server

SQL Server's Linked Servers feature lets you access Oracle data and data from other OLE DB/ODBC-compatible data sources from SQL Server. In his May SELECT TOP(X) column "Setting Up an Oracle Linked Server," Michael Otey relates the basic steps for setting up an Oracle linked server. Read this article today and post your comments at

In a Nutshell:
Ever Wonder About Torn Pages in SQL Server?

In this week's blog, Kevin Kline summarizes an MVP description of the performance impact of torn-page detection in SQL Server. Read the blog and tell Kevin how helpful this information is today at

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

  • OLAP/Data Warehousing: Analysis Manager—Cube Errors
  • Performance: Best Use of dbo. Prefix?
  • SQL Server 2005 General Discussion: Filegroups and Files
  • SQL Server 2005 Security: Default Schema for NT-Group

Hot Spot
Discover how to ensure efficient ongoing management of your digital certificates, how your business will benefit by addressing unique online security issues and more!

7. Announcements

SQL Server Magazine Master CD—SAVE 50%!
Subscribe today and get portable, high-speed access to the entire SQL Server article database on CD-ROM—a searchable library that includes every SQL Server Magazine issue ever published. The newest issue also includes BONUS SQL Server 2005 Tips. Order now and save:

May Exclusive—Save $100 On the Exchange & Outlook Newsletter
For a limited time, order the Exchange & Outlook Administrator newsletter and SAVE up to $100! You'll get 12 helpful issues loaded with solutions you won't find anywhere else and FREE access to the entire Exchange & Outlook online article database. Subscribe now:

7. New & Improved

by Blake Eno, [email protected]

Make Database Comparison and Synchronization Simple
Red Gate Software announced SQL Bundle 5, a set of database comparison and packaging tools that includes SQL Compare, SQL Data Compare, and DTS Package Compare. SQL Bundle makes it easier to compare and synchronize SQL Server Databases with new enhancements to UIs, project management capabilities, data views, and automation features. Of particular note are the upgrades to SQL Compare. With the newest release, you can push changes from development to staging, test, and live environments. You can also create a single script for migrating database changes, and keep an audit trail of all changes. Pricing for SQL Bundle starts at $490. For more information, contact Red Gate at 866-733-4283, [email protected], or [email protected]

Fax-Integration Solution for .NET Developers
FaxCore announced FaxCore Integration Server (FIS), a fax-integration solution built on Microsoft .NET technologies. FIS includes a single-server license, .NET Software Development Kit (SDK), SQL-compliant database, and SMTP email integration. The product supports up to 25 users and is bundled with a print-to-fax printer driver. By leveraging FIS's .NET architecture and SDK, you can fax-enable customer relationship management (CRM), email, and office productivity software with minimal lines of code. For more information, contact FaxCore at 720-870-2900, [email protected], or [email protected]

Tell Us About a Hot Product and Get a T-Shirt!
Have you used a product that changed your IT experience by saving you time or easing your daily burden? Tell us about the product, and we'll send you a T-shirt if we write about the product in a future Windows IT Pro Magazine What's Hot column. Send your product suggestions with information about how the product has helped you to [email protected]

Contact Us

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!

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 2006, Penton Media, Inc. All Rights Reserved.

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.