It can be with HP ProLiant Essentials... a portfolio of modular, flexible software tools that expand the capabilities of ProLiant servers. The ProLiant Essentials Foundation Pack contains the essential software needed to quickly and easily deploy a ProLiant server into a managed infrastructure. ProLiant Essentials Value Packs, optional software offerings, let you selectively extend and enhance the potential of your current IT infrastructure. Using Adaptive Infrastructure technologies, value packs facilitate an array of advanced provisioning, deployment, availability and management capabilities within your SQL Server environment. For more information, visithttp://www.hp.com/servers/proliantessentials

September 26, 2002—In this issue:

1. COMMENTARY

Simplify Mobile Development with CE

2. SQL SERVER NEWS AND VIEWS

Results of Previous Instant Poll: Guidance from Microsoft

New Instant Poll: Developing with .NET

3. ANNOUNCEMENTS

Get Realtime Training with Kalen Delaney!

Immediate Access to T-SQL Solutions!

4. HOT RELEASES (ADVERTISEMENTS)

Automate File Replication Tasks

Unisys ES7000 Server

SQL Server Magazine LIVE!

5. RESOURCES

What's New in SQL Server Magazine: Using ADO MD with .NET

Hot Thread: Unencrypting Stored Procedures

Tip: A Set-Based Way to Find Specific Rows

6. NEW AND IMPROVED

Manage SQL Server Settings

Create Web Pages

7. CONTACT US

See this section for a list of ways to contact us.

1. COMMENTARY

SIMPLIFY MOBILE DEVELOPMENT WITH CE

(contributed by Brian Moran, news editor, brianm@sqlmag.com) Last week, Microsoft announced availability of SQL Server 2000 Windows CE Edition (SQL Server CE) 2.0. The new release includes updates that will improve your query capabilities and let you set security and connectivity options more quickly. But the most important change is that SQL Server CE is now integrated with the .NET Compact Framework and Visual Studio .NET. Combined, these tools provide a desktop and mobile-device programming model that's unified with the .NET Framework and that makes building applications for mobile devices easier than ever.

Pocket computing for business applications hasn't taken off as many folks expected. One of the biggest hurdles that pocket computing has faced is that few people are writing native applications for Windows CE devices. But the advent of unified programming models for desktop (i.e., connected) and mobile applications will make it easier for people to write once and deploy twice. In other words, a developer can use a mainstream desktop as an application's initial and primary development platform, but by making a relatively simple port, the developer can also deploy the application on mobile devices. I think this integrated development environment will boost the adoption of mobile applications in corporate America. And Microsoft agrees with me. The company says it expects consistency among mobile technologies to encourage more Visual Studio programmers to develop mobile applications. And Microsoft expects the increased developer engagement to "translate into more \[and\] better mobile applications for enterprise users."

With the demand for mobile applications expected to increase, mobile database computing might well be a high-growth specialization for SQL Server professionals to consider. To learn more about this exciting technology, you can download SQL Server CE 2.0 from a link on the product home page at http://www.microsoft.com/sql/CE/default.asp . This site also includes links to product overviews and a marketing-oriented evaluation guide. You'll find more technical information on the SQL Server CE Technical Resources site at http://www.microsoft.com/sql/ce/techinfo/default.asp . This site includes complete product documentation, an FAQ section, and a variety of white papers to help you get started with SQL Server CE.

MORE SESSIONS, BEST QUALITY, UNBEATABLE VALUE!

SQL Server Magazine LIVE! provides you with an in-depth menu of sessions to choose from covering the most advanced topics down to the basics. On Oct. 27-30, 2002, at the Hyatt Grand Cypress Resort in Orlando, Florida, we will provide relief for those who hunger for the latest insights and how-to information necessary to build and maintain scalable and reliable databases that drive mission-critical applications. Increase your productivity with shortcuts, tips and tricks you'll learn only at this conference; discover the best ways to integrate SQL Server with Web applications; solve tough interoperability issues; enhance database administration with new tools; benefit from third-party gurus and magazine authors who'll share real-world experiences; and much more! For additional information and to register, go tohttps://secure.win2000mag.com/events/sql_register.asp

