SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
THIS ISSUE SPONSORED BY
Free Business Intelligence White Paper
REAL-WORLD TIPS AND SOLUTIONS HERE FOR YOU
MCP TechMentor Conference in San Diego this Sept
(below NEWS AND VIEWS)
SPONSOR: FREE BUSINESS INTELLIGENCE WHITE PAPER
Retail, financial services, telecommunications: Each of these industries demands timely answers from ever-increasing data. Business Intelligence helps by providing agile reporting, analysis, and information delivery solutions. See for yourself. Order your free white paper, "Meeting Industry-Specific Challenges with Business Intelligence Solutions" today.
August 8, 2002—In this issue:
- Readers Respond to Software Bug Topic
2. SQL SERVER NEWS AND VIEWS
- Buffer-Overflow Vulnerability in MDAC 2.7, 2.6, and 2.5
- Results of Previous Instant Poll: DTS Expertise
- New Instant Poll: Curtailing Software Bugs
3. READER CHALLENGE
- August Reader Challenge Winners and September Challenge
- Win a Free Trip to SQL Server Magazine LIVE! in Orlando!
- Need to Keep Your Servers Running 24/7?
5. HOT RELEASES (ADVERTISEMENTS)
- Cybernet Software Systems? IntelliVIEW
- New! - Distributed SQL Server
- Microsoft ASP.NET Connections
- What's New in SQL Server Magazine: Get Smart with BI
- Hot Thread: Minimum Memory Required for SQL Server 2000
- Tip: Using Views to Control User Access to SQL Server Data
7. NEW AND IMPROVED
- Manage Your Environment
- Manage Patches
8. CONTACT US
- See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, [email protected])
I received dozens of responses to my July 25 commentary "Complacency Creates Vicious Cycle of Software Bugs", which talked about bugs in the software development industry. I believe that most software companies try to deliver high-quality software. We live and work in a free-market society, and real pressures force software companies to keep quality high. But I think the bar of acceptable quality has dropped too low. Most readers agreed with me; the trick is how to make vendors accountable for bugs.
Many readers pointed out that all vendors must adopt any proposed initiative or it would fail. Here's an anecdote from one reader:
"In the old days, Ashton-Tate used to not only publish the known bugs in their dBase program, but they also published workarounds. This was very good for their users but unfortunately led to a situation where the press depicted dBase as a buggy and unreliable system! Naturally people didn't want to use a buggy system, and eventually Ashton-Tate stopped releasing or even acknowledging bugs in their software. Everybody lost a valuable source of information."
Another reader noted that vendors are not all alike. Should we give special consideration to small vendors so we don't prevent entrepreneurs from taking risks?
"... a lot of good software out there is freeware and/or shareware. The package I developed ... is totally free. Should I ever be subject to a liability law, I would just simply pull the plug and so would many of my colleagues."
Reporting bugs isn't as easy as you might think. Here's a comment from a colleague who works at a software company:
"Many bug reports have comments \[that specify which\] customers are affected, and \[the reports\] sometimes include code from a customer's scenario in addition to other proprietary intellectual property. Publishing all bugs to a clearinghouse would require us to create a small team for every product that did nothing but polish bug reports. This kind of work could drain resources better spent in other ways and could have a disastrous impact on smaller companies \[that are\] unable to shoulder the burden."
Should the government get involved? My commentary suggested a consumer watchdog agency backed by new legislation. Most of you believe that the government is poorly suited to solve this problem, and I tend to agree. Washington is unlikely to worry about a few bugs here and there unless an incident drains billions of dollars from the markets in one day, as some of the recent accounting scandals have done. Can or should Washington solve Bug-gate? Probably not. Someone who works for a software-development company offered the following:
"I believe that you need to let the market do its job. Participating in a software defect report effort would really be better suited as \[a voluntary effort\] a company \[could make\] in an effort to build a sense of trust and community around \[its\] product. Government-mandated business processes are bad."
So what should we do if reporting bugs is burdensome and expensive for vendors and the government can't snap its fingers to make everything all right? I don't think we should give up. One reader offered this thought:
"I think the trick (and possibly a difficult one to pull off) would be to create competition between the vendors, so they will happily try to outdo their neighbors. Microsoft and Oracle seem to base a great deal of development resources \[to\] achieving top TPC-C results. Perhaps if the bug levels were tracked and publicized in a similar way, they would see this as a competitive point and motivate themselves. A well-publicized standards authority may achieve greater results than a legal process."
Even better than a standards body, one clever reader suggested the following:
"I think that the software defect report could be a profitable startup. Look at companies \[such as\] Gartner that evaluate technology and vision. If the right \[people\] started the company, they could keep an independent record of bugs and software quality for all vendors and really make an impact. It would then behoove each individual company to contribute where they could to make sure that they had good reviews on the site."
I think that this reader's suggestion is a wonderful idea. It creates a model based on free-market dynamics that could create compelling incentives for software vendors to participate. I plan to launch it in my free time, which—as a DBA, author, husband, and father—I have plenty of!
Whatever the result of these comments, I'd hate to see the spirit behind this conversation die. The flood of email I received about this topic told me that I wasn't the only one who is unhappy with the current situation. At the least, software companies should begin paying attention to the problem of software bugs and should recognize the building dissatisfaction in the community. One reader put it this way:
"I think what's important is that the consumers ... show their frustration and keep the pressure on software companies. There is always a battle going on between features, resources, schedule, and quality. Companies will make choices based on what customers tell them is most important."
Register online for Windows & .NET Magazine LIVE! before this conference sells out. This conference is chock full of "been there, done that" information from people who use Microsoft products in the real world. Increase your productivity with shortcuts, tips and tricks you'll learn only at Windows & .NET Magazine LIVE! Solve those tough interoperability issues, enhance systems administration with new tools, learn how .NET will impact your job. Benefit from our mix of speakers representing magazine authors, Microsoft architects, and other third- party gurus. This must-attend event will help you keep your skills sharp. Loaded with practical information you can use right away. Real-world technical tips and insights you don't want to miss. Register now and you'll receive FREE access to sessions of concurrently run XML Web Services Connections. Stay the whole week and catch SQL Server Magazine LIVE! and Microsoft ASP.NET Connections at deeply discounted prices. Go to
2. SQL SERVER NEWS AND VIEWS
David Litchfield of Next Generation Security Software discovered that a buffer-overflow vulnerability exists in Microsoft Data Access Components (MDAC) that could result in the SQL Server service failing orexecuting arbitrary code from a potential attacker. This vulnerability results from an unchecked buffer in the MDAC functions that handle the OpenRowSet command. Microsoft has released Security Bulletin MS02-040 (Unchecked Buffer in MDAC Function Could Enable SQL Server Compromise) to address this vulnerability and recommends that affected users apply the appropriate patch mentioned in the bulletin.
Sponsored by Oracle
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How would you describe your level of expertise with Data Transformation Services (DTS)?" Here are the results (+/- 1 percent) from the 376 votes:
- 5% Expert
- 24% Advanced
- 40% Intermediate
- 31% Novice
The next Instant Poll question is, "What's the best way to curtail the escalating number of bugs in software?" Go to the SQL Server Magazine Web site and submit your vote for 1) Government regulation, 2) Unbiased software bug reports from a watchdog agency, 3) Fewest-bugs competition regulated by a standards organization, 4) More publicity about unacceptable bug levels in software, or 5) Other.
SPONSOR: MCP TECHMENTOR CONFERENCE IN SAN DIEGO THIS SEPT
Training - Networking - Certification - Solutions
MCP TechMentor has it all - 1- and 2-day workshops; certifications slam sessions; keynotes and general sessions; interactive Exhibit Hall; on-site testing; and special events and receptions. Content, Content, Content. This conference is all about training with 100% increase in training hours from past TechMentors. Immersion training on: Security, Active Directory, Exchange 2000 Server, ISA Server, SQL Server, Group Policy, .NET PKI & Cisco Technologies. Register online today!
3. READER CHALLENGE
(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])
Congratulations to Michael S. Armentrout, senior DBA at Dallas-based SWS Securities, and Werner Geuens, consultant MCDBA at Cronos in Kontich, Belgium. Michael won first prize of $100 for the best solution to the August Reader Challenge, "Synchronizing Logins." Werner won second prize of $50. Honorable mention goes to Peter Lin, who was the first-place winner in our May Reader Challenge. You can find a recap of the problem and the solution to the August Reader Challenge at
Now, test your SQL Server savvy in the September Reader Challenge, "Creating Indexed Views" (below). Submit your solution in an email message to [email protected] by August 14. SQL Server MVP 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: As the architect for several SQL Server 2000 databases, Nick faces performance problems with some ad hoc queries that perform complex joins and aggregations. He decides that creating a few indexed views will generate significant performance gains without requiring him to modify most of the existing code. This solution should be effective because the query optimizer can rewrite the queries dynamically to use the indexed views rather than querying the larger base tables. However, after he creates the indexed views, he realizes that they change the way the code interacts with the base tables and performs the ad hoc queries.
Failure of the stored procedures that modify the base tables in the indexed views is Nick's first hint that creating the indexed views has changed the code. (The applications that invoke the stored procedures and modify the base tables connect to SQL Server through ODBC, OLE DB, or ADO APIs.) When Nick tries to invoke some of the code, he receives the following error message:
Server: Msg 1934, Level 16, State 1, Line 1 INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER,ARITHABORT'.
Help Nick take the necessary steps to ensure that the stored procedures and any code he uses on the base tables work properly after he creates the indexed views.
(brought to you by SQL Server Magazine and its partners)
Have you created a technical solution to a problem or enhanced a program or system feature to improve performance or return on investment? Then get the recognition you deserve for your cutting-edge SQL Server solution by entering our SQL Server Innovator Awards program sponsored by Microsoft! Hurry—deadline for entries is August 15! Enter today at:
Join Morris Lewis for SQL Server Magazine's next Web Seminar, "Planning Highly Available Database Server Environments," on August 27. This seminar will explain methods for achieving high availability and detail the criteria you must evaluate to determine which options will best suit your tolerance for risk and your budget. Register today!
5. HOT RELEASES (ADVERTISEMENTS)
Slash development time for creating reports by over 75%! Integrate powerful reporting features into your applications with ease. Design, develop, deploy & distribute reports in any environment. Absolutely No Client Licensing Fees! Get your client license now at
SQL-UP! ensures uptime and disaster-protection of SQL Server databases. The software clusters databases over LAN or WAN without a shared storage device. Clustered databases are automatically synchronized in realtime. For free download and more information:
Microsoft ASP.NET Connections and VS.NET Connections will co-locate with SQL Server Magazine LIVE! this October. Early Bird discount expires soon, register today to save $2,990 and access all three events for the price of one!
Michael Otey explains how business intelligence (BI) technologies provide insight into your company's line of business (LOB) applications and expose data relationships that can lead to more intelligent decision making. His article "Get Smart with BI" appears in the August issue of SQL Server Magazine and is available online at
Theworm wants to know the minimum amount of memory required to run SQL Server 2000. He's also interested in documentation comparing the minimum memory requirements of SQL Server and Oracle. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
(contributed by the Microsoft SQL Server development team)
Q. I have a Microsoft Access 2000 application with SQL Server 2000 as the back end. To prevent Access users from viewing all the data in a SQL Server 2000 table, I want to use a view that displays only data rows that the user has been authorized to see. Do SQL Server views support such a scenario?
A. Yes, you can create a view that limits user access to SQL Server data—if each user logs in to Access with a unique userid. The following sample statement creates such a view:
CREATE VIEW v_data AS SELECT
FROM dbo.mytable AS a INNER JOIN dbo.authtable AS b ON (a.Pkey = b.DataKey AND b.userid = suser_sname())
This view will restrict access based on userid and will require you to maintain a table (authtable) with the user name matched to specific primary keys in the data table (mytable). If your situation is less complex and doesn't require you to manage row access for multiple users, you could insert the userid column into the data table, as the following code shows:
CREATE VIEW v_data AS SELECT
FROM dbo.mytable AS a WHERE a.userid = suser_sname()
Send your technical questions to [email protected]
7. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
NetIQ announced SQL Management Suite 1.1, software that facilitates SQL Server diagnosis and recovery, configuration management, and change-history functions. You can investigate changes to database permissions and database schema. The suite comprises AppManager for SQL Server, DiagnosticManager for SQL Server, RecoveryManager for SQL Server, and ConfigurationManager for SQL Server. Pricing is $15,000 for a five-server starter pack. The standard edition, which doesn't include AppManager, costs $7500 for a five-server starter pack. Contact NetIQ at 888-323-6768.
St. Bernard Software announced UpdateEXPERT, software that lets you scan and patch security holes. UpdateEXPERT features an extensive database that includes service packs, hotfixes, and other patches. To increase protection, the software scans your networked systems for missing patches and remedies discovered weaknesses. UpdateEXPERT lets you research available fixes, scan your workstations and servers, and deploy updates to any number of networked machines. The software works with network vulnerability scanners to help you enforce security policies. The software supports SQL Server 2000. For pricing, contact St. Bernard Software at 858-676-2277 or 800-782-3762.
8. CONTACT US
Here's how to reach us with your comments and questions:
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
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.
Thank you for reading SQL Server Magazine UPDATE.