Questions, Answers , and Tips About SQL Server

Q: Does SQL Server 7.0 require Internet Explorer (IE) 4.01?

SQL Server 7.0 needs IE 4.01 to run the Microsoft Management Console (MMC—through which you run SQL Enterprise Manager), Books Online (BOL), and SQL Server's other management tools. However, you don't have to install IE 4.01 if you don't want these tools—you can install the basic connectivity components without installing IE 4.01.

Q: Can I install SQL Server 7.0 on a Windows NT Server 4.0, Terminal Server Edition system?

Microsoft doesn't officially support installing SQL Server 7.0 on a Terminal Server system; however, we suspect installation is possible. We found the following advice about installing applications on Terminal Server systems at John Savill's Windows NT Frequently Asked Questions Web site (http://www.ntfaq.com).

To ensure that all your Terminal Server system's users have access to applications on the system, you need to use a certain method to install the applications. Terminal Server has two modes: Execute and Install. By default, all users log on in Execute mode, which lets them run programs. As the administrator, you need to set Terminal Server to Install mode when you want to install an application that all users can access. If you use the Add/Remote Programs Control Panel applet to install an application, Terminal Server automatically sets the mode to Install during the installation and resets the mode to Execute after the installation. To manually change to Install mode, just type

c:> change user /install

To return to Execute mode after the installation, type

c:> change user /execute

To check your system's current mode, type

c:> change user /query

I'll walk you through an example installation using the Add/Remove Programs applet to install WinZip on a Terminal Server system. In the Add/Remove Programs Properties dialog box, select Install on the Install/Uninstall tab. The system prompts you to provide the setup media; click Next. For this example, you don't need to insert a CD-ROM or disk because WinZip exists on the hard disk. The system will look for the media and not find it, so it will give you the option to browse and find the executable file on the hard disk. After you find and select WinZip, click Next, select All users begin with common application settings, and click Next to start the installation. After setup is complete, click Next in the Install/Uninstall tab, then click Finish. All Terminal Server users can now access WinZip.

Q: Does SQL Server 7.0 support the Database Consistency Checker (DBCC) MEMUSAGE statement? Also, how do I use DBCC to fix sequencing problems associated with keys that rely on SQL Server's identity feature?

In the Microsoft article "BUG: DBCC MEMUSAGE Is Not Supported in SQL Server 7.0" (http://support.microsoft.com/ support/kb/articles/q196/6/29.asp), the company states, "The DBCC MEMUSAGE statement is not supported in SQL Server 7.0. Executing it on servers running heavy loads with large databases might cause the server to stop responding." Microsoft advises using Windows NT Performance Monitor counters to monitor memory usage.

To answer your second question, the command you're looking for is DBCC CHECKIDENT. The SQL Server 6.5 CHECKIDENT syntax is

CHECKIDENT [()]

This command checks the current identity value and compares it with the maximum value in the identity column. If the current identity value is invalid, SQL Server will reset it using the maximum value in the identity column. When a PRIMARY KEY or UNIQUE KEY constraint exists on an identity column, invalid identity information causes SQL Server error message 2627.

However, Microsoft significantly enhanced CHECKIDENT in SQL Server 7.0. Version 7.0 offers new capabilities through its extended syntax:

DBCC( '' [, { NORESEED |   {RESEED [, ]} }])

Table 1 shows the Books Online (BOL) summary of CHECKIDENT effects, which depend on the parameters you supply.

Q: Does SQL Server 6.5 have an Easter egg?

The easiest way to see the Easter egg is to use ISQL/w. You can connect to a server or click Cancel, then Help, About. Press F1 and SQL Server 6.5 will reward you with the Easter egg—you can see the ISQL/w development team. You can also reach this Easter egg from Enterprise Manager:

Microsoft swears that SQL Server 7.0 doesn't have any Easter eggs. Can any readers tell us where to find one?

Q: Where can I find Microsoft-hosted SQL Server discussion groups?

Most of you monitor the Windows NT Magazine SQL Server online community discussion threads and Steve Wynkoop's SQL Server mailing list (http://www.swynk.com) for ideas and tips. But many of you don't take advantage of Microsoft's newsgroups, which Microsoft-selected Most Valuable Professionals (MVPs) monitor regularly. The SQL Server newsgroups you can join are

The following Microsoft MVPs monitor these newsgroups:

Q: My SQL Server 7.0 system is sending me error messages that Books Online (BOL)doesn't document. Why aren't these errors in BOL?

Microsoft added error messages to SQL Server 7.0 after the company published BOL. Table 2 lists the additional error messages.

Q: Does SQL Server 7.0 have any new reserved keywords?

Reserved keywords are words you shouldn't use for user-defined database objects, such as table names, column names, or variables. Ensure that none of your object names or variables use the keywords BACKUP, DENY, PERCENT, RESTORE, or TOP.

Q: Does SQL Server 7.0 handle Year 2000 (Y2K) problems better than SQL Server 6.5 handles them?

Yes, SQL Server 7.0 offers two new features that improve Y2K functionality. First, the new two-digit year cutoff option lets you change the default value at which SQL Server interprets two-digit years as four-digit years. According to Books Online (BOL), you use the two-digit year cutoff option to specify an integer from 1753 to 9999 that represents the cutoff year for SQL Server 7.0 to interpret two-digit years as four-digit years. SQL Server 7.0 interprets a two-digit year that is less than or equal to the last two digits of the cutoff year as belonging to the same century as the cutoff year. SQL Server interprets a two-digit year that is greater than the last two digits of the cutoff year as belonging to the century that precedes the cutoff year. For example, if the two-digit year cutoff is 2049 (the default setting), SQL Server 7.0 interprets the two-digit year 49 as 2049 and the two-digit year 50 as 1950.

Trace flag 8816 is another helpful feature that you can use to track rogue database applications that contain a two-digit date reference rather than a four-digit date reference. This trace flag logs every conversion from a two-digit year to four-digit year. You use the Database Consistency Checker (DBCC) Traceon (trace flag number) and DBCC Traceoff (trace flag number) commands to set trace flags on and off. For example, the script

dbcc traceon(8816)goSET dateformat mdygoCREATE table birthday   (name nvarchar(50), birthdate datetime)goINSERT birthday(name, birthdate)   values('Chris Preston','7/30/47')go

causes SQL Server 7.0 to record the following message in the SQL Server error log:

1998-12-22 18:36:51.23 spid7   2-digit year 47 convertedto 2047

This message tells you that SQL Server is behaving correctly and the error is an application error. To avoid this problem, enter the four-digit year (e.g., 1947) or decrease the two-digit year cutoff value. Microsoft recommends that all programs use the four-digit year for all date operations.

You can find more information about SQL Server 7.0's Y2K capabilities in the Microsoft article "INF: Trace Flag 8816 to Help Year 2000 Conversion" (http://support.microsoft.com/ support/kb/ articles/ q198/4/16.asp). BOL contains information about DBCC and trace flags.