Questions, Answers, and Tips About SQL Server - 05 Oct 1999

How do you read the code behind the system stored procedures (sps)?

Reading the Transact SQL (T-SQL) behind SQL Server's system sps is one of the best ways to learn T-SQL. To view a stored procedure's definition from Enterprise Manager, expand a server group, a server, Databases, Master, then Stored Procedures. Then, double-click the sp you're interested in.

I installed SQL Server 7.0 Enterprise Edition on a machine with 4GB of memory. Windows NT Task Manager reports 2.9GB of available memory. All my applications don't use more than 1.9GB of memory total, yet SQL Server won't use more than 1.9GB. How can I make SQL Server use as much as 3GB of memory?

After you install NT Server, Enterprise Edition (NTS/E), you need to modify the boot.ini file to enable 4GB. To enable 4GB, add the /3GB parameter to the startup line as the following example shows:

[boot loader]timeout=30default=multi(0)disk(0)rdisk(0)partition(2)WINNT[operating systems]multi(0)disk(0)rdisk(0)partition(2)WINNT="Windows NT Server,Enterprise Edition Version 4.00" /3GBmulti(0)disk(0)rdisk(0)partition(2)WINNT="Windows NT Server,Enterprise Edition Version4.00 [VGA mode]" /basevideo/sos/basevideo /sos

The following excerpt from SQL Server Books Online (BOL) clarifies this confusing solution: "Windows NT Enterprise Edition provides a 4GB virtual address space for each Microsoft Win32 application, the lower 3GB of which is private per process and available for application use. The upper 1GB is reserved for system use."

In SQL Server 6.5, what is the purpose of the C:sql.log file, and is deleting it safe?

C:sql.log is the default filename that SQL Server 6.5 uses when ODBC tracing is on, and deleting it is safe. You can configure ODBC tracing via code from within an ODBC application or from the Control Panel ODBC applet. Usually, you want ODBC tracing turned off because it adds a significant amount of overhead. However, ODBC tracing is useful when you're trying to isolate connectivity or performance problems associated with a particular client system, application, or driver.

Can I pass a variable to a SELECT statement that uses the TOP keyword within a stored procedure (sp)?

The TOP operator doesn't support the ability to specify a variable, but you can use the execute (EXEC) keyword to work around this problem and to create a dynamic SQL statement, as the following Transact SQL (T-SQL) scripts show:

DECLARE @Rows intDECLARE @SQlString varchar(255)SELECT @Rows = 1SELECT @SQlString = 'SELECT TOP' + str(@Rows) + ' * FROM authors'EXEC( @SQlString)

This technique lets you use TOP to specify a variable, and you can easily use this command within an sp.

Another method to limit the size of a result set is to run SET ROWCOUNT n before you run a statement. SQL Server applies the SET ROWCOUNT limit to rows you're building in the result set before SQL Server evaluates an ORDER BY. Thus, if you specify an ORDER BY, SQL Server still terminates the SELECT statement after it has selected n rows. SQL Server selects n rows, orders those rows, then returns the rows to the client. SET ROWCOUNT remains in effect until you run another SET ROWCOUNT statement, such as SET ROWCOUNT 0, to turn off the option. In addition, SET ROWCOUNT directly supports the ability to reference a numeric variable, so you don't have to jump through hoops to create a dynamic SQL statement.

In SQL Server 6.5 and SQL Server 7.0, SET ROWCOUNT supports the ability to specify a numeric variable. Too bad Microsoft didn't add this support to the TOP operator—let's hope Microsoft will add support in a future release.

Do you know of a Web site that provides a comprehensive list of SQL Server resources?

We're partial to the new SQL Server Site Index at http://www.microsoft.com/ sql/index.htm. This page lists many great SQL Server resources, including the most comprehensive list of SQL Server white papers that we've run across. Figure 1 lists the white papers currently available at this site.

SQL Server 7.0 Books Online (BOL) claims that you can send email messages as large as 8KB via SQL Mail, but I can't send messages of this size. What am I doing wrong?

In SQL Server 6.5 and SQL Server 7.0, the xp_sendmail extended stored procedure (sp) breaks text data into 4096-byte pieces. When you set @width to a value greater than 4096, SQL Server still separates the data with a carriage return and a tab at 4096 bytes.

To work around this problem, use the @attachments parameter that BOL documents to send the text as an attachment. Permissions for xp_sendmail default to a member of the sysadmin fixed server role, but you can grant other users permission. Listing 1 shows the syntax for xp_sendmail, which contains many parameters that most users don't take advantage of.

What is the new sysperfinfo table?

Performance Monitor is a great tool for tracking SQL Server performance counters, but capturing this information in a seamless manner can be difficult unless you keep Performance Monitor running from a Windows NT console. Sysperfinfo is a system table in SQL Server 7.0 that simplifies tracking SQL Server-specific performance counters. This table isn't a real table because it's not backed by persistent disk storage, but sysperfinfo exports most of the counters you can view from Performance Monitor. For a list of counters that sysperfinfo exports, type

"SELECT * FROM master..sysperfinfo"

If you're running SQL Server on an NT platform, this command lists more than 400 counters that sysperfinfo exports. Exporting these counters as a table simplifies tracking information within SQL Server because you can use standard Transact SQL (T-SQL) commands to manipulate the data.