THIS ISSUE SPONSORED BY
Accelerate SQL Server Application Performance!
5 Years' Worth of SQL Server Content in One Place
(Below NEWS AND VIEWS)
March 11, 2004—Data Modelers, Arise
In this issue:
1. SQL Server Perspectives
- Data Modelers, Arise, and Take Microsoft's New Survey
2. News and Views
- Microsoft Delays Yukon and Whidbey until 2005
- Results of Previous Instant Poll: Annual Revenue
- New Instant Poll: Time with Data-Modeling
3. Reader Challenge
- Winners of the March Reader Challenge: Restoring a Database
- April Reader Challenge: Protecting Against SQL Injection
- Get the SQL Server 2000 System Table Map Poster!
- Dig Deeper into SQL Server
- What's New in SQL Server Magazine: 54 Administration Tips
- Hot Thread: Installing Reporting Services
- SQL Server Magazine Launches 2 New Forums!
- Tip: Fast Bulk Load in SQL Server 7.0
6. Events Central
- New SQL Web Seminar—Reporting Services Tips and Tricks
- SQL Server Magazine Connections: Win a Harley
7. New and Improved
- Prevent Database Attacks
- Synchronize SQL Server Databases
Sponsor: Free Book: "The 5 Styles of Business Intelligence"
Looking for a single, enterprise-class architecture for all your reporting and analysis needs? Only MicroStrategy uses an integrated platform to deliver all 5 "styles" of BI: Enterprise Reporting, Cube Analysis, Ad Hoc Query & Analysis, Statistical Analysis & Data Mining, and Report Delivery. With MicroStrategy, you'll lower your administration costs by defining metrics once, and then reuse for all applications. Order our free 87-page book today to find out how:
1. SQL Server Perspectives
(contributed by Brian Moran)
I recently ran across the following post on the SQL Server newsgroups: "Are you using a data-modeling tool now? Have you used a data-modeling tool in the past? Do you have ideas, suggestions, or wishes for a new data-modeling solution? If your answer is yes, then we need your help." The post came from the SQL Server and Visual Studio teams, which need your help gathering data-modeling scenarios and customer requirements. The post points to a 40-question survey that attempts to understand the role you play, the amount of time you spend doing data modeling, and other ways you might end up using an existing or new tool.
I've taken the survey, and you should, too. I don't know a single SQL Server professional with data-modeling responsibilities of any kind who hasn't bemoaned Microsoft's lack of a decent data-modeling tool. The fact that someone on the SQL Server team signed the message is a good sign that Microsoft might finally be serious about providing a data-modeling solution for SQL Server professionals. I always thought it was odd that the SQL Server team had little to do with the design of Microsoft's data-modeling offerings. But let's not dwell in the past—let's hope for a brighter future!
So, SQL Server data modelers of the world, arise, and take the survey. If you've ever whined about Microsoft's lack of a quality data-modeling tool and dreamed of a better solution, share your ideas and needs and do something about it! We owe it to ourselves to help Microsoft build a data-modeling tool that's easy for everyone to use. And after you take the survey, email me what you think are the most important design goals Microsoft should address in a data-modeling tool. I'll share the best ideas and my own thoughts about what we need in a data-modeling tool in an upcoming column.
Sponsor: Accelerate SQL Server Application Performance!
Ensure your business applications perform at peak efficiency. VERITAS Indepth™ for SQL Server gives you the application performance management you need by proactively monitoring, analyzing, and tuning SQL Server databases. Download a free trial of VERITAS Indepth™ for SQL.
2. News and Views
by Paul Thurrott
Yesterday, in a stunning move that will likely have ramifications for the next Windows version (code-named Longhorn), Microsoft delayed the next versions of SQL Server (code-named Yukon) and Visual Studio .NET (code-named Whidbey) from late 2004 until the first half of 2005. Both products have been in limited, private beta since last fall, and Microsoft issued early releases of both to Microsoft Professional Developers Conference (PDC) 2003 attendees in October.
"Microsoft made the decision to delay the delivery of these products to ensure that they meet the high-quality requirements of our customers," a Microsoft representative told me yesterday. "We are still on track to deliver SQL Server Yukon beta 2 and Visual Studio \[.NET\] Whidbey beta 1 in the coming months."
Yukon and Whidbey comprise the Yukon wave of products that Microsoft says it will ship before the Longhorn wave, which will include new versions of Microsoft Office, MSN, Visual Studio .NET, Windows, Windows Server, and other products. If Microsoft delays the Yukon products until 2005, Longhorn probably won't ship until the first half of 2006 at the earliest.
The voting has closed in SQL Server Magazine's Instant Poll for the question, "What is your organization's total annual revenue?" Here are the results (+/- 1 percent) from the 134 votes:
- 47% Less than $100 million
- 11% $100 million to $200 million
- 12% More than $200 million but less than $500 million
- 30% More than $500 million
The next Instant Poll question is "How much time do you spend on data-modeling activities?" Go to the SQL Server Magazine Web site and vote for 1) all of my time, 2) most of my time, but I also have other duties, 3) half of my time, 4) some of my time, but they aren't my primary focus, or 5) none.
Sponsor: 5 Years' Worth of SQL Server Content in One Place
Subscribe to the SQL Server Magazine Master CD and get portable, high-speed desktop access to all articles, code, tips, tricks, and expertise published in SQL Server Magazine and T-SQL Solutions since their premiere issues. The CD features articles by such experts as Brian Moran and Kimberly L. Tripp. Search by keyword, subject, author, or issue and find fast answers to your SQL Server questions. Let this helpful resource save you some time. Subscribe today!
3. Reader Challenge
contributed by Umachandar Jayachandran
Congratulations to Quentin Ran, an independent consultant in Houston, Texas, and Anith Sen, an independent database consultant in Cordova, Tennessee. Quentin won first prize of $100 for the best solution to the March Reader Challenge, "Restoring a Database." Anith won second prize of $50. You can find a recap of the problem and the solution to the March Reader Challenge at
Now, test your SQL Server savvy in the April Reader Challenge, "Protecting Against SQL Injection" (below). Submit your solution in an email message to cha[email protected] by March 18. 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: Jeff is a database architect, responsible for designing and developing database solutions using SQL Server 2000. Jeff's main concern when performing code reviews is the use of dynamic SQL in stored procedures that his Web application uses for search purposes. He's worried that the dynamic SQL will put his system at risk for SQL injection attacks, in which an attacker compromises the system's security by executing unauthorized code.
The SearchCustomersAndSuppliers stored procedure is available online. The example uses the sample Northwind database objects to show how a user exploits the dynamic SQL construction in a stored procedure. The Web page in this example lets the user specify searches for customers or suppliers based on relationship, city, company, or contact name. The Web page executes the stored procedure, SearchCustomersAndSuppliers. The parameters for city, company, and contact name let users conduct wildcard searches by using LIKE patterns, meaning they can search for words "like" cty and still get city. The @Relationship parameter limits the search to a specific value.
Using the SearchCustomersAndSuppliers stored procedure, Jeff issues the following command to return the generated queries and a list of customers from the Customers table, which demonstrates the security danger of forming dynamic SQL without specific checks:
EXEC SearchCustomersAndSuppliers @CompanyName = N'%';SELECT * FROM customers;PRINT '', @Debug = 1
By injecting SQL code into the search parameters, an attacker can potentially perform unauthorized actions depending on the permissions of the user account, the Web page, or application executing the stored procedure.
Help Jeff write the dynamic SQL to prevent SQL injection attacks. Ultimately, he wants to suggest to the developers a more secure dynamic SQL approach as a standard technique for stored procedures that require that kind of logic.
If you're an administrator or developer and work with SQL Server, SQL Server Magazine can help you at work. Subscribe today and you'll gain access to a treasury of SQL Server experts, content, tips, code listings, articles, and more. Bonus—the System Table Map Poster. Click here for details:
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 SQL Server tools, tips, and content are only a click away. Visit today:
Got a question about managing or tuning your SQL Server system? In our March focus article, "54 Administration Tips," we've got the answers you're looking for. From index sorting, grouping and aggregating to setting up file locations to using T-SQL to avoid unwanted NULLS and more, these tips are loaded with information to help you manage your systems. Read them all today at
And find the Web-exclusive supplement, "54 More Administration Tips," at
Andrutek_SQL is confused by the three CDs that Microsoft sent that include SQL Server 2000 Reporting Services Enterprise, Standard and Development editions. When Andrutek_SQL tried to install Reporting Services' Standard Edition on Windows 2000 Server, an error message stopped the install and said that ASP.NET wasn't installed. Andrutek_SQL is also having difficulty installing Reporting Services on a Windows XP workstation. Have you experienced similar difficulties? Offer your advice and see what other people have said on SQL Server Magazine's Reporting Services forum at
SQL Server has added new forums for two of SQL Server's hottest technologies: one for Reporting Services and one for replication. Check out the new Reporting Services forum at
And click here to explore the new Replication forum
by Brian Moran
SQL Server 7.0 offers several high-speed mechanisms for loading data. Bulk copy program (BCP) is a high-speed file-import utility that SQL Server has supported since the early days of the database management system (DBMS). BCP lets you quickly load large files and is often a good choice, but it's not user friendly.
In SQL Server 7.0, Microsoft extended SQL Server's data-import capabilities with Data Transformation Services (DTS) and the T-SQL command BULK INSERT. DTS offers a tremendous amount of data-handling flexibility, but BULK INSERT can be twice as fast as either BCP or DTS when used in comparable circumstances.
Why is BULK INSERT so much faster? BULK INSERT is a T-SQL command, so it runs in-process with the SQL Server engine. Thus, SQL Server doesn't need to pass the data along the normal client API network-abstraction layer called a Network Library (NetLib). Bypassing the NetLib layer saves a huge amount of time.
In addition, SQL Server 7.0 supports a custom task add-on that lets you write a BULK INSERT task directly from a DTS package. Microsoft also integrated this feature into SQL Server 2000. If you're looking for the best combination of speed and programmatic workflow control, BULK INSERT from DTS might be the answer.
6. Events Central
For a complete guide to Web and live events, see
The key to getting the most out of Reporting Services is learning the tips and tricks. SQL Server Magazine invites you to attend a free Reporting Services Web seminar designed specifically for SQL Server professionals. This live, online event will be presented on March 17. Register today!
The SQL Server Magazine Connections conference will be held April 18-21 with concurrently running events Microsoft ASP.NET Connections and Visual Studio Connections. Receive access to all three conferences for one low price, and get a chance to win a Harley. Register online or call 203-268-3204 or 800-438-6720.
7. New and Improved
(contributed by Dawn Cyr, [email protected])
Application Security announced AppRadar, an intrusion-protection system for enterprise databases. The system detects attacks and misuse on the database, detects and prevents complex attacks from valid users, facilitates information security lockdown policies, and ensures more efficient use of enterprise IT resources. The host-based system works with a continuously updated database-security knowledge base. AppRadar supports SQL Server 2000 and MSDE 2000 databases. For pricing or to download an evaluation version, contact Application Security at 866-927-7732 or http://www.appsecinc.com/products/appradar.
e-dule Software announced DB SynchroComp 3.0, a tool for synchronizing SQL Server 7.0 and later databases. The tool determines differences between two databases, then generates a script that changes the target database structure to match the source database structure. The product's latest release lets you save the database schema in a file for later use and lets you save the difference report in a text file. Other new features include support for extended properties and handling of unique constraints, more intuitive script generation, and a bug workaround that enables execution of sp_helpdb on all SQL Servers. DB SynchroComp costs $499.95, and you can download a free evaluation copy. For more information, contact e-dule.
Quest Software, Inc.
Database contention affecting SQL Server performance? Download white paper at
DB Ghost for SQL Server
Take control of your source code! Change management for SQL is here.
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.
Here's how to reach us with your comments and questions:
- About SQL Server Perspectives — [email protected]
- About the newsletter — [email protected]
(please mention the newsletter name in the subject line)
- About technical Questions — http://www.sqlmag.com/forums
- About Product News — [email protected]
- About your subscription — [email protected]
- About sponsoring SQL SERVER MAGAZINE UPDATE? — Kate Silvertooth ([email protected]
Manage Your Account
You are subscribed as #EmailAddr#
To unsubscribe from this email newsletter, send an email message to mailto: #Mailing:UnsubEmail#.
To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
Copyright 2004, Penton Media, Inc.