Editor's Note: Send your SQL Server questions to Richard Waymire, Microsoft's group program manager for SQL Server management tools, at questions@sqlmag.com.
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.
Prev. page
[1]
2
next page 
