SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
FREE White Paper on Continuous Data Auditing
Special Offer from SQL Server Magazine
VERITAS i3(TM) for SQL Server
(below NEWS AND VIEWS)
SPONSOR: FREE WHITE PAPER ON CONTINUOUS DATA AUDITING
Proactively monitor and audit your data without the use of triggers and application modification with Lumigent(R) Entegra(TM)--the single most important, non-invasive solution for verifying, investigating, and reporting data activity. FREE white paper at:
August 14, 2003—In this issue:
- Is Microsoft Abandoning SP2?
2. SQL SERVER NEWS AND VIEWS
- Microsoft Releases Analysis Services Fixes
- Opinion: Windows Worm Should Never Have Been a Problem
- Results of Previous Instant Poll: SQL-DMO
- New Instant Poll: SQL Server Releases
3. READER CHALLENGE
- August Reader Challenge Winners and September Challenge
- SQL Server Worldwide User's Group Help Center
- PDC 2003: Register by Sept. 10th and Save $300
- What's New in SQL Server Magazine: Survive the (Relational) Divide
- Hot Thread: Transaction Deadlocks
- Tip: Using Datetime Columns as Primary Keys
6. HOT RELEASES (ADVERTISEMENTS)
- SQL Server Magazine Connections: 4-for-1 Offer
- SSMU Announces Performance Tuning Mini-Series
7. NEW AND IMPROVED
- Generate Scripts
- Visually Build SQL Scripts
8. CONTACT US
- See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, email@example.com)
Microsoft recently announced a cumulative patch that addresses three new security vulnerabilities in SQL Server: named pipe hijacking, named pipe Denial of Service (DoS), and a new SQL Server buffer-overrun problem. These security holes can lead to system unavailability and elevation of privilege, letting external users take control of your SQL Server system and run unauthorized code. (If you haven't read Microsoft Security Bulletin MS03-031, available at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/MS03-031.asp , take a moment right now to review the bulletin and take steps to rollout out the new patch.)
According to the security bulletin, the SQL Server 2000 security patch exists for SQL Server 2000 Service Pack 3 (SP3) and SP3a, but it isn't available for SP2. Microsoft confirmed that SP2 is subject to these security vulnerabilities, yet the bulletin states: "Microsoft tested SQL Server 7.0, MSDE 1.0, SQL Server 2000 SP3, SP3a, MSDE 2000 SP3 and MSDE (Windows) to assess whether they are affected by this vulnerability. Previous versions are no longer supported, and may or may not be affected by these vulnerabilities." However, the company's Product Support Lifecycle Web site says, "Microsoft has listened to its customer feedback, and is pleased to announce a change in the Service Pack Support Policy. Customers can receive support for the current and immediately preceding service pack, rather than only the most current service pack." This statement implies that SP2, the service pack preceding the current SP3, should be supported. (To read more about Microsoft's service-pack support policies, see http://support.microsoft.com/default.aspx?scid=fh;en-us;lifecycle , paying special attention to the section "Service Packs and Security Patch Policy.") But a related page says that SQL Server SP4 will be the first service pack subject to this new policy. The link isn't obvious to find, but you can read the service-pack support policy for SQL Server at http://support.microsoft.com/default.aspx?id=fh;\[LN\];LifeSQLFAQ .
Although, according to information at this last link, Microsoft is technically within its support rights not to release a security patch for SP2, I don't agree with this decision. Let me summarize the facts as I see them. Three serious security vulnerabilities were recently found in SQL Server. Microsoft quickly released a patch for SQL Server 7.0 and SQL Server 2000 SP3 and SP3a but didn't release a patch for SQL Server 2000 SP2. My initial reaction to this situation was that Microsoft's recent statements indicating that the company "finally gets it" when it comes to enterprise security fall flat. I discussed the SP2 support issue with representatives from the SQL Server team, including Stan Sorenson, Microsoft director of product management for SQL Server.
Microsoft officials believe that SP3a addresses so many security issues (Slammer, in particular) that customers should make every effort to deploy this service pack as soon as possible. "It's absolutely in our customers' best interests to deploy Service Pack 3a," Sorenson stressed. "SP3a includes all the security fixes that were found during the 3-month security review as well as all updates that address Slammer. For this reason, we strongly recommend that customers move to SQL Server SP3a."
(The 3-month security review Sorenson refers to was a hiatus that the development team took last year to work on security issues; all code development stopped for 3 months as Microsoft looked for security problems in the SQL Server 2000 code base.)
Although I agree that customers should do whatever is necessary to be on SP3 (or SP3a), I still disagree with the decision not to roll out a patch for SP2. We've debated the service-pack upgrade issue many times in this space. I don't want to rehash all the arguments, but one of the most compelling reasons for many SQL Server customers not to upgrade to SP3 immediately is that their OEM software package doesn't support it. One of my customers runs a software product that the vendor says doesn't support SP3. Such customers are between a rock and a hard place when it comes to security threats because Microsoft will no longer patch SP2 as new security vulnerabilities arise.
"We don't want to give the impression that Microsoft doesn't care about its customers who are still on SP2," Sorensen said. "What we are saying is that customers who are still on SP2 are putting themselves at a higher risk by not making the move to SP3a." Although it appears that Microsoft isn't addressing a situation in which SQL Server customers might be subject to serious, known security vulnerabilities, Microsoft feels that its focus to get customers on SP3a will help customers avoid more serious issues over the long run.
To help address these SP2 concerns, Microsoft asked me to provide a list of customers or Independent Software Vendors (ISVs) that are unable to move from SP2 to SP3a. If you're facing this dilemma, please send me an email to explain your problem. I can't guarantee that your service-pack issue will be solved, but at least Microsoft will get an idea of how many customers are still at risk.
SPONSOR: SPECIAL OFFER FROM SQL SERVER MAGAZINE
SQL Server Magazine presents the SQL Server Technical Education Package, including a 1-year print subscription to SQL Server Magazine, full SQL Server Magazine Web-site access, and a 1-year subscription to the SQL Server Magazine Master CD (2 CDs), for only $39.95 (US)! Click here for this incredible limited-time offer!
2. SQL SERVER NEWS AND VIEWS
Microsoft has released four Analysis Services-related hotfixes. The supported fixes are intended to correct only the problems described in the following articles. Microsoft recommends applying the fixes only to systems that are experiencing these specific problems.
Because of a change in Active Server Pages (ASP) behavior in Microsoft Internet Information Services (IIS) 6.0, which is included with Windows Server 2003, HTTP connections to Analysis Services server computers running Windows Server 2003 are slower than HTTP connections to Analysis Services server computers running Windows 2000.
If you try to perform an incremental update of an Analysis Services virtual dimension used in a remote partition for a cube, the remote partition is marked as "unprocessed" and the data files are removed from the remote server that's running Analysis Services.
If you perform an incremental update of an Analysis Services cube or partition that uses the HOLAP storage mode, the process might fail during the merge-partitions processing step.
If you try to create an offline Analysis Services cube and both the following conditions are true, the creation of the cube fails: You use the SQL Server 2000 Analysis Services Service Pack 3 (SP3) 32-bit version or the SQL Server 2000 Analysis Services 64-bit version of Microsoft OLE DB Provider for Analysis Services, and you select a parent-child dimension as one of the dimensions for the offline cube. If you try to create the offline cube from Microsoft Excel, you receive the following error message: "Microsoft OLE DB Provider for Analysis Services: Data source provider error." If you use the CREATE GLOBAL CUBE syntax from the MDX Sample Application, you receive the following error message (where "DimensionName" is the name of the parent-child dimension from the source cube): "Syntax error, expecting ',', near: DESCRIPTION 'DimensionName'."
(contributed by Paul Thurrott, firstname.lastname@example.org)
More than a month ago, Microsoft issued a critical security update exhorting users to install a patch that fixed a security vulnerability that the company said, at the time, had yet to be exploited. Microsoft made the patch available through AutoUpdate, Windows Update, the company's public Web site, and its security email newsletters. Security experts at Windows & .NET Magazine and elsewhere practically begged readers to install the patch. I received a copy of an email message that Windows & .NET Magazine Senior Editor Mark Minasi sent to tens of thousands of readers, noting that installation of the patch was an immediate priority for all Windows administrators. The US Department of Homeland Security even got involved, warning not once, but twice, that Windows users should install the patch that Microsoft issued in early July; news media around the world widely publicized the second warning.
And yet this week, when the Johnny-come-lately MSBlast worm struck this very vulnerability, seeking unprotected computers around the world, systems began to fall like so many playing cards. A close friend of mine who is a SQL Server administrator wrote to me yesterday about this problem, citing similar concerns that he voiced when the SQL Slammer worm hit early this year. "Every time one of these new viruses comes out, someone in my company sends a frantic email about it," he wrote. "The systems people always respond that they updated all the systems a long time ago and that, anyway, the virus won't get through our firewall or our virus software. Then I hear about companies like \[automaker\] BMW \[which was affected by the MSBlast worm\]. A company that size, with the resources it has, being affected by a virus that was fixed by a patch issued a month ago? Someone should be fired for that."
I spend a lot of time speaking with systems administrators, and I take a rather hard stance on installing security patches. I find it amazing that my stance divides readers, however. I received an interesting email message yesterday from a systems administrator who asked me to back off and cut administrators a break. They're overworked and underappreciated, after all. We're all overworked and underappreciated but, love it or not, keeping systems secure is part of the administrator's job. The same friend I mentioned above was shocked to learn last year that many of the SQL Server systems that the SQL Slammer worm infected had been left unprotected because the relevant Microsoft patch--made available months before the worm hit—was "too difficult" to install. And he's right. That excuse is ridiculous.
We can and do expend a lot of energy berating Microsoft when we think the company doesn't do a good job. And as an advocate of the people who use Microsoft software, I've given some rather impassioned presentations to various representatives of the company. Certainly, security is one area in which the company needs improvement. But let's be honest for a moment. Isn't Microsoft doing a credible job trying to make security job number one? Aside from creating software that doesn't have any security vulnerabilities (which I think we all agree is impossible), what else could the company have done to make this patch more available to customers—send Microsoft employees to your company to install the patch while you were at lunch?
I get email messages from end users who complain that slow dial-up connections make Windows Update unusable. The sheer number of Microsoft security patches makes administration almost impossible, and the fact that many Microsoft fixes require a server reboot is unacceptable, administrators say. These points are valid, and they make life more difficult for anyone who attempts to patch Windows systems. The company is working to streamline patch management, and life will be better after that happens, but in the end, administrators are responsible for keeping their systems safe and up-to-date, regardless of how easy or difficult that task is. And, frankly, anyone who spends any amount of time on the Internet also should take a little responsibility for his or her own safety. We wear seatbelts when we drive, and we look both ways before we cross the street. The Internet is an equally dangerous place. Maybe it's time we all accept that fact and stop blaming Microsoft for our own mistakes.
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Have you used SQL Distributed Management Objects (SQL-DMO) to write scripts or an application?" Here are the results (+/- 1 percent) from the 277 votes (deviations from 100 percent are due to a rounding error):
- 45% Yes
- 20% No, but I plan to
- 34% No, and I don't plan to
The next Instant Poll question is "What SQL Server releases is your organization running?" Go to the SQL Server Magazine Web site and vote for 1) SQL Server 6.5 and 7.0, 2) SQL Server 7.0, 3) SQL Server 7.0 and 2000, 4) SQL Server 2000, or 5) Other.
SPONSOR: VERITAS i3(TM) FOR SQL SERVER
VERITAS i3(TM) for SQL enables you to guarantee the performance of your SQL Server-based applications. VERITAS i3(TM) ensures application performance manageability and maximizes your ROI. By detecting and correcting the root cause of application slowdowns, VERITAS i3 cuts through organization and technology barriers. Download evaluation software at:
3. READER CHALLENGE
(contributed by SQL Server MVP Umachandar Jayachandran, email@example.com)
Congratulations to Narasimhan Jayachandran, a database-management consultant for HTC Global Services in Troy, Michigan, and Shannon L. Edie, senior database analyst for Kaiser Permanente Center for Health Research in Portland, Oregon. Narasimhan won first prize of $100 for the best solution to the August Reader Challenge, "Trimming Blanks." Shannon won second prize of $50. 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, "Restoring Database Backups" (below). Submit your solution in an email message to firstname.lastname@example.org by August 21. 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: Kumar is a DBA who manages several SQL Server 2000 installations. The databases are organized in filegroups and range in size from 50GB to 100GB. The following code creates a sample NorthwindDW database and tables that show the filegroups' layout:
CREATE DATABASE \[NorthwindDW\] ON PRIMARY( NAME = N'NorthwindDW_Data', FILENAME = N'C:\NorthwindDW_Data.mdf'), FILEGROUP \[fg_NorthwindDW_Y2002\] (NAME = N'NorthwindDW_Y2002', FILENAME = N'C:\NorthwindDW_Y2002.ndf'), FILEGROUP \[fg_NorthwindDW_Y2003\] (NAME = N'Northwind_Y2003', FILENAME = N'C:\NorthwindDW_Y2003.ndf') LOG ON (NAME = N'NorthwindDW_Log', FILENAME = N'C:\NorthwindDW_Log.ldf') COLLATE sql_latin1_general_cp1_ci_as GO USE NorthwindDW GO CREATE TABLE Products( ProductID int PRIMARY KEY ) CREATE TABLE Orders_M20020101( OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2002 CREATE TABLE Orders_M20020102( OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2002 CREATE TABLE Orders_M20030101( OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2003 CREATE TABLE Order_M20030102( OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2003
The sample database contains a filegroup for each year, with each filegroup containing fact tables only for its specific year. Kumar performs a full backup every Sunday and differential backups on the other days. The following list shows the three most recent backups:
- Sunday - Full Backup: NorthwindDW_full.bak
- Monday - Differential Backup: NorthwindDW_diff_1.bak
- Tuesday - Differential Backup: NorthwindDW_diff_2.bak
For testing purposes, Kumar needs to retrieve fact data for the year 2002 only and manipulate that data on a test SQL Server installation. Help Kumar restore the database backups efficiently, conserving disk resources and time.
(brought to you by SQL Server Magazine and its partners)
SSWUG.org (www.sswug.org) provides resources, help, articles, scripts, news, links, and much more on a daily basis about the use and support of SQL Server, Oracle, and XML. Sign up for the daily newsletter.
Microsoft Professional Developers Conference, Los Angeles, CA, October 26-30. Connect with the future of Microsoft platform software and the developers responsible for building it. See under the hood of Microsoft Windows "Longhorn," SQL Server "Yukon," Visual Studio "Whidbey," and more. Register by September 10th, and save $300.
Relational division, which involves matching sets that share some or all members, is a useful T-SQL programmer's tool for retrieving information from database tables. In his T-SQL Black Belt column "Survive the (Relational) Divide," Itzik Ben-Gan presents a relational-division puzzle and invites you to solve it by applying techniques that use aggregations and correlated subqueries. He then shows you an additional solution and some variations. Read this August SQL Server Magazine article at
Sometimes when Rodri tries to access a table, he receives the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005) \[Microsoft\]\[ODBC SQL Server Driver\]\[SQL Server\]Transaction (Process ID 95) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
When he reruns the query, it works. This error always occurs when he's trying to execute the RecordCount property of a recordset. What's happening, and how can he avoid this error? See what other DBAs have said, and offer your advice, on SQL Server Magazine's T-SQL forum at the following URL:
(contributed by Brian Moran, email@example.com)
Q. I'm using a datetime column in SQL Server 2000 as a unique key, and I'm trying to query the data using a Microsoft Access form. I can query the data, but when I try to create two new rows that have primary keys, such as 2003-04-17 00:00:00:00 and 2003-04-17 00:00:00:01, I get an error message indicating that I've entered a duplicate key. What's the problem?
A. Datetime values are accurate only to within 3ms, and smalldatetime values are accurate only to within a minute. For comparison purposes, SQL Server rounds these values. For example, SQL Server treats 2003-04-17 00:00:00:00 and 2003-04-17 00:00:00:01 as identical values during an equality operation. In addition, SQL Server treats them as identical when checking whether a UNIQUE constraint or primary key is truly unique. The scripts that the following code produces both generate an error that proves this rounding behavior:
-- This example uses datetime and is accurate to 3ms. CREATE TABLE DateTimeTest (HeresADate datetime PRIMARY KEY) INSERT INTO DateTimeTest VALUES ('2003-04-17 00:00:00:00') INSERT INTO DateTimeTest VALUES ('2003-04-17 00:00:00:01') -- This example uses smalldatetime and is accurate to 1 minute. DROP TABLE DateTimeTest CREATE TABLE DateTimeTest (HeresADate smalldatetime PRIMARY KEY) INSERT INTO DateTimeTest VALUES ('2003-04-17 00:00:01') INSERT INTO DateTimeTest VALUES ('2003-04-17 00:00:15')
(For more information about SQL Server datetime data types, see the SQL Server Books Online-BOL-topic "Datetime and Smalldatetime.")
A basic database-design principle is that a primary key must always be unique. And because SQL Server can't differentiate between datetime values that are within a narrow range, you must never use a datetime column as a primary key in SQL Server. If you do, you'll get the following error message:
Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__ DateTimeTest__29221CFB'. Cannot insert duplicate key in object 'DateTimeTest'. The statement has been terminated.
Send your technical questions to firstname.lastname@example.org
6. HOT RELEASES (ADVERTISEMENTS)
SQL Server Magazine Connections runs concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Conference. $200 discount expires August 29. Register today and receive all four conferences for the price of one.
The Performance Tuning Mini-Series advanced-level on-line training course for SQL Server professionals will be presented September 3, 10, 17, and 26 from 1:00 to 2:00 p.m. Eastern Time by Kimberly L. Tripp. Register today!
7. NEW AND IMPROVED
(contributed by Carolyn Mader, email@example.com)
Wingenious announced DBAction, a set of T-SQL routines for SQL Server 2000 and 7.0 that can generate scripts for database schema. You can also generate scripts for custom user-defined functions (UDFs), views, and stored procedures in dependency order. DBAction lets you export query results or selected table records to a text file. You can receive information describing the database schema and the current state of the data. You can also close gaps in primary key IDENTITY values and reset the next available IDENTITY value. You can maintain a history of all data changes in selected tables and reverse any change. Wingenious also offers documents about suggested database architecture and free resources, including T-SQL code, on its Web site. Contact Wingenious at firstname.lastname@example.org.
Red Earth Technologies released Superior SQL Builder, an SQL tool that visually builds complete SQL scripts. Superior SQL Builder uses a different methodology for developing SQL scripts, removing the need to cut and paste individual queries for an SQL script and letting developers view an entire script as one entity. A developer can then create SQL scripts faster and reduce syntax and logic errors. The product's interface lets administrators, developers, and IT professionals have complete control over the generated SQL script. You can also use the interface to programmatically access the Document Object from within your applications. Superior SQL Builder supports SQL Server, Microsoft Access, Oracle, and MySQL. Program registration is $199. Contact Red Earth Technologies at email@example.com.
8. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — firstname.lastname@example.org
- ABOUT THE NEWSLETTER IN GENERAL — email@example.com
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — firstname.lastname@example.org
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — email@example.com
- 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 firstname.lastname@example.org 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.
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.
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.