Answers from Microsoft - 26 Jun 2000

Editor's Note:Send your SQL Server questions to Richard Waymire, program manager for SQL Server development at Microsoft, at [email protected].

When I try to drop a database in SQL Server 6.5, I see the error message

Error 3702: [SQL Server] cannot drop the database'databasename' because it is currently in use.

The database has no tables or stored procedures, and the current activity window shows no activity on this database. I'm the only user in the database, and I log in as systems administrator (sa). When I try to set the single-user option for the database, Error 3508: ( ... the usage count is 3 ...) appears. What's happening?

Make sure you've stopped the jobs in the SQLExecutive and that you have no open query windows to the database. Also, you can't be in the database when you try to drop it. Note that you add to the USE count statement simply by opening a query window with a USE mydb statement or a new empty query window with an open selected database option set to that database. If you still can't drop the database, use -m to start SQL Server in single-user mode and drop the database.

My MSSQLServer service won't start because of a login failure that raises the following error message:

Error 1069: occurred while performing this service operationon MSSQLSERVER Service

Also, SQL Services in SQL Server 7.0 won't start. I could reinstall SQL Server 7.0, but what else can I try first?

You don't need to reinstall SQL Server 7.0. Select Control Panel, Services, reset the account and password for the MSSQLServer service, then try to start the service. If the password has expired, SQL Server 7.0 prompts you at login for a new password. To set a new password, you need to log in to the domain using the service account. Enter a password, then use the account in the Control Panel Services applet to reset the service account password for the MSSQLServer service. You might also talk to your domain administrator about setting the expiration option to Password Never Expires so that you can change the password periodically at your convenience without an interruption. You'll also need to verify that the service account is a local administrator or has the required permissions for files and Registry keys as documented in SQL Server Books Online (BOL) (under Installing SQL Server, Running SQL Server Setup, Before Installing, Creating SQL Server Service User Accounts).

I work with a database that requires the user to enter keywords based on credentials such as MCP, MCSE, etc. I store these values in a full-text-enabled field. I want to rank search results on this data by the quality of the match. After reading about this topic in SQL Server Books Online (BOL), I realized that I could use the Containstable facility to weight the values and return the rankings. How does the Containstable facility calculate the ranking values? Does 0 indicate a poor match and 1000 the perfect match?

The Containstable facility computes the ranking value on each query, and the value depends on the current size of the full-text catalog, occurrences of the words, proximity of the words, and the size of each indexed item. The weight is an input value that skews how the facility calculates the rank in favor of one or more of the query terms. A value of 0 indicates a less relevant match. You will never achieve a value of 1000 in a content-based query because 1000 means an exact match and only Boolean matches can be exact. All other matches aren't statistically exact. The Containstable facility is statistically based. And when you're working with statistics, the more items you consider, the more meaningful the results. So the rankings that you receive from a full-text catalog that holds data for only three rows are less meaningful in relation to one another than the rankings you receive on these three rows if the full-text catalog holds data for 1 million rows.

When I try to use Data Transformation Services (DTS) to move data from a SQL Server 7.0 database on one server to another SQL Server 7.0 database on another server across a TCP/IP connection, why do I receive a licensing error message?

Check to see whether one of your databases is a SQL Server Desktop installation. SQL Server Desktop can talk only to a Standard Edition or Enterprise Edition that has per-seat mode licensing. To find out which installation each server is running, run

SELECT @@version

and look at the description. Then, to check the licensing mode, open the server's Control Panel, select Licensing, and look at SQL Server's license mode. Your server needs to be in per-seat mode.

How do I restore specific tables from a database backup in SQL Server 7.0?

The only way to restore individual tables from a backup in SQL Server 7.0 is to use filegroups, then create a table with a clustered index locating the clustered index in the file group. However, when you restore a file group, you need to apply the transaction logs to make the file group consistent with the rest of the database. From a full backup in SQL Server 7.0, you can't restore a single table in any other way. The code in Listing 1 demonstrates how to locate a clustered index in the filegroup myfilegroup.

I recently changed my SQL Server configuration based on the Microsoft white paper Microsoft SQL Server 7.0 Performance Tuning Guide (http:// www.microsoft.com/sql/techinfo/perftuninguide .htm). In my previous configuration, drive F (Raid 0+1) contained the SQL Server system databases, tempdb, and the transaction log for the production database. Drive J (Raid 0+1) contained the production databases with all nonclustered indexes on the same filegroup (Primary). In the current configuration (based on the tuning guide's suggestions), drive F (Raid 0+1) contains the SQL Server system databases, tempdb, the transaction log for the production database, and the nonclustered indexes on a new filegroup. Drive J (Raid 0+1) contains the production database (on the Primary filegroup). After moving the nonclustered indexes from the J drive to the F drive, I'm seeing a dramatic performance decrease. Why?

You need to isolate your transaction logs because the logs will have heavy write activity on most online transaction processing (OLTP) databases. In your case, the disk controller is probably too busy trying to service the log writes and index reads to do both adequately. We'll update the Microsoft SQL Server 7.0 Performance Tuning Guide to include this recommendation.

I recently used a stored procedure in SQL Server 7.0 that didn't behave as I expected. Consider the following code snippet:

declare @zip varchar(20)set @zip="'98001','98002'"select * from ks_list wherezip in (@zip)

This query returns 0 rows. However, if I enter

select * from ks_list wherezip in ('98001','98002')

I get the data. Why doesn't SQL Server properly substitute the value of @zip?

The SQL Server parser interprets @zip as a single value. So the IN compares your zip column with the single value in the parentheses, which is a string with a comma in it, and your zip column won't match that. However, you can change the query slightly to

declare @zip varchar(20)set @zip="'98001','98002'"exec('select * from ks_list where zip in (' + @zip + ')')

and you'll get the expected results. In this case, the value of the variable is resolved first. Then, when the parser evaluates the IN list, the parser sees two values separated by a comma and doesn't know that those two values came from a single variable.