Questions, Answers, and Tips - 23 May 2001

Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at [email protected].

I have a Comments field in a customer service database that captures the data our telephone representatives type. The representatives often press the Enter key as they type, so the comments have many carriage returns. Can I strip the carriage returns to clean things up for a data transfer process that I run?

T-SQL doesn't provide the world's most sophisticated string-manipulation commands, but the REPLACE and CHAR commands can help you solve this problem. The REPLACE command replaces all occurrences of the second given string expression in the first string expression with a third expression:

REPLACE ( 'string_expression1' ,'string_expression2' ,'string_expression3' )

The CHAR command converts an integer ASCII code to a character:

CHAR ( integer_expression )

The solution to your problem becomes clear if you understand that pressing the Enter key typically creates two special control characters—line feed and carriage return—that the ASCII codes 10 and 13 represent, respectively.

With that in mind, applying Listing 1's T-SQL script to your particular need should be simple. If you run the following command

SELECTTextId,TextValueFROMBadTextData

you'll receive a resultset that includes the line feed, as Figure 1 shows. Then, you can use the REPLACE and CHAR commands to remove the hidden control characters and display all the text on one line, as the following query shows:

SELECTTextId,replace ( replace(TextValue, char(10),''), char(13), '') ModifiedTextValueFROMBadTextData

Figure 2 shows the results. Make sure Query Analyzer is in Text mode rather than Grid mode when you run the queries. If the output is displayed in Grid mode, you won't see the embedded carriage return when the second query runs. (Don't forget to run the DROP TABLE command after you run through the example.)

I need to capture the SQL trace of a process that I scheduled to start daily at 2:00 A.M. I don't want to dial in at 2:00 a.m., but I can't figure out how to set up a trace programmatically by using the SQL trace system stored procedures. Can I schedule a trace through the SQL Server Profiler GUI?

I have good news and bad news. The bad news is that SQL Server has no interface for directly scheduling a trace to run from the Profiler GUI. The good news is that you don't have to get up in the middle of the night, and you don't have to struggle through SQL Server Books Online's (BOL's) poor trace procedure documentation. The Profiler GUI provides a convenient mechanism for scripting the definition of an existing trace into a SQL file that makes the proper calls to the SQL trace procedures. Simply create a trace that includes the events, data columns, and filters that you need. From the File menu, select the Script Trace option, then select SQL Server 2000 or SQL Server 7.0. Trace procedures have changed dramatically between SQL Server 7.0 and SQL Server 2000, but the new Profiler GUI knows how to create the proper trace calls for either version. Now your trace file includes standard SQL statements that you can use as a starting point to create a job that you can schedule to run through SQL Server Agent or another scheduling tool. If you want the script to be useful as a general-purpose task that you can run at the same time every night, you need to take one final step. You have to edit the script that Profiler generates to ensure that the script stops when you want it to.

With Microsoft Access, I can easily return just the first or last row of a resultset by using the FIRST and LAST keywords, but how can I accomplish the same task in SQL Server?

ANSI SQL doesn't define a rank() function or let you specify rows directly in a resultset by a logical row number, and T-SQL hasn't implemented a specific function to help solve this problem. But you can easily use the TOP keyword to achieve the same result. TOP lets you return just the TOP N rows of a resultset where N is a specific number of rows or a percentage. Specifying TOP where N = 1 lets you return the first row of a resultset. If you can order the resultset by using the ORDER BY clause, you can capture the last row simply by changing the default ordering direction from ascending to descending, as the following example shows. Let's assume that you want to retrieve the most recent row from the Northwind Orders table with an ORDER BY clause that's based on OrderDate. The query in Listing 2 shows you how to capture the row. Changing DESC to ASC gives you the oldest row from the Orders table according to OrderDate. The query shows that you can easily retrieve the first or last row simply by specifying which direction you want to order the results by, then selecting the first row by using the TOP 1 command.

My question is a follow-up to the tip "Connecting to a SQL Server 2000 Named Instance from MDAC 2.5," February 2001. I want to create an alias called MySQLAlias that points to a SQL Server instance that's already registered with SQL Server under its default name of MyMachineMySQL. Can I reference the same SQL Server instance by using two different names?

Yes, you can use an alias, but before I show you how easily you can set one up, let me describe a real-world scenario in which creating two names for one SQL Server instance is helpful. This technique is especially handy when a DBA with systems administrator (sa) privileges wants to register one instance of SQL Server in Enterprise Manager under two different sets of security credentials. For example, the DBA might want to register an instance under the sa login as well as under a developer-level login to test permissions in different circumstances. Enterprise Manager doesn't let you reregister the instance under the same name, but you can create a client-side alias for the SQL Server instance. Enterprise Manager then lets you register both the real name of the instance and the alias.

Setup is easy. You can define the alias by using the SQL Server Client Network utility as follows: Open the utility, and from the Alias tab, click Add and enter the appropriate information. A full description of the Client Network utility is beyond the scope of this answer, but let's assume that you want to create the alias outlined in the original question by using the TCP/IP Network Library. In this case, you select TCP/IP as the network library, then enter MySQLAlias in the Server Alias field and MyMachineMySQL in the Server Name field. Then, you can register the new alias in Enterprise Manager under a different set of user credentials.