SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
SQL Web Seminar—Tactics for Protecting MS-SQL
What's New in SQL Server 2005?
April 29, 2004—In this issue:
1. SQL Server Perspectives
- Caution: XML in the Developer Zone
2. News and Views
- Microsoft Extends BI Platform with ActiveViews Acquisition
- Microsoft Fixes Compile-Time Problem
- Distributed-Transaction Error in SQL Server 2000 SP3 Resolved
- Results of Previous Instant Poll: Index Tuning Wizard
- New Instant Poll: BI Tools
3. Announcements
- Your Vote Counts—Announcing the 2004 Readers' Choice Awards
- SqlJunkies Has What Developers Need
4. Resources
- What's New in SQL Server Magazine: Let XML In
- Hot Thread: New Server Logins
- Tip: Managing Stored Procedures with Multiplication
5. Events Central
- Microsoft TechEd 2004 Europe, June 29-July 2, Amsterdam
6. New and Improved
- Provide BI Data to Users at All Levels
- Make Analysis Services Data Understandable
Sponsor: SQL Web Seminar—Tactics for Protecting MS-SQL
It's crucial to protect Microsoft SQL Server (MS-SQL) from outside forces (weather, user error, system outages) that can jeopardize application and associated data. Sign up today for a free, one-hour Web seminar on May 4, sponsored by NSI Software. Not only will you learn several solutions associated with protecting MS-SQL, but you'll also work to identify the costs, risks, and advantages of each so you can determine if you have a protection scheme that's well matched to your business needs. Register now and receive a FREE evaluation version of Double-Take and a FREE whitepaper titled, Protecting Your Microsoft SQL Server Data:
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BHZS0AD
1. SQL Server Perspectives
(contributed by Brian Moran, news editor, [email protected])
Join me as we peer into the thoughts of a developer who has just received a freshly minted SQL Server 2005 CD-ROM sometime in the future: "The row size in SQL Server is expanded! I can now put my entire database in a single XML column in a single row and query it through XQuery. You can index the native XML data type, so performance is bound to be great! Our project has a tight deadline, and think how much faster the developers can work if they don't have to deal with those darn stored procedures and inferior T-SQL code. Done! One row, one column—how cool!" Laugh if you want, but don't doubt that otherwise sane and rational developers will be tempted by the allure of this scenario. One row and one column might be over the top, but developers will unquestionably end up using XML in places they shouldn't. Be honest, you know at least one developer who thinks it would be cool to have that one-row, one-column database.
"What's the big deal?" you might ask. In a battle to protect the integrity of databases around the world, the mighty DBA will fight to the death to protect life, liberty, and the pursuit of normalization. But many development shops don't have a full-time DBA who considers it his or her sworn duty to think about and protect data. Fortune 500 shops still have that luxury, but many medium-sized businesses assign database-design tasks to their development team. One or two people, often with little or no database experience, end up being the database keepers.
Two weeks ago, I went out on a limb and suggested that most database developers don't spend enough time on formal data modeling, but that this lack of effort didn't usually create major problems (see "Data-Modeling Tools Aren't Physically Fit"). I might have to change my stance with SQL Server 2005's release. More so than any previous release, SQL Server 2005 includes features that, if implemented incorrectly, can seriously affect your system. Misuse of such new features as the Common Language Runtime (CLR) and the XML data type can hurt the SQL Server engine's ability to perform efficient data access. Poorly designed data models might begin to cause a significant share of performance and application-logic problems. Storing XML data in the database is the right thing to do from a Microsoft tool developer's perspective. But SQL Server community leaders and Microsoft, through its Patterns and Practices initiative and other materials, need to teach developers how to use XML—and how not to use it.
Sponsor: What's New in SQL Server 2005?
SQL Server Magazine offers a library of helpful SQL Server expertise, article archives, endless code listings, valuable tips and an easy-to-use Web site that takes the word "timesaving" to an entirely different level. Our May 2004 issue is dedicated solely to SQL Server 2005, offering the inside scoop on what's new. Serving as just one example, each issue is packed with useful information that can be incorporated into your everyday work life. Experience the benefits of having a go-to SQL Server resource. Subscribe today and get a free SQL Server 2000 System Table Map Poster:
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BHlI0AL
2. News and Views
At the Gartner Business Intelligence Summit 2004 in Chicago on Monday, Microsoft announced that the company will extend its business intelligence (BI) platform by acquiring ActiveViews, Inc. ActiveViews provides an ad hoc reporting system that takes advantage of the power of Microsoft .NET and SQL Server Reporting Services, letting users explore data stored in a variety of systems. Bill Baker, general manager for BI at Microsoft, said, "Based on feedback from customers and industry experts, we believe that by incorporating this functionality within the business intelligence capabilities of SQL Server, Microsoft will provide a more complete BI solution than has been offered in the industry before." The ActiveViews system presents a business view of the data that an end user wants to examine and gives them a window into the database so they can dig into that information. With ActiveViews technology, end users can modify existing reports and build new reports from scratch in a Web-based environment. To read more about ActiveViews and the acquisition, see, "Microsoft Acquires ActiveViews' Business Intelligence Functionality" at:
http://www.microsoft.com/presspass/press/2004/apr04/04-26activeviewspr.asp
When you run a query that uses at least one outer join, the query's compile time might be greater for SQL Server 2000 post-Service Pack 3 (SP3) hotfix build 8.00.0780 or later than for SQL Server 2000 SP3. A supported hotfix is now available from Microsoft. For more details about the hotfix, read the Microsoft article "FIX: The compile time for a query that uses at least one outer join may be greater for SQL Server post-SP3 builds" at:
http://support.microsoft.com/?kbid=836136
Microsoft has resolved a problem that might occur when you try to run a distributed transaction on your instance of SQL Server 2000 Service Pack 3 (SP3). If the distributed transaction doesn't complete successfully, you might still receive the error message "Server: Msg 8525, Level 16, State 1, Line 1. Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction." You might experience this problem if an instance of SQL Server 7.0 is configured as a linked server or as a remote server on your instance of SQL Server 2000 SP3 and if the distributed transaction involves Data Manipulation Language (DML) statements, such as an INSERT, DELETE, or UPDATE, on the tables on the linked server. To find out more about this problem and the resolution, read the Microsoft article "You may receive error message 8525 when you try to run a distributed transaction on an instance of SQL Server 2000 SP3" at:
http://support.microsoft.com/?kbid=834849
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you find the Index Tuning Wizard reliable for finding all the best indexes for your system?" Here are the results (+/- 1 percent) from the 159 votes:
- 9% Yes, I trust it completely
- 54% No, but it's still helpful
- 14% No, I don't find it helpful at all
- 13% I haven't used the wizard but plan to
- 10% I haven't used the wizard and don't plan to
The next Instant Poll question is "Which SQL Server business intelligence (BI) tools are you using?" Go to the SQL Server Magazine Web site and vote for 1) OLAP, 2) Reporting Services, 3) data mining, 4) more than one of the above, or 5) none of the above.
http://www.sqlmag.com
3. Announcements
contributed by Umachandar Jayachandran, [email protected]
In the September 2004 issue, SQL Server Magazine will feature our readers' favorite products and their choice of companies that provide the best overall services. Use this opportunity to make your voice heard and to reward excellence to those that deserve it. Vote for your favorite SQL Server products today at
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BFsN0AV
SqlJunkies is your online community resource for original tutorial and how-to articles for developing applications with SQL Server 2000 and Yukon; peer-to-peer help and networking through discussion forums and newsgroups; technology tips and pointers from expert bloggers; and the latest in SQL Server-related events and news.
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BFll0As
4. Resources
The next release of SQL Server—formerly code-named Yukon and now officially named SQL Server 2005—will provide deep XML integration with the SQL Server database engine. SQL Server 2005 will feature a native XML data type, XML indexing, a new XQuery language, and a graphical XQuery Builder. But not everyone is enamored with the idea of including XML in his or her relational database. In his May editorial, "Let XML In," Michael Otey urges readers to think progressively because the role of the relational database is no longer to server as simply a repository for character and numeric data. Today's applications require a variety of information types, including character, numeric, binary large object (BLOB), and XML data. Read this article today at
http://www.sqlmag.com/articles/index.cfm?articleid=42132
Revuca_SQL needs to rebuild an existing server on a new, more powerful server. Revuca_SQL can restore from tape backups or use the Copy Database Wizard. What does Revuca_SQL need to do to ensure the logins, roles, users, and so on stay valid and usable, assuming they'll have different database IDs and SIDs? Is there an easy way for Revuca_SQL to get all the Data Transformation Services (DTS) packages onto the new server? Offer your advice and see what other people have said on SQL Server Magazine's Administration forum at
http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1664&threadid=120296
(by William Vaughn, [email protected])
T-SQL stored procedures can be too large to manage. If the query optimizer gives up trying to come up with a good query plan (and it will if it feels the query is too complex), you need to simplify your stored procedure. Executing four 100-line stored procedures is often better than executing one 400-line procedure.
5. Events Central
http://www.winnetmag.com/events
Get the most out of Microsoft's software and technology at Microsoft's premier European conference for building, deploying, securing and managing connected solutions. Benefit from 400+ sessions packed with technical content covering Microsoft Visual Studio .NET 2003, Windows Server 2003, Exchange Server 2003, SQL Server 2000, and more. Register now and save 300 Euros. Get the most out of Microsoft's software and technology at Microsoft's premier European conference for building, deploying, securing and managing connected solutions. Benefit from 400+ sessions packed with technical content covering Microsoft Visual Studio .NET 2003, Windows Server 2003, Exchange Server 2003, SQL Server 2000, and more. Register now and save 300 Euros.
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0zFv0AC
6. New and Improved
(contributed by Dawn Cyr, [email protected])
Panorama Software announced Panorama 4.0, a comprehensive business intelligence (BI) solution that works with SQL Server 2000's BI platform to let decision makers at all levels analyze data, create and distribute reports, and measure performance. The latest release includes a "bubble up exceptions" feature that notifies analysts and operational users of exceptions to established rules. This feature lets users identify business trends that might signal a problem or opportunity. In addition, the product includes interactive Web-based dashboards that let users view metrics in a graphical console. Because users access information through the Web, the solution is scalable to a large number of users inside and outside a company's firewall. And Panorama provides ad hoc query capabilities for SQL Server 2000 Reporting Services, so users can run queries and generate reports through a standard Web browser. For pricing and other information, contact Panorama Software at 877-709-5848, 416-545-0990, or [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BHlJ0AM
Databeacon announced that its Databeacon Web-reporting and data-analysis software now supports SQL Server 2000 Analysis Services. The latest release of the software lets users effectively use Analysis Services' data-mining and analysis capabilities and present the resulting data in an interactive format that everyone in the organization can understand. Designed for mid-market organizations (companies or departments that have 100 to 1000 employees), Databeacon offers a low-cost solution for publishing user-friendly reports. For pricing or to download a demo or evaluation version of the software, contact Databeacon at 888-921-8360, 613-729-4480, or [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BHlK0AN
Software FX
Chart FX OLAP-.NET front-end OLAP for Visual Studio developers.
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BGx10A7
DB Ghost for SQL Server
Take control of your source code! Change management for SQL is here.
http://lists.sqlmag.com/cgi-bin3/DM/y/efg30FgQMn0BRZ0BEkO0AN
CONTACT US
Here's how to reach us with your comments and questions:
- About SQL Server Perspectives — [email protected]
- About the newsletter — [email protected]
(please mention the newsletter name in the subject line) - About technical Questions — http://www.sqlmag.com/forums
- About Product News — [email protected]
- About your subscription — [email protected]
- About sponsoring SQL SERVER MAGAZINE UPDATE? — Kate Silvertooth ([email protected]
Manage Your Account
You are subscribed as #EmailAddr#
To unsubscribe from this email newsletter, send an email message to mailto: #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.
http://www.winnetmag.com/email
Copyright 2004, Penton Media, Inc.
