Editor's Note: Send your SQL Server questions to Richard Waymire, Microsoft's group program manager for SQL Server management tools, at [email protected].
Correlating Event ID and Error Numbers
My company is just starting to use SQL Server 7.0 on Windows NT 4.0. To monitor the NT event log (application category) for SQL Server errors, I've installed a standard application that monitors the event log for both the NT event ID numbers and the SQL Server error numbers. However, I heard that to use my company's standard monitoring software and to complete the SQL Server 7.0 implementation, I must correlate the event ID numbers with the appropriate error numbers. I've checked numerous resources but can't find any information about the topic. Do you know where I can find the correlation data?
Unfortunately, correlation data doesn't exist. Microsoft is working on providing this information in the next SQL Server release, code-named Yukon. You must parse each error message to look for the actual SQL Server error.
Designating a Language for Full-Text Search
I get the following error message in all SQL Server 2000 editions:
Server: Msg 7619, Level 16, State 1, Line 1 Language database/ cache file could not be found.
What does the message mean?
Microsoft has documented a similar case. In that situation, apparently the wordbreaker for Full-Text Search couldn't load correctly because the Full-Text Search—enabled table contained French data, but the Full-Text Search catalog was created in English. Dropping the catalog and recreating it by designating French as the language for the wordbreaker fixed the problem.
Estimating Replication Time and Space
I run SQL Server 7.0 on Windows NT 4.0. The database size is 50GB. I plan to set up transactional replication to another server in the same location. Approximately how long should I expect the initial replication to take? I've installed the Distributor and Publisher on the same server. For a database of 50GB, how much space do I need to claim for the Distributor?
You should expect the initial replication to take about the same amount of time that you spend using the bulk copy program (bcp) to bcp out the data that you want to replicate plus the time the data takes to bulk-copy into the Subscriber. Microsoft recommends using native format for SQL Server—to—SQL Server replication. You can also tweak SQL Server's snapshot and distribution tasks to generate and apply the initial data faster by experimenting with the MaxBcpThreads parameter. The mileage varies depending on the number of CPUs and the disk subsystem you assign to the task. The speed of your network greatly affects how long the replication takes. For the Distributor, you need to allocate space approximately equal to the size of the data you're replicating plus the space that SQL Server needs to accumulate incremental changes in the distribution database. If you replicate continuously, the accumulation should be insignificant.
Changing Database Names During a Database Restore
I'm having a problem restoring a database. I backed up database ABC from server1 to the abc.bak file. Then, I created a new database called XYZ on server2. The servers belong to different companies and aren't connected to each other. I wanted to restore the abc.bak backup on the XYZ database on server2, so I selected Restore, From device, and on the Option tab, I selected Force restore over the existing database. In the option below the Force restore option, I specified current data and log file path for Move to Physical File Name. However, the Logical File Name shows ABC database names. If I try to change the ABC database names to XYZ names, the restore fails. But if I keep the ABC database names, the restore works. How can I overcome the Logical File Name problem?
You can't change the logical name of a file during a restore, so you must use the existing database names. Simply restore the database by using the ABC database names, and move the files to their new locations. You can then use the following ALTER DATABASE statement to change the logical names to XYZ names:
ALTER DATABASE MODIFY FILE
Note that this solution works only on SQL Server 2000, not on SQL Server 7.0.
Creating INSERT Statements
I want to log in to a SQL Server 7.0 database and script out the data from a table so that I can create the INSERT statements to populate another table with the data. To allow the programmatic repopulation of tables, I want to point to a table and create the text file datapopulate.sql, which contains necessary SQL commands such as the TRUNCATE option and the IDENTITY_INSERT option. Do you know of a tool I can use to create the INSERT statements?
I don't know of any tool or utility that lets you script out the data, then turn it into INSERT statements directly. However, you can use the bulk copy program (bcp) to copy out the data in character format, use a script to modify the file, then use the following wrapper
INSERT tablename values (" ")
Or, you can write a SELECT statement that accomplishes the same task, insert the results of the SELECT statement into a temporary table, then bcp out the results.
Synchronizing the Default Installation Language and Date Syntax
I'm having a problem with Query Analyzer. When I write the following query
SELECT WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems WHERE DateCreated > '28/02/2001'
the query fails and returns the following error message:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
But if I write the same query this way:
SELECT WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems WHERE DateCreated > '2001-02-28' OR SELECT WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems WHERE DateCreated > '28/Feb/2001'
the query works fine. Why do I get these results?
Query Analyzer bases its date interpretations on SQL Server's default installation language and date syntax. If the default installation language is US English, SQL Server interprets a date with the syntax 28/02/2001 as US English's default date format mm/dd/yyyy, or 28th month, second day, 2001—a date that doesn't exist. When you write the same date with the syntax 2001-02-28, SQL Server interprets the date by using a different conversion semantic. To see the default formats in SQL Server Books Online (BOL), look up SQL Server's default installation language (for example, US English), then follow the links to the defaults for that language. See Kalen Delaney, Inside SQL Server, "Solving the Datetime Mystery," September 2000, and "Inside Datetime Data," October 2000, for more datetime information.
Increasing the Maximum Number of Characters in a Column
I need to display data in a text column in Query Analyzer as well as in our Java-based front-end tool. Query Analyzer limits the number of displayed characters to 256 bytes. I've been looping through each column in 256-byte increments by using the READTEXT statement, but do you know of a better technique? Furthermore, what's more efficient: using the READTEXT clause or the SUBSTRING clause?
The path Tools, Options, Results in Query Analyzer 2000 takes you to a dialog box that lets you increase the maximum number of characters per column. The default ceiling is 256, so bump it up to a larger number, such as 8000. For reading text data, the most efficient clause is READTEXT.
Running a Maintenance Plan in Multiple-User Mode
I'm running SQL Server 2000 on Windows 2000 Service Pack 1 (SP1). I've encountered a problem when I run a maintenance plan. The integrity checking and backups fail for the master and msdb databases as well as for user database ODS1. I can't put these databases into single-user mode. For some reason, the connections appear to stay open. When I stopped and restarted SQL Server, the ODS1 integrity check worked, although the master and msdb checks failed again. Do you have any ideas about what's happening?
You've probably set the option attempt to repair any minor problems in the Database Maintenance Wizard, which behind the scenes runs DBCC, CHECKDB, or CHECKTABLE with the repair option set. When you request the repair option, the database has to be in single-user mode. To avoid the problem, edit the plan and remove the repair option.