Poll for the question, "How do you prefer to get how-to or best-practices information from Microsoft?" Here are the results (+/- 1 percent) from the 180 votes:

52% Informational Web sites (e.g., Developer Centers)

42% White papers

04% Web seminars

02% Live seminars

01% Consulting services

NEW INSTANT POLL: Developing with .NET

The next Instant Poll question is "What types of production applications are you creating through the .NET Framework?" Go to the SQL Server Magazine Web site and submit your vote for 1) Web or ASP.NET applications, 2) Windows-based Visual Basic .NET applications, 3) Windows-based C# applications, 4) Windows-based C++ applications, or 5) I'm not using .NET for my production applications. http://www.sqlmag.com

SPONSOR: INCREASE SERVER UPTIME AND STABILITY - FREE

Diskeeper(R) Lite, the high-speed manual introduction to the popular automatic defragmenter Diskeeper 7.0, is available for immediate download.

Fragmentation is the root cause of many system problems, server slowdown being only one of them. Eliminating fragmentation is the key to increasing network performance. Diskeeper Lite was built with the same advanced defragmentation technology as Diskeeper 7.0 and can be used on Windows(R) 98 on up.

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

GET REALTIME TRAINING WITH KALEN DELANEY!

Fine-tune your SQL Server performance at the next SQL Server Magazine Web seminar, "Analyzing Locking and Blocking," taught by Kalen Delaney, noted SQL Server author and Microsoft MVP, on October 1 at 1:00 PM Eastern time. For more information or to register for this event, sponsored by Precise Software Solutions, go to http://secure.duke.com/nt/sqlmag/webseminar/index.cfm?code=&pc=kdei322i14

IMMEDIATE ACCESS TO T-SQL SOLUTIONS!

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. http://www.tsqlsolutions.com

4. HOT RELEASES (ADVERTISEMENTS)

AUTOMATE FILE REPLICATION TASKS

OpalisRendezVous offers inbound collection and outbound distribution of files to ensure directories have the most up-to-date data. It complements disaster recovery and data protection strategies by reducing data loss. Transfer files across the network using copy, Internet and FTP methods. Download and get FREE online training: http://www.opalis.com?source=sql-09-2002rdv

UNISYS ES7000 SERVER

Get better performance for Microsoft SQL Server databases on a Unisys ES7000 server. The Unisys ES7000 database server couples Microsoft/Intel economics with a robust and scalable server architecture. Delivering more headroom for growth, faster response times and superior availability. http://www.unisys.com/simplify/?b=01nl01ss

SQL SERVER MAGAZINE LIVE!

SQL Server Magazine LIVE! will co-locate with Microsoft ASP.NET Connections and VS.NET Connections. Register now, save $2,990. Access all three events—160+ sessions. Chock-full of real-world tips and new ideas to give you the edge. https://secure.win2000mag.com/events/sql_register.asp

5. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: USING ADO MD WITH .NET

Have you tried making .NET and ADO MD work together? SQL Server Magazine contributing editor Russ Whitney has—and he found that it's not straightforward. In "Using ADO MD with .NET," he tells you about some of the problems he ran into and some ways you can avoid similar complications when you try to integrate .NET with your existing COM applications. This article appears in the September 2002 issue of SQL Server Magazine and is available online at http://www.sqlmag.com/articles/index.cfm?articleid=25800

HOT THREAD: UNENCRYPTING STORED PROCEDURES

Kel_best's organization has purchased a SQL Server 7.0 database, but the database supplier has encrypted the stored procedures. Kel_best wants to know if you can unencrypt stored procedures once they're encrypted. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL: http://www.sqlmag.com/forums/messageview.cfm?catid=8&threadid=8892

TIP: A SET-BASED WAY TO FIND SPECIFIC ROWS

(contributed by Brian Moran, brianm@sqlmag.com)

Q. I have a table that includes a single-column primary key called IdValue. Given a particular value for IdValue, I want to find the table rows that are immediately before and after my target value (assuming the result set is sorted by IdValue). How can I avoid using cursors and get the result I want with a set-based approach?

A. T-SQL is a set-based language and doesn't easily lend itself to solutions that assume positioning of particular rows within a result set. However, the performance of server-side ANSI T-SQL cursors is usually much worse than the performance of a set-based solution, so learning different techniques for solving problems such as this is important.

Let's use the Northwind database's Orders table for an example. We'll restate the problem like this: How can I find the row immediately before and immediately after a particular row in the Orders table? We'll assume we're ordering the result set on the OrderId column.

Creative use of SQL Server's MIN(), MAX(), and TOP capabilities can help you deal with positional result set questions such as this one. Listing 1 and Listing 2, at the end of this answer, show two similar but slightly different approaches for solving the problem. Listing 1 provides a general solution because it works with MIN() and MAX() end points for @TargetOrder. However, I wanted to demonstrate the flexibility that TOP can provide in some cases. Note that the query in Listing 2 returns an empty set when you provide the minimum OrderId (10248) from the Orders table as an argument because the query assumes a row will always exist before the @TargetOrder row.

You typically have more than one way to generate a result set, and one approach is usually more efficient than the others. When you evaluate SHOWPLAN and SET STATISTICS IO information for both of these examples, you'll see that Listing 2, which uses the TOP statement, is slightly more efficient than Listing 1. The differences are small because the sample data sets are small, but it's important to test the performance of different query approaches when more than one option exists.

LISTING 1: Using the MIN() and MAX() Functions with the OR Keyword to Find Target Rows DECLARE @TargetOrder int SET @TargetOrder = 10330 SELECT OrderId FROM Orders WHERE OrderId = @TargetOrder OR OrderId = (SELECT MAX(OrderId) FROM orders WHERE OrderId < @TargetOrder) OR OrderId = (SELECT MIN(OrderId) FROM orders WHERE OrderId > @TargetOrder)

LISTING 2: Using the TOP Keyword to Find Target Rows SELECT TOP 3 * FROM orders WHERE OrderId >= (SELECT MAX(OrderId) FROM orders WHERE OrderId < @TargetOrder) ORDER BY OrderId

Send your technical questions to savvy@sqlmag.com.

6. NEW AND IMPROVED

(contributed by Carolyn Mader, products@sqlmag.com)

MANAGE SQL SERVER SETTINGS

Ecora Software announced Configuration Auditor for SQL Server. The software discovers, documents, and tracks changes to configuration settings on servers. You can use Configuration Auditor to enforce configuration compliance by comparing the configuration of a standard server against other servers in the environment. The software lets you see which users are assigned to which server roles. And you can collate information about all your SQL Server databases, tables, and permissions. For pricing, contact Ecora Software at 603-436-1616 or 877-923-2672. http://www.ecora.com

CREATE WEB PAGES

TheDevShop announced dbQwikSite 2.0, software that can create Web pages by using your SQL Server database data. The software can produce Web catalogs, lists, and online store sites. The program connects to any database through ADO or ODBC. The preview feature lets you see how the final generated pages will appear with the data. The Standard version produces static HTML sites. The Pro version adds Active Server Pages (ASP) code to search, add, edit, and delete data and let you manage server data remotely. DbQwikSite runs on Windows XP, Windows 2000, Windows NT, Windows Me, and Windows 9x systems. The Standard version costs $79, and the Pro version costs $129. Contact TheDevShop at sales@thedevshop.com. http://www.dbqwiksite.com

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.http://www.sqlmag.com/sub.cfm?code=ssei211x1y

SQL Server Magazine LIVE!'s full conference schedule is now online.

This real-world conference, packed with best practices, is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job. Register now and access concurrently run Microsoft ASP.NET Connections and VS.NET Connections for FREE!http://www.sqlmagLIVE.com

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More