THIS ISSUE SPONSORED BY
Newsflash: SQL Server and .NET are a Powerful Duo
January 15, 2004—In this issue:
1. SQL SERVER PERSPECTIVES
- The Trouble with Patents
2. SQL SERVER NEWS AND VIEWS
- Latest Security Update to Stem Code Execution
- Results of Previous Instant Poll: Small Business Server 2003
- New Instant Poll: Building BI
3. READER CHALLENGE
- January Reader Challenge Winners and February Challenge
- Dig a Little Deeper
- 5 Years of SQL Server Content in One Place
- What's New in SQL Server Magazine: Grading Grid Computing
- Hot Thread: Selectively Slicing a Cube
- Tip: Dangers of Enabling Cross-Database Chaining
- Register: SQL Server 2000 Reporting Services Webcast
- SQL Server Magazine Connections: Win a Harley
7. NEW AND IMPROVED
- Design SQL Databases
- Use Clustering to Manage Industrial Data
8. CONTACT US
- See this section for a list of ways to contact us.
Sponsor: Free White Paper Simplifies Clustering
Find out how Matrix Server lets you easily consolidate SQL Servers and storage, eliminate data silos, and reduce TCO by 50 percent. Do you have non-HA, standalone SQL Servers on Windows 2000? Install Matrix Server and achieve a fully High Availability environment with your existing servers, with no need to purchase additional passive servers. Centrally manage and provide HA across application, file-serving, or Web-serving tiers for up to 16 clustered servers and their storage. Click here for a FREE whitepaper and signup for upcoming demo:
(contributed by Brian Moran, news editor, email@example.com)
Have you heard about a company called Timeline and the patents it holds? I suspect not. However, this company has the potential to significantly affect every database professional in the world. I'll give you some background, but keep in mind that this story is the subject of many pending lawsuits and thousands of pages of legal documents. Calling this week's commentary an incomplete summary would be an incredible understatement.
Timeline holds three U.S. patents, for its Data Warehouse Generator and Data Mart Generator technology, which provides connectivity to account systems and automates the generation of data warehouses and distributed data marts. The patented technology lets Timeline's customers automate the building and maintenance of financial data warehouses in SQL Server for analytic applications, synchronize the warehouse and the general ledger, automatically update the warehouse and related Excel-based reports, and deliver data and reports to user desktops. Timeline asserts that its patents cover such common database functions as using a driver to interrogate a source (e.g., transaction systems), use the information from the source to determine its structure, and in turn use the structural information in a middleware layer to design a target data mart or data warehouse; reporting from a data mart built using this method; write back to a target database; and pull data from multiple sources into one target structure.
According to Timeline's Web site, other patent violations include scheduling the building of or refresh of target data marts and publishing of reporting databases by area of responsibility, natural language queries, caching query results, loading data from a source, using a user interface to select from various drivers, creating new tables in target databases; generating tables, and optimizing reporting marts (e.g., adding summarization, rollups, or new relationships through an interface).
Timeline's home page (http://www.timeline.com) provides information about the patents involved. The full text of each patent is available from the United States Patent and Trademark Office Web site at www.uspto.gov . I encourage you to read the patents to form your own opinion. My research revealed that Timeline asserts that anyone who uses a software tool that automates the process of building a reporting environment that integrates and summarizes data from multiple upstream transaction systems is potentially in violation of the company's patents. This blanket assertion probably includes almost every data-warehousing product on the market. I'm not a lawyer (and I didn't stay at a Holiday Inn Express last night) so that opinion might not be legally correct, but it's based on thoughtful diligent research for this article. Timeline's original patent claims in 1998 were limited to narrow use within a financial services context. Those claims have been expanded since 1998 to apply to any generic data transformation architecture.
Think about the implications of those expanded patent claims. You probably have a reporting system that integrates data into a summarized format in a database that's separate from the original transactional systems in which it was generated.
I was initially stunned to learn that Microsoft and many other major database and extraction, transformation, and loading (ETL) vendors paid for licenses to use the patented technology rather than fight the patents. Public records show that Oracle paid around $1 million to license the patents. Microsoft originally licensed the patents with the understanding that it would be able to sublicense the patents to their customers and to third party software developers who use Microsoft software and tools. Microsoft intended to provide this sublicense to its customers for free to ensure that the patent claims didn't directly affect customers. Microsoft sources told me that for this privilege, the company paid substantially more than other vendors for its license, although the exact figure isn't public. Microsoft filed suit against Timeline shortly after signing the license agreement in June 1999 because Timeline claimed that Microsoft didn't have the sublicensing rights. See the Microsoft PressPass article at http://www.microsoft.com/presspass/press/1999/jul99/timelinepr.asp for additional information about the suit Microsoft filed against Timeline. In December 2002, the Seattle Supreme Court ruled in favor of Timeline on this matter.
Timeline is now suing Cognos over related patent-infringement claims. Cognos has refused to pay a fee to license the patents, claiming the patents are invalid. Last week, Cognos counter-sued Timeline, claiming "unlawful assertion of said patents." Cognos hasn't issued a press release about the countersuit but on January 8, a firm representing Timeline issued a response, which you can read at http://www.newsandearnings.com/ViewFile.asp?ID1=8994&ID2=%5B:FileTracking:%5D&ssid=3&directory=204&filename=TMLN_CGNS_reply_final.pdf . The argument that Cognos makes is interesting because it questions the validity of the patents. A win for Cognos could mean significant problems for Timeline, which has made a substantial sum from suing other companies for patent infringement. Timeline posted a company profile this summer that states, "Patent licensing enforcement efforts have grossed more than $12 million to date and have the potential to contribute millions more in the future."
I wonder why few people in the industry seem to know or care about this pending litigation. It seems to me that these patents could affect every IT shop that uses third-party software to build a reporting database to summarize and integrate data from multiple databases. Deciding the future of all computer-generated reports across every industry that exists is big news to me.
I'd like to point out that Timeline has never sued an end-user company for building a solution using Microsoft and other third-party products. Timeline suits have been against OEMs and Independent Software Vendors (ISVs). This doesn't mean that Timeline's legal strategy couldn't change in the future. However, you don't have to immediately assume that you have to stop that really cool data warehousing project you're working on. At least not yet.
Newsflash: SQL Server and .NET are a Powerful Duo
SQL Server Magazine is a dynamic library made up of SQL Server and .NET Framework expertise. Whether you're looking for novice or advanced-level content, SQL Server Magazine can help you do your job better. Subscribe today and receive 12 print issues, along with full access to the entire online article archive. PLUS, get an extra gift. Click here for details:
2. SQL SERVER NEWS AND VIEWS
Microsoft released Microsoft Security Bulletin MS04-003, which contains an update designed to prevent unwanted code execution in Microsoft Data Access Components (MDAC). DBAs should install this update at their earliest convenience because MDAC 2.6 is included in SQL Server 2000. To read the full bulletin, visit the Microsoft site at
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you plan to run Windows Small Business Server 2003 (SBS 2003)?" Here are the results (+/- 1 percent) from the 168 votes (deviations from 100 percent are due to a rounding error):
- 7% Yes, we're already upgrading to the new version
- 5% Yes, and we're first-time SBS users
- 6% Yes, but later this year
- 82% No
The next Instant Poll question is "Do you build or maintain business intelligence (BI) applications?" Go to the SQL Server Magazine Web site and vote for 1) Yes, I build them, 2) Yes, I manage them, 3) Yes, I build and manage them, 4) No, but my company uses BI applications, or 5) No, my company doesn't use BI applications.
3. READER CHALLENGE
contributed by Umachandar Jayachandran, firstname.lastname@example.org
Congratulations to Ihor Bobak, an MCP and chief software architect for UKEESS Software House in Lviv, Ukraine. Ihor won first prize of $100 for the best solution to the January Reader Challenge, "Inserting Order Details." You can find a recap of the problem and the solution to the January Reader Challenge at
Now, test your SQL Server savvy in the February Reader Challenge, "Importing from Excel" (below). Submit your solution in an email message to email@example.com by January 22. 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: Phil generates reports for a sales team in a company that sells books. The company stores publication data for its books in a SQL Server 2000 database. Phil receives sales data updates in a Microsoft Excel file that has the following header labels for columns: stor_id, yr, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec. The spreadsheet contains quantities of books sold in each store by year and month. You can generate sample data for the Excel spreadsheet from the Sales table in the Pubs database by using the code at the URL below.
Phil needs to import the data from the Excel file into a SQL Server table called StoreSalesSummary by unpivoting the month columns from the spreadsheet. You can create the StoreSalesSummary table by using the following code:
CREATE TABLE StoreSalesSummary ( stor_id int NOT NULL, qty int NOT NULL, yr smallint NOT NULL, mn tinyint NOT NULL, PRIMARY KEY(stor_id, yr, mn) )
Help Phil import only the Excel spreadsheet data into the StoreSalesSummary table, then insert and update each store's sales from the spreadsheet. Import only the stores with a nonzero quantity value for any month.
See the following URL for the full version of the problem, complete with the code for generating the Excel spreadsheet sample data.
(brought to you by SQL Server Magazine and its partners)
Discover SQL Server solutions. Delve into real-world success stories. Drill down into building highly available database servers. Go to the SQL Server Magazine Special Reports section online. Valuable tools are only a click away.
Subscribe to the SQL Server Magazine Master CD and get high-speed, portable access to all articles, code, tips, tricks, and expertise published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue and find fast answers to your SQL Server questions. Subscribe today!
Register today for the exclusive Microsoft SQL Server 2000 Reporting Services Online Launch Event on January 27th, 2004. Learn how this powerful new reporting platform will change the landscape of enterprise reporting. This live event will include executive keynote speakers, customer testimonials, demos, and a live Q&A session. Click here:
SQL Server Magazine Connections conference will be held April 18-21, 2004 with concurrently running events Microsoft ASP.NET Connections and Visual Studio Connections. Save $200--get access to all three conferences for one low price, and get a chance to win a Harley. Register online or call 800-438-6720 or 203-268-3204.
With on-demand grid computing, the computing environment is dynamic, shifting applications between servers to match demand. Although Microsoft has been slow to embrace grid computing at the database level, SQL Server's shared-nothing cluster is scalable, and SQL Server 2000 still holds the top three clustered TPC-C scores. In his January editorial, "Grading Grid Computing," Michael Otey explains that although SQL Server doesn't play in the grid world yet, the platform isn't behind in the database technology race. Read this article today at
Sam001 has an analysis cube that contains three dimensions (D1, D2, and D3) and four measures (M1, M2, M3, and M4). The dimension D1 contains four values (D11, D12, D13, and D14). So, when a user picks D1 as the ROW and the four measures for COLUMN, the cube displays 16 cells. Sam001 encounters his problem when a user selects a dimension other than D1 (i.e., D2, D3, or D4) as the ROW along with the four measures for the COLUMN. Sam001 wants to change the availability of various measures and the corresponding totals depending on the fields the users chooses to slice the cube.
For example, if a user slices the cube on the dimension D1 and selects the value D12, Sam001 wants to show only the first two measures (M1 and M2), and only those two measures should be aggregated and included in the totals.
Sam001 speculates that he might be able to accomplish his goal by writing RULE expressions, but fears he'll have to write too many of them. Will Analysis Server 2000 handle this type of problem gracefully? Offer your advice and see what other people have said on SQL Server Magazine's OLAP/Data Warehousing forum at
Q. I'm about to deploy SQL Server 2000 Service Pack 3 (SP3) to a large number of servers in my enterprise. I noticed an option called cross-database chaining. What does this do?
A. Cross-database chaining is a new configuration option in SP3 that you can enable for individual databases by using the sp_dboption command
EXEC sp_dboption 'privatedb', 'db chaining', 'ON'
You can also enable cross-database chaining server-wide by using the sp_configure command. Alternatively, you can use Enterprise Manager to enable this option for individual databases or at the server level. Be aware that cross-database ownership chaining opens up several serious security holes and, except for specific circumstances, should be avoided. For the complete answer to this question, see
Send your technical questions to Brian Moran at firstname.lastname@example.org.
7. NEW AND IMPROVED
(contributed by Dawn Cyr, email@example.com)
Datanamic announced DeZign for Databases 3.1, a database-modeling tool that uses an entity relationship diagram to support entity modeling techniques. The latest release supports views and index options and lets you export SQL Server comments on tables and columns to the SQL script. The new release also includes an improved logarithm for automatic relationship line posting. DeZign for Databases visually supports the layout of entities and relationships and automatically generates the most popular SQL and desktop databases. DeZign for Databases runs on Win32 platforms including Windows XP, Windows 2000, Windows NT 4.0 and 3.5, and Windows 9x. The tool works with SQL Server, Microsoft Access, Oracle, IBM DB2, Sybase, and other SQL-based databases. DeZign for Databases 3.1 costs $229 and current users can download the update for free. For more information, contact Datanamic at firstname.lastname@example.org, 31(0)-71-3410483, or
Wonderware, an operating unit of Invensys Systems, announced IndustrialSQL Server 8.0 Enterprise Edition historian. The product is designed for use in a plant environment, which requires high-speed acquisition of time-series-based data. IndustrialSQL Server historian combines high-speed front-end data collection with time series extensions to an embedded SQL Server database. The new Enterprise Edition supports Microsoft clustering services, which extend the application's high-availability capabilities. The product's other high-availability features include Server Store & Forward and data acquisition services that you can deploy remotely, which helps eliminate interruptions in data flow. These capabilities let you store data to a secondary server if the primary server goes offline and forward the stored data to the primary server without interruptions when you restore the primary server to operation. For pricing and other information, contact Wonderware at 949-727-3200 or
8. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — email@example.com
- ABOUT THE NEWSLETTER IN GENERAL — firstname.lastname@example.org
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — email@example.com
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — firstname.lastname@example.org
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
Contact Richard Resnick at email@example.com or 800-949-4007.
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.
Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.