Product Watch
by Blake Eno, products@sqlmag.com
Integrated BI Solution for SQL Server 2005 and 2000
XMLA Consulting announced ReportPortal 2.1, a business intelligence (BI) solution that includes analysis, reporting, dashboards, scorecards, and advanced data visualization features for SQL Server 2005 and 2000. ReportPortal lets you build, publish, and view a variety of reports including data mining, Crystal Reports, and SQL Server 2005 Reporting Services reports. New reports in the latest release include tree map, pie chart tree, bar chart tree, geographical map chart, and dashboard reports. For more information, contact XMLA Consulting at 813-866-3483 or visit http://www.reportportal.com.
Migrate Crystal Reports to Reporting Services
dataReference announced that Report Migrator works in conjunction with SQL Server 2005 so that you can migrate single or multiple Crystal Reports into Report Definition Language (RDL) and run them by using SQL Server 2005 Reporting Services. Report Migrator lets you continue to use Cyrstal Reports Designer to develop reports but distribute them through Reporting Services. This functionality lets you maintain your investment in report design and distribute the report across your organization without retraining your development staff. For more information, contact dataReference at 214-239-0820 or 866-489-4512, or visit http://www.datareference.com.
3. Hot Articles
Q&A: Changing SQL Query Behavior
by Brian Moran, brian@solidqualitylearning.com
Q: I know that SQL Server 2005 lets me choose forced parameterization and use plan guides to change individual query behavior. How do I decide when to use these features?
Read the answer to this question today
SELECT TOP(X): SQL Server Migration Assistant
Converting Oracle databases to SQL Server has always been a manual process fraught with pitfalls—but not anymore, thanks to SQL Server Migration Assistant. Mike Otey tells you about are four features of this tool that will pave the way to a successful conversion. Read this article today and post your comments.
This Month’s Focus: Disaster Recovery—Database vs Storage Replication
by Mel Shum
Are you unsure about whether to use database or storage replication to replicate your data? Here are three factors to consider before deciding which is best for you.
Puzzled By T-SQL: Grouping Consecutive Rows with a Common Element
In this week’s blog, Itzik Ben-Gen posts a puzzle that’s sure to cause a little head scratching. Your task is to write a set-based query (no cursors) that groups consecutive rows with the same attendance status for each student in an Attendance table that Itzik has provided.
Remember, this puzzle is live! Send your solutions to Itzik, and we will give away two prizes: One winner will be chosen randomly out of those who provided correct solutions; another winner will be chosen based on the fastest correct solution. See the full details of the puzzle and submit your solution today.
In a Nutshell: Online Indexing in SQL Server 2005
Does performing online indexing on a large table degrade SQL Server performance? In this week’s blog, Kevin Kline eavesdrops on a conversation between SQL Server MVP Hilary Cotter and Microsoft uber-genius Stefano Stefani. Read about their discussion of this hot topic, and send your comments to Kevin today.
Hot Threads:
4. Events and Resources
Linux + Unix + Windows—TechX World
Pure-play IT shops are a nice idea, but the reality today is that we’re all faced with interoperability issues. TechX World 2006 gives you access to leading experts in the field and will prepare you to master interoperability issues in your environment.
Tired of using separate products on your Microsoft Exchange server for antivirus, antispam, attachment filtering, disclaimers, content auditing/filtering? This webcast will address the latest threats to messaging security and spotlight Sunbelt’s Messaging Ninja that enables system administrators to easily secure their messaging infrastructures and stop threats at the Exchange Server.
Can you distinguish between the facts and fiction of Linux? Get the straight answers about Linux UNIX, and Windows—together with head-to-head comparisons. Read articles and download free resources today! You can also test your Linux skills and enter to win a $150 MSN Music gift card!
Randy Franklin Smith outlines five evaluation points to consider when choosing your antispyware solution in this free podcast. Download it today!
See the complete Windows IT Pro Network guide to Web and live events.
http://www.winnetmag.com/events
5. Featured White Paper
Extend Microsoft Windows Rights Management Services (RMS) to support enterprise requirements for information protection, including proprietary business data. Download the free whitepaper today!
6. Reader Challenge
September Reader Challenge Solution: Fixing a Faulty Lookup Query
by Umachandar Jayachandran, challenge@sqlmag.com
Congratulations to Steve Kass and Lay Chew. Steve won first prize of $100 for the best solution to the September Reader Challenge, "Fixing a Faulty Lookup Query." Lay won second prize of $50. You can read a recap of the problem and the solution to the September Reader Challenge at http://www.sqlmag.com/Article/ArticleID/93486/sql_server_93486.html.
October Reader Challenge:
Now, test your SQL Server savvy in the October Reader Challenge, "Enforcing Uniqueness Without the Unique Constraint" (below). Submit your solution in an email message to challenge@sqlmag.com by September 21. 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:
Paul is a database architect in a company that provides Web-based message services. He must design a schema to store and retrieve forum messages from the database, and he plans to use SQL Server 2000 as the database server. Among the tables that contain the messages is a table that has a unique per-message identifier column. The table's schema is shown in the following code:
CREATE TABLE messages (
msg_id INT NOT NULL PRIMARY KEY,
msg_hdr VARCHAR(1024) NOT NULL
)
Paul wants to enforce uniqueness on the msg_hdr column and tries to define a unique constraint on the column by using the following script:
ALTER TABLE messages ADD CONSTRAINT uq_messages_id UNIQUE(msg_hdr)
The ALTER TABLE statement produces the following warning message:
Warning! The maximum key length is 900 bytes. The index 'uq_messages_id' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.
Using generated sample data for the table, Paul performs tests that reveal that the msg_hdr value might exceed 900 bytes, so he can't use the unique constraint approach. (A unique index in SQL Server enforces a unique constraint, and, as the warning message says, index keys are restricted to a maximum of 900 bytes.)
Help Paul to efficiently enforce uniqueness on the msg_hdr column in the Messages table without the unique constraint.
Hot Spot: Neverfail
SQL Server Guide to Clustering Alternatives
Want the convenience of a server cluster without the expense? Learn about server cluster alternatives that provide high availability, preventative maintenance, and failover capabilities at pricing that fits your budget.
6. Announcements
Special Invitation for VIP Access
Become a VIP subscriber and get continuous, inside access to ALL of the content published in Windows IT Pro magazine, SQL Server Magazine, Exchange and Outlook Administrator newsletter, Windows Scripting Solutions newsletter, and Windows IT Security newsletter. Subscribe now and SAVE $100!
Get the SQL Server 2005 System Table Map FREE
SAVE up to $30 on SQL Server Magazine and get a SQL Server 2005 System Table Map poster FREE with your paid order! In addition, you’ll also get unlimited access to the entire online article archive, which houses more than 2300 helpful SQL Server articles. This is a limited-time offer, so order now!
7. Web Community
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!
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.