SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
THIS ISSUE SPONSORED BY
All Built-in Administrator Accounts Compromised
SQL Server Magazine - No Risk Offer!
24 X 7 AVAILABILITY WEB SEMINAR
(below SQL SERVER NEWS AND VIEWS)
SPONSOR: LIEBERMAN & ASSOCIATES
The built-in administrator account on all your workstations has been compromised. USER MANAGER PRO mass changes local admin accounts, passwords, auditing, policies, groups, members, registry, scan IP subnets in minutes + automatic retry/scheduled operations. Wake On LAN now available! :)
FREE TRIAL VERSION: http://lists.sqlmag.com/cgi-bin3/flo?y=eLtF0COfyP0BRZ01qh0Ag
May 9, 2002—In this issue:
- Check Out the SQL Server Playback Program
2. SQL SERVER NEWS AND VIEWS
- Magazines Name Technical Excellence Award Winners
- Results of Previous Instant Poll: Time Spent Coding
- New Instant Poll: Beta Program Participation
3. READER CHALLENGE
- May Reader Challenge Winners and June Challenge
- Immediate Access to T-SQL Solutions!
- Vendors to Unveil New Solutions at XML Europe 2002
5. HOT RELEASE (ADVERTISEMENT)
- Rich, Interactive web-based reporting!
- What's New in SQL Server Magazine: In the Picture
- Hot Thread: Linked Server Problem
- Tip: Understanding Code Pages
7. NEW AND IMPROVED
- Automatically Generate VB Code
- Facilitate Network Management
8. CONTACT US
- See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, firstname.lastname@example.org)
Microsoft announced a new SQL Server quality assurance program last year called the SQL Server Playback Program, which I wrote about in my SQL Server Magazine UPDATE article "Participate in the SQL Server Playback Program!" (which is available online at the URL at the end of this paragraph). Microsoft is always looking for customers with interesting application workloads that fit the program criteria, and the company is now actively seeking a new round of SQL Server Playback customers to get ready for the Yukon rollout in 2003. So, I'd like to revisit the unique opportunity this program presents for the right SQL Server customers.
What will the SQL Server Playback Program do for you? Participation in the program:
- might give you access to key Microsoft development resources
- will extend your virtual testing or quality assurance team to include the Microsoft SQL Server development team testers
- will ensure your code works with the next release of SQL Server
- might help you spot previously undetected performance bottlenecks
- will make you eligible to win cool prizes
Interested? If so, keep reading!
You've probably used SQL Server Profiler to examine the commands your SQL Server is processing. However, you might not realize that Profiler also provides a foundation for "playing back" a trace file later. Profiler lets you create repeatable workloads that you can run again and again, tweaking certain configuration parameters or indexes as you go. Microsoft uses this capability to create robust test suites based on live customer applications. SQL Server 2000 is very reliable, but the SQL Server test team is always looking for ways to push SQL Server to the next level, and the best way to do that is to benchmark the performance and behavior of real applications.
Microsoft needs your help to find the busiest servers, the largest databases, and the most complex queries the team can use to stress SQL Server to the limit. In the Microsoft labs, the team will replay these workloads on future versions of SQL Server to stress the server while applying concurrent workloads. The team will look for reliability and performance problems. If Microsoft accepts your workload for testing, you'll have extra assurance that your database code will work and perform marvelously with the next SQL Server release.
Microsoft will require a few commitments from you if you participate in the Playback Program. You must provide a backup and Profiler trace of your production database and workload. Microsoft will provide nondisclosure agreements (NDAs) to secure your data for use only in this program. The engineers who manage this program have told me that the biggest hurdle some customers have to cross before deciding to join the program is allaying their own concerns about how well Microsoft will guard their data and application code. Speaking about security practices in the SQL Server Playback Program, a Microsoft representative said, "Security is naturally of utmost importance. Rest assured that customer databases will be used only by the SQL \[Server\] team for testing purposes and will not be distributed to anyone else whatsoever, for any purpose. All playbacks are held on a secure storage server where only the SQL \[Server\] team has access. Furthermore, all media (tapes, CDs, etc.) are stored in a locked cabinet room. We can also provide two-way signed nondisclosure agreements upon request, if it eases your concern."
What types of workloads will Microsoft select? All Playback Program submissions must be from SQL Server 2000 on production servers. The team is looking for Profiler traces that include some of the following characteristics (but your application doesn't have to include all these items):
- databases larger than 50GB
- databases containing localized data
- databases containing Unicode data
- single SQL Server systems supporting more than 10 active databases
- systems supporting more than 200 concurrent users
- applications that use a SQL Server feature in a unique way
- applications that contain complex stored procedures, triggers, cursors, or any other development factors that challenge the server
If you want to participate in the SQL Server Playback Program, email your company name, company contact, email address, and phone number to email@example.com. Microsoft will review your application and respond if you qualify. Also include
- a brief description of the applications SQL Server supports
- the SQL Server version (including service packs and Quick Fix Engineering—QFE—updates that the application runs on
- the number of databases your server supports
- the number of applications your server supports
- the approximate disk size to support all of your databases
- the SQL Server features the application uses
- any SQL Server 2000-specific features the application leverages
- the approximate maximum number of concurrent users the application supports
- the approximate number of total users the application supports
- if you use a localized SQL Server version, the language you use
- whether you store localized data or Unicode data
- any problem you're having with SQL Server, the nature of the problem, and whether you're working with someone at Microsoft to resolve the problem
Microsoft will accept submissions for the current round of SQL Server Playback testing until August 31. In addition to the technical benefits you'll get if you participate, you'll receive a cool SQL Server polo shirt, and a Microsoft representative told me that workloads that "help us find issues" will earn "a little something as a reward."
SPONSOR: SQL SERVER MAGAZINE - NO RISK OFFER!
Hands-on, how-to articles covering Database Modeling, ADO.NET, XML, Performance Turning, Security and all of the issues database developers and administrations need to know to manage SQL Server. Subscribe to SQL Server Magazine with this NO RISK offer and, if for any reason, you are not satisfied with your first issue, just write cancel on the invoice and return it to us. Keep the first issue with our compliments - no questions asked. Subscribe today at:
2. SQL SERVER NEWS AND VIEWS
The Unisys ES7000 server and ProClarity Analytics Platform 4.0 each won a Technical Excellence Award at the SQL Server Magazine LIVE! and Windows & .NET Magazine LIVE! event this week in Palm Springs, California.
Michael Otey, senior technical editor for SQL Server Magazine and Windows & .NET Magazine, said that all the products presented at the event expo were excellent in their fields. "The final winners were chosen based on the products' strategic importance to the market, the competitive advantages, and the competitive features as compared to other products in the market," he said.
Otey said that the Unisys ES7000 server is unique because of the hardware platform's ability to scale up. "The ES7000 allows scaling up to 32 processors, whereas the nearest competitor scales to only eight processors," he said. "It is the only platform that can work with Microsoft Datacenter, and it allows servers to be partitioned to work with multiple OSs."
The ProClarity Analytics Platform benefits the enterprise by enhancing SQL Server Analysis Services through a reporting capability that increases understanding and awareness of internal business practices, according to Otey.
"Analytics Platform 4.0 provides the ability to scale up throughout the enterprise and is flexible enough to be used by everyone from a power user to the occasional administrator," he said.
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "How much time per day do you spend coding?" Here are the results (+/- 1 percent) from the 637 votes:
- 14% None—I don't code
- 25% 1-2 hours
- 32% 3-5 hours
- 20% 6-8 hours
- 8% More than 8 hours
The next Instant Poll question is, "Has your company participated in Microsoft SQL Server beta programs?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, several times, 2) Yes, but only once or twice, 3) No, but we'd like to, or 4) No, and we're not interested.
SPONSOR: NEED 24 X 7 AVAILABILITY?
High-availability networks, systems, and applications are critical to every business. Sign up for our (free!) Webinar taking place on May 14 (sponsored by MKS), and find out how to achieve 24 x 7 availability on Windows 2000. Windows & .NET Magazine author Tim Huckaby shares his expertise on load balancing, monitoring, and more. Register today!
3. READER CHALLENGE
(contributed by SQL Server MVP Umachandar Jayachandran, firstname.lastname@example.org)
Congratulations to Peter Lin, software engineer at Energistic in Port Klang, Malaysia, and Walid Rifai, software developer for Engineering Design and Manufacturing in Beirut, Lebanon. Peter won first prize of $100 for the best solution to the May Reader Challenge, "Simplifying the Search." Walid won second prize of $50. You can find a recap of the problem and the solution to the May Reader Challenge on the SQL Server Magazine Web site.
Now, test your SQL Server savvy in the June Reader Challenge, "Passing the Values" (below). Submit your solution in an email message to email@example.com by May 15. 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 database architect for a company that uses SQL Server 2000, Michael has been assigned the task of designing routines for auditing changes that the company's Web application makes to the data. He decides to use triggers to implement the auditing routines and to use stored procedures for performing all data-manipulation operations in the application. Let's simulate Robert's scenario by using the following four CREATE statements to create two sample tables with relevant columns, a trigger, and a stored procedure:
CREATE TABLE Test ( i int primary key ) CREATE TABLE TestAudit ( i int, Source varchar(10) ) GO CREATE TRIGGER AuditTestInsert ON Test AFTER INSERT AS BEGIN DECLARE @CallingProc varchar(128) — Robert needs to initialize this variable DECLARE @Source varchar(30) — Robert needs to initialize this variable INSERT INTO TestAudit ( i ) SELECT i from inserted END GO
CREATE PROCEDURE InsertTest ( @Source varchar(30), @i int ) AS INSERT INTO test values( 1 ) GO
For a specific business purpose, Michael wants to learn two kinds of information: from within the trigger code, he wants to find out the name of the stored procedure that fires the INSERT trigger, and from the audit trigger, he wants to discover the value of the @Source parameter that the application passes to the stored procedure each time the trigger fires. Michael expects that after the Web application calls the stored procedure with the following parameters:
EXEC InsertTest 'App1', 1
the audit table will contain the newly inserted row with its App1 source value. However, the row appears in the audit table without the information he needs, so Michael has to modify the code—preferably with minimal changes—so that it populates all the necessary columns. Help Michael design an efficient solution for passing values from the stored procedure to the trigger.
Exclusive in-depth articles, tips, tricks, and code samples all at your fingertips. Content you can't get anywhere else—brought to you by the SQL Server experts you trust such as Kalen Delaney, Itzik Ben-Gan, and others. Increase your productivity today! Go to the following URL.
More than 40 solution providers will showcase their latest innovations this month at XML Europe 2002, May 20 through 23, 2002, in Barcelona, Spain. See the latest advances in XML products and services through hands-on demonstrations and displays at the Expo, or take advantage of 3 multi-track days of presentations from experts in the XML field. For complete details and to register, call 703-837-1070 or visit
5. HOT RELEASE (ADVERTISEMENT)
IntelliVIEW is an elegant, XML-based Reporting Solution to query, view and publish SQL Server data. IntelliVIEW is lightweight and scalable and with its incredible price-performance, offers an ROI 180% greater than our nearest competitor (Crystal Reports/Brio/BO etc.)
Download the free IntelliVIEW client now!
Sharpen your T-SQL skills by learning how to use T-SQL to create an abstract drawing. Itzik Ben-Gan demonstrates this mind bender in "In the Picture," which appears in the May 2002 issue of SQL Server Magazine and is available online at the following URL:
Tracy is trying to establish connections between one local server and three remote servers, but after she sets up the linked servers, she's unable to query the other servers through one of the remote servers. 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've noticed that the SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS, but SQL Server 2000's default code page is Latin1_General_CI_AS. This change makes a significant difference when I need to restore SQL Server 7.0 databases into SQL Server 2000. How can I get around this change?
A. SQL Server is moving away from its legacy self-defined code page support and toward code pages that match the Windows code pages that you choose when you set Windows locale IDs. For more information about code pages, see "How Character Data Is Stored" in SQL Server 2000 Books Online (BOL). When you upgrade from SQL Server 7.0 to SQL Server 2000, by default you keep your existing sort order and code page. However, when you perform a clean installation of SQL Server 2000, you get the new sort order and code page. If you attach a database to a SQL Server 2000 instance, SQL Server keeps whatever the collation was on the database when you created it (i.e., you keep the SQL Server 7.0 code page if you attach a SQL Server 7.0 database to SQL Server 2000). Because SQL Server is moving toward the new default code page, we recommend that if you're upgrading by doing a clean installation, then attaching a database, you need to pick a collation at SQL Server 2000 installation time that matches your SQL Server 7.0 collation. You can determine this collation by performing an upgrade (e.g., an upgrade of the Pubs database) from your SQL Server 7.0 installation, then looking at the collation that results when you query the serverproperty ('collation') builtin. Microsoft has made this change to reduce the scope of differing results that incompatible SQL Server and Windows code pages cause.
Send your technical questions to firstname.lastname@example.org.
7. NEW AND IMPROVED
(contributed by Carolyn Mascarenas, email@example.com)
MIDAR announced the Multi-Tier Code Generator, software that examines your database and automatically creates three complete Visual Basic (VB) projects for presentation, business logic, and database tiers. The software automatically generates SQL Server scripts to interface with the VB tier. The generated VB code uses COM interfaces. The software supports SQL Server 2000 and 7.0 and costs $31 per month. Contact MIDAR at firstname.lastname@example.org.
IS Decisions released WinReporter, software that retrieves network data to facilitate network management. You can use WinReporter to find all servers that don't have the latest service pack installed, find old computers in the network, count installed software, detect unregistered software, get information about available disk space, list groups and users by domain or computer, and list RAM sizes. The software supports SQL Server, Microsoft Access, and Oracle databases. For pricing, contact IS Decisions 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.
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.