Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at savvy@sqlmag.com.
Tracing Events on a Selected SQL Server Machine
I'm trying to use SQL Server Profiler to trace events on a particular SQL Server 7.0 machine, but the trace always shows events that are occurring on the first server that appears in Profiler's SQL Server drop-down list. When I select the SQL Server machine that I want to trace, Profiler ignores the selection and traces the first server in the list. How can I force Profiler to trace my selected SQL Server?
You've stumbled across a bug that Microsoft introduced in SQL Server 7.0 Service Pack 3 (SP3). If you've used one of the SQL Server client utilities, such as Enterprise Manager or Profiler, to register more than one SQL Server and you've installed SQL Server 7.0 SP3, you might see the Profiler behavior that you outlined in your question. The Microsoft article "FIX: SQL Server Profiler and SQL Server Agent Alerts May Fail to Work After Installing SQL Server 7.0 SP3" describes this bug and provides information about downloading and applying the hotfix.
Retrieving Specified Rows Within a Resultset
I understand how to use the TOP clause within a SELECT statement to retrieve the first n rows of a resultset, but I have a business need to return the second set of n rows. For example, rather than returning the first 10 rows, I want to return rows 11 through 20. What's a good way to retrieve a second set of rows?
SQL is a set-based language, which means that it doesn't support working with specific rows within a resultset well. Using a server-side T-SQL cursor to solve the problem of get rows 11-20 is easy, but I don't recommend that solution. Most SQL experts agree that server-side T-SQL cursors are horribly inefficient. Although T-SQL cursors aren't a problem when you're working with a query that you run infrequently and that doesn't operate on many rows, I recommend that you avoid server-side T-SQL cursors in favor of set-based solutions whenever possible. A set-based solution is a solution that you can express by using single T-SQL commands that operate on a range of rows to return one resultset without relying on the use of a cursor.
Sometimes, taking a problem that lends itself to a row-by-row, iterative cursor-based solution and creating a set-based T-SQL solution is difficult. However, you can usually find a creative way to solve the problem. The T-SQL code in Listing 1 shows one example of how you can return rows numbered 11-20 from a resultset without using a T-SQL cursor.
The query in Listing 2, page 20, shows the 10 orders from northwind..orders with the lowest total order amounts. This query might be a bit complex to follow if you're a SQL novice, so let's walk through it quickly. You reuse the query from Listing 1 twice in Listing 2. You use it the first time to generate a list of the 10 order IDs with the lowest total order amounts, which you then use as the subquery that the NOT IN clause operates on. The outer query in Listing 2 is essentially the same query as in Listing 1 except that you filter out the order IDs that you've already counted within the subquery.
You can choose from a variety of set-based operations for accomplishing this same task. For this answer, I didn't worry about query optimization, and the NOT IN clause isn't always the most efficient query construction. However, this construction makes the logic and query easier to follow than some alternative query formulations; it also makes demonstrating the primary point of this question—avoiding row-by-row T-SQL cursor operations—easier.
Prev. page
[1]
2
next page 
