SQL Server Magazine UPDATE, October 16, 2003

REPORTING SERVICES IS THE FUTURE

(contributed by Brian Moran, news editor, [email protected])

Creating commodity solutions for complex IT needs has been Microsoft's key to success over the years and the company's latest commodity solution, SQL Server 2000 Reporting Services, is sure to make a big splash in the data-management and reporting worlds. Although reporting isn't the most glamorous part of the IT world, information and report delivery is at the heart of any business operation. However, many organizations' reporting infrastructures are outdated compared to other IT areas. Several vendors offer sophisticated managed-reporting solutions, but these solutions are pricey, running into six figures. Reporting Services will offer a less costly, integrated alternative to these third-party products. I don't have space to fully describe Reporting Services in this commentary, but you can find additional information at http://www.microsoft.com/sql/reporting/default.asp and in my SQL Server Magazine article, "First Look: Reporting Services."

Last week at the Worldwide Partner Conference, Microsoft announced that beta 2 of Reporting Services is now available. Microsoft hasn't set up public download links for the beta, however you can get a CD by registering to participate in the public beta at http://www.microsoft.com/sql/evaluation/betanominations.asp . The beta code will be available as a download to MSDN subscribers within a few weeks.

Several of my customers are anxiously awaiting this beta's release because they've delayed evaluating other tools until they can look at Reporting Services. Only time will tell if Reporting Services' feature sets are robust enough to compete with pricier tools, but Microsoft's model for delivering Reporting Services will certainly attract customer interest. Reporting Services is included as a core part of SQL Server, much like Analysis Services is. You can't buy Reporting Services as a separate product—the bits for Reporting Services will be part of the SQL Server license and will be included as part of the SQL Server installation media. Installing Reporting Services is free on a server that has a valid SQL Server license. Client connectivity licenses follow the same rules as a typical SQL Server client.

Reporting Services Standard and Enterprise editions will have slightly different feature sets. Microsoft hasn't specified what the differences will be yet, but the company told me that the Enterprise Edition will have additional features to improve Reporting Services' scalability.

Reporting Services requires a local SQL Server for metadata management, but Reporting Services is client and server neutral from a content perspective. In other words, you could role out a managed-reporting solution based on Reporting Services in a UNIX environment that otherwise has no SQL Server installed. So Reporting Services solutions certainly aren't limited to Microsoft-centric environments.

My customers have been looking for a low-cost, powerful solution such as Reporting Services. I'll be surprised if Reporting Services isn't a big hit. Take a look at the beta bits and let me know what you think.

SPONSOR: LET SQLMAG.COM HELP YOU DO YOUR JOB

Visit the SQL Server Magazine Web site. You can find answers to many of your SQL Server questions here, job-related or not. Take advantage of the available search box and navigation toolbar to access new articles, tips, archived articles, associated code, and more! The site features columns by such authors as Brian Moran, Kalen Delaney, and Itzik Ben-Gan, as well as an active SQL Server forum, weekly Instant Poll, and links to articles from past issues. In addition, you can also renew or subscribe to SQL Server Magazine and gain access rights to even more useful SQL Server information. Hurry and visit this helpful resource today! Click here:http://www.sqlmag.com

2. SQL SERVER NEWS AND VIEWS

MICROSOFT ANNOUNCES REPORTING SERVICES BETA 2

In his keynote address at the SQL Server Magazine Connections conference, Microsoft director of SQL Server Product Management Stan Sorensen announced that SQL Server Reporting Services Beta 2 is now available and said that Microsoft will ship the product by the end of the year. Sorensen encouraged attendees to try out this latest beta and give Microsoft any feedback they have. Sorensen also encouraged attendees to watch the Web site for the open beta of Yukon, which will come in the first half of 2004. Yukon is currently in a closed beta that includes about 1000 customers. To sign up to test beta versions of SQL Server products, visit Microsoft's Beta Nominations site athttp://www.microsoft.com/sql/evaluation/betanominations.asp

VISUAL STUDIO TOOLS FOR OFFICE SYSTEM INCLUDES SQL SERVER

Marie Huwe, general manager of Microsoft's Developer Division, announced that Visual Studio Tools for the Microsoft Office System is now available. The Microsoft Office System lets developers create solutions that integrate information stored on different platforms and systems. The new Visual Studio tools combine features of Visual Studio .NET and the .NET Framework to let developers create applications that take advantage of familiar Office interfaces and easy deployment. Visual Studio Tools for the Microsoft Office System supports XML and XML Web Services. The tools include SQL Server 2000 Developer Edition so that developers can test solutions before deploying them. You can read more information about the tools at http://msdn.microsoft.com/vstudio/office/officetools.aspx .

SQL SERVER INNOVATORS TAKE TOP AWARDS

Altan Arslan, software projects manager at Turk Economy Bank in Instanbul, Turkey, won the grand prize in SQL Server Magazine's second annual Innovator Awards contest for his company's realtime stock-exchange application.

Arslan received his award this week in Palm Springs, California, at the SQL Server Magazine Connections conference, where three runners up and 10 honorable mention winners were also announced. Douglas McDowell, principal consultant at Intellinet in Atlanta, won first runner up for a network management system that incorporates SQL Server Analysis Services, Data Transformation Services (DTS), Notification Services, and the new Reporting Services. Second runner up, Xingguang Ou, DBA at CallPlus Service, Ltd., in Auckland, New Zealand, won for a SQL Server replication solution that involves MySQL. And Paul Munkenbeck, senior database consultant with Maritz Europa, Ltd., in Marlow, UK, was named third runner up for using SQL Server 2000's XML functionality to create an application that captures and color-codes server service availability information and presents it in an HTML document.

The grand-prize-winning stock-exchange application supports 1200 concurrent internal users and 60,000 external customers, with users requiring immediate notification of data modifications to stock information. SQL Server Magazine will showcase this and other winning solutions from the 2003 Innovator Awards program in upcoming issues of the magazine.

The Innovator Awards, designed to recognize creative SQL Server solutions to technical and business problems, drew 42 entries. "Looking at all the creative solutions that participants shared in this year's Innovator Awards program was inspiring," noted Kathy Blomstrom, editor of SQL Server Magazine. "The entries really demonstrated that customers are bringing SQL Server's full capabilities to bear in going beyond simply a workable solution to find the best way to meet their organizations' needs."

Judges named the following (in alphabetical order) as honorable mentions in this year's awards program: Steven Berringer, Parker Hannifin, Orange, California; Tim Caylor, Thomas Nelson Publications, Nashville; Andrea Corey, Eloqua Corporation, Toronto; Tom Ellison, Ellison Enterprises, Grand Island, Nebraska; Resilene Mansur, CSN, Rio de Janeiro; Merle Martell, Cargill, Minnetonka, Minnesota; Jose Muller, AscendOne Corporation, Columbia, Maryland; Arindam Sen, American Power Conversion, West Kingston, Rhode Island; Ashley Smith, Tenon Group, Shepton Mallet, UK; and Rolf Tesmer, Australia Post, Melbourne.

RESULTS OF PREVIOUS INSTANT POLL: VISUAL STUDIO .NET

sponsored by Lumigentwww.lumigent.com/go/wb13

The voting has closed in SQL Server Magazine's Instant Poll for the

question, " Are you using Visual Studio .NET?" Here are the results (+/- 1 percent) from the 434 votes (deviations from 100 percent are due to a rounding error):

NEW INSTANT POLL: REPORTING SERVICES BETA 2

The next Instant Poll question is "Are you interested in participating in the second beta test for SQL Server Reporting Services?" Go to the SQL Server Magazine Web site and vote for 1) Yes, I'm already signed up, 2) Yes, I plan to participate, 3) Yes, but I don't have time, or 4) No, I'm not interested.
http://www.sqlmag.com

SPONSOR: NEED FAST ANSWERS TO YOUR SQL SERVER QUESTIONS?

The SQL Server Magazine Master CD provides realtime, high-speed access to all articles, code, and expertise published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue. Order your one-year subscription today!
http://www.sqlmag.com/mastercd

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

CONDUCT AD-HOC SCALABILITY AND PERFORMANCE TESTING

SQL Server Magazine and CSA Research have made available the Database Performance Portal. IT professionals conduct scalability studies; perform ad hoc systems health analysis; identify infrastructure bottlenecks; conduct off-site diagnostics; and qualify new hardware purchases. Check out this helpful resource today!
http://csaperform.sqlmag.com

DON'T HAVE THE NEWEST SYSTEM TABLE MAP POSTER?

Become a subscriber to SQL Server Magazine and automatically receive the most recent version of the Microsoft System Table Map Poster—and by becoming a subscriber, you'll have access to all of the newest information and tools needed for SQL Server. Limited quantities of the poster are available—subscribe today:
http://secure.pentontech.com/nt/sql/index.cfm?promocode=psep213afp

4. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: XML DOCUMENT TYPES

XML has become a vital interoperability tool and a core component of many applications. Visual Studio .NET uses XML as its main programming infrastructure for intra-object communications and to create project files. In his SQL Seven column "XML Document Types," Michael Otey shows seven XML document types that developers use most often in SQL Server database applications. Read this October SQL Server Magazine article at
http://www.sqlmag.com/articles/index.cfm?articleid=39889

HOT THREAD: BETTER PERFORMANCE FOR DYNAMIC QUERIES

Srinivas_mca says, "In my stored procedures, I usually create dynamic queries by storing the query in a string variable and adding conditions to the string based on the IP and business logic. Then, I run the string by using the command EXEC(str)." Srinivas_mca wants to know which will provide better performance: using the EXEC command or running the T-SQL string directly. Read what other DBAs have said, and offer your suggestions, on SQL Server Magazine's Performance forum at
http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=19894

TIP: OLD JOIN SYNTAX VS. NEW

(contributed by Brian Moran, [email protected])

Q. I'm responsible for some old T-SQL code, and I've noticed that a few queries return incorrect result sets. The queries I'm having trouble with all use outer joins and the non-ANSI syntax for expressing them, which was required in SQL Server 6.5—before ANSI-compliant outer-join syntax was added. For example, the following query against the Northwind sample database returns incorrect results:

SELECT Customers.CustomerIdFROM customers, ordersWHERE customers.Customerid *= orders.CustomerIdAND orders.CustomerId IS NULLORDER BY Orders.CustomerId

I need to see a list of customers who don't have any orders, but this query returns all 91 customers, even though most of them have a record in the Orders table. How can I get the correct result set?

A. SQL Server lets you write an outer join two different ways. You can use the old syntax, which is proprietary to SQL Server, or you can use the new ANSI-compliant syntax for expressing the join. The old syntax places the JOIN expression in the WHERE clause and uses an asterisk (*) to indicate which table is the preserved table in the outer-join relationship. The new syntax places the JOIN expression in the query's FROM clause and explicitly uses the OUTER keyword, as the following query shows:

   SELECT Customers.CustomerIdFROM customers LEFT OUTER JOIN ordersON customers.CustomerId = orders.CustomerIdWHERE orders.CustomerId IS NULL

You're probably familiar with how syntax varies depending on whether the JOIN expression is in the WHERE clause or the FROM clause. In most cases, each query will produce an identical, and correct, result set. However, you might not be aware of problems that can occur when you combine outer joins with an IS NULL check in the WHERE clause.

Let's examine the first query to understand why SQL Server returns an incorrect result set. Your goal in this query is to join the Customers table to the Orders table, then retrieve all records where the value for CustomerId in the Orders table is NULL. However, instead of getting the correct result of two rows, you get 91. You get the incorrect result because the OUTER JOIN condition is in the WHERE clause instead of the FROM clause, causing SQL Server to perform the IS NULL check against orders.CustomerId before the join. To get the answer you want, you need to test for IS NULL after the join. But if you use the old join syntax, SQL Server evaluates the test before it processes the join. Because no rows in the Orders table have a NULL value for CustomerId, SQL Server eliminates all the Orders table's rows before it processes the outer join. So when SQL Server processes the join, it doesn't see any rows in the Orders table and, thus, incorrectly returns all customers as having no orders.

The ANSI syntax for expressing a join removes the ambiguity about when the IS NULL check should happen. When you use the ANSI syntax, SQL Server evaluates JOIN expressions before WHERE conditions in all cases. I encourage you to always place JOIN conditions in the FROM clause instead of in the WHERE clause, especially when using outer joins, so that you don't have this kind of evaluation-order problem, which can lead to wrong answers.

Send your technical questions to [email protected].

5. HOT RELEASES (ADVERTISEMENTS)

LUMIGENT: FREE PAPER ON CONTINUOUS DATA AUDITING

Learn how you can monitor and audit database activity without the use of triggers and application modification. Automatically capture changes in permissions, schema and data mods. Free white paper at:
http://www.lumigent.com/go/hl12

ENTERPRISE DATA FORUM

Don't miss Enterprise Data Forum, November 3-6 in Philadelphia. Topics include Integration, XML, Data and Process Modeling, Analytics, Legislative and Privacy Compliance (including Sarbanes Oxley), Data Quality, Profiling, and Metadata. Over 90 speakers. 3-for-2 group discount!
http://www.wilshireconferences.com/EDF2003/index.htm

6. NEW AND IMPROVED

(contributed by Dawn Cyr, [email protected])

SYNCHRONIZE DATABASES

E-dule announced DB SynchroComp 2.3, a database tool that compares two databases. If the tool finds differences, it generates scripts to change the target database's structure to match the source database while maintaining the integrity of the target database's data. Enhancements in the latest release let you synchronize stored procedures, functions, views, and triggers even if they're encrypted on the target database. DB SynchroComp 2.3 supports SQL Server 2000 and 7.0. For pricing and information, contact e-dule at [email protected] or
http://www.e-dule.com

INCLUDE SEARCH CAPABILITIES IN YOUR APPLICATIONS

IMP Technology announced that it has received a US patent for its IMP Search Engine, indexing and search software that developers can integrate into their SQL Server-based applications. The software lets users search SQL Server for information while accounting for alternative spellings and synonyms. The software gives users search results in order of relevance and lets users refine their search criteria. The search engine is written entirely in SQL for use with SQL Server databases, so developers can easily include it in their applications. For pricing and information, contact IMP Technology at
http://www.imptechnology.com

7. CONTACT US

Here's how to reach us with your comments and questions:

(please mention the newsletter name in the subject line)

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

The SQL Server Magazine Connections conference—loaded with best-practices information from magazine authors and Microsoft product architects—is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job.
http://lists.sqlmag.com/cgi-bin3/flo?y=ePF50FgQMn0BRZ0ggP0At

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email

Thank you for reading SQL Server Magazine UPDATE.