| Downloads |
|---|
| 21391.zip |
Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected].
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.
Moving Databases Between SQL Server 2000 Editions
I used SQL Server 2000 Personal Edition to create a database. Can I easily move this database to a new SQL Server machine that's running SQL Server 2000 Enterprise Edition? Can I also go from Enterprise Edition to Personal Edition?
Several important feature differences exist between the different SQL Server 2000 editions, but the underlying data structures in the database are the same. You can easily move databases between the different editions by using either basic backup and restore techniques or the sp_detach_db and sp_attach_db stored procedures. In fact, you can even move a SQL Server 7.0 database to SQL Server 2000 (although this move is a one-way process—you can't go from SQL Server 2000 to SQL Server 7.0).
Because the various SQL Server 2000 editions have different feature sets, queries might behave differently while running on a particular edition. The "Features Supported by the Editions of SQL Server 2000" topic in SQL Server 2000 Books Online (BOL) provides a helpful discussion of what feature differences you'll find.
Connecting to a SQL Server Instance Running Outside a Firewall
I'm having a hard time connecting to a SQL Server 2000 instance that's running on the other side of a firewall. I've opened port 1433 on the firewall, and I've used the Client Network Utility to create a server alias that references the correct TCP/IP address. I can ping the SQL Server machine by IP address. Using this same technique, I can connect to a SQL Server 7.0 machine running on the same network segment behind the same firewall. What am I doing wrong?
SQL Server 7.0 always chooses port 1433 as the default port for TCP/IP connections. However, SQL Server 2000 doesn't, and connecting to SQL Server 2000 behind a firewall is slightly more complex than connecting to SQL Server 7.0.
SQL Server 2000 supports multiple instances on the same physical machine, which can have just one TCP/IP address. Of course, multiple SQL Server instances can't share one port number (e.g., 1433) at the same time, which means that each SQL Server instance must have a separate port number. Having to hard-code the port number for every SQL Server instance within every client workstation is inconvenient, so SQL Server Net-Libraries need a mechanism for querying a particular machine (by server name or IP address) and determining which TCP/IP ports SQL Server instances are using at that time. Here's how SQL Server 2000 manages this problem. (You can find details about this problem in the "Controlling Net-Libraries and Communications Addresses" topic of SQL Server 2000 Books Online—BOL.)
When an application requests a connection to a remote computer, dbnetlib.dll opens a connection to UDP port 1434 on the computer network name or IP address that the connection specifies. All computers running an instance of SQL Server 2000 listen on this port. When a client dbnetlib.dll connects to this port, the server returns a packet that lists all the instances running on the server. For each instance, the packet reports the server Net-Libraries, network addresses, and port number on which the instance is listening. After the dbnetlib.dll on the application computer receives this packet, that computer chooses a Net-Library that's enabled on both the application computer and the instance of SQL Server and makes a connection to SQL Server. BOL warns, "You cannot assign UDP port 1434 to an application other than SQL Server on computers running instances of SQL Server 2000. Network administrators managing network filters must allow communications on UDP port 1434 to enable SQL Server 2000 connections to pass through the filter." So, you must open UDP port 1434 to talk to a SQL Server 2000 box on the other side of a firewall.
Here's the second potential problem you might be facing. SQL Server 2000 doesn't default to TCP/IP port 1433 for named instances. Port numbers for SQL Server named instances are chosen when the instance is first installed. The SQL Server installation manager randomly picks a port that the server isn't using. The SQL Server named instance then listens on that port forever unless you manually change it. You can change the port number or simply verify which port the server is configured to listen on by opening the Server Network Utility, selecting the TCP/IP protocol, selecting the General tab, then clicking Properties.
