Answers from Microsoft - 27 Apr 2000

When I use SQL Server 7.0 to run the following SQL Server 6.5 query to select objects from the sysobjects table, it returns the wrong results. How can I make this query work correctly?

SELECT name FROM sysobjectsWHERE type='v'ORDER BY name

This query should return all views, but you shouldn't directly query system tables such as sysobjects. I recommend you look at the INFORMATION_SCHEMA views. Through these views, you can find the same information you're trying to obtain from the sysobjects table. For more information about accessing system table information through INFORMATION_SCHEMA views, see Kalen Delaney, Inside SQL Server, "Property Functions, Schema Views," April 2000. Remember, SQL Server system tables aren't designed for direct queries and can change from release to release.

SQL Server 7.0 doesn't handle null in the simple, elegant way that SQL Server 6.5 does. When I migrated to SQL Server 7.0, my application had several null-related processing problems. For example, when I use concatenations such as lastname+firstname+middle initial with the middle initial as null, the entire value becomes null. How does SQL Server 7.0 handle null in such cases?

Microsoft implemented the ANSI-standard way of working with null in SQL Server 7.0, which handles null correctly. For backward compatibility, you can also use the CONCAT_NULL_YIELDS_NULL SET option; you can set this option for a database by using sp_dboption or set the option per session by using a SET statement. If you've turned off CONCAT_NULL _YIELDS_NULL, you'll see the SQL Server behavior you described. SQL Server Books Online (BOL) has more information about null in SQL Server 7.0. Also, see Kalen Delaney, Inside SQL Server, "Controlling Query Behavior," January 2000.

When I tried to import a 350-column dBase (.dbf file) table into SQL Server 7.0, Data Transformation Services (DTS) returned an error that said I'd defined too many fields. I tried to import the same table into Microsoft Access 2000 and Microsoft Excel 2000, but both limit tables to 255 columns. I read that SQL Server 7.0 can handle up to 1024 columns. How can I import dBase tables into SQL Server 7.0 without errors? Also, does SQL Server 7.0 have any settings or options that limit the number of columns or the size of the table you can import? If so, how can I change these settings?

You're correct: SQL Server 7.0 supports up to 1024 columns. And you can import dBase tables, but first you need to make sure that the database you're trying to import is in SQL Server 7.0 compatibility mode. For example, if you want to import the Pubs database, run

sp_dbcmptlevel 'pubs'

You should see the message The current compatibility level is 70. If SQL Server doesn't return this message—for example, if the message says The current compatibility level is 65—run

sp_dbcmptlevel 'pubs',70

to put the database in 7.0 compatibility mode, which lets all the new SQL Server 7.0 features work in their native form.

The error message Function Sequence Error appears when I run tests using ODBC in SQL Server 7.0. Microsoft TechNet and the Microsoft Developer Network (MSDN) Web sites (http: //www.microsoft.com/technet and http://msdn .microsoft.com, respectively) say the problem is the ODBC driver version (I'm using version 2.x, and the new version is 3.x). Where do I find the correct drivers?

To run ODBC with SQL Server 7.0, you need to use an updated backward-compatible ODBC driver. The ODBC drivers are in the MDAC_TYP.exe file in the Microsoft Data Access Components (MDAC) release that shipped with the SQL Server 7.0 CD-ROM. For the most recent version of MDAC, go to http://www.microsoft.com/data. Using the latest ODBC driver version will fix your problem and maintain backward compatibility. To make sure the driver works, try it on a test machine first.

In SQL Server 6.5, I transferred a database from one device to another on the same server, then saw the following message in the log file:

Error: In file DEVTS010.SuretyNetDEV.PRC in statement on line 20135

Transfer Status: Creating Stored Procedures on destination database

Ad-hoc updates to system catalogs not enabled. System Administrator must reconfigure system to allow this.

SuretyNetDEV is the source device. How can I successfully transfer the database?

It looks as though, in the process of transferring the database, you're transferring a stored procedure to directly update a system table. Before you can do this transfer, you must set the allow updates option with sp_configure to create a stored procedure that directly updates a system table. For example, the code in Listing 1 turns on allow updates, creates the procedure that modifies the system tables, then turns off allow updates.

SQL Server 7.0 is on the D drive in my PC, which runs Windows 95. After I added another hard disk, the new drive became D and the old D drive remapped as E. Because the drive mapping changed, I can't start the SQL Server engine or components. Changing the Registry settings from D to E didn't help. What do you suggest?

Besides changing the Registry settings, you need to change the parameters in the HKEY_LOCAL _MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerParameters key and the service startup information in the HKEY_LOCAL _MACHINESYSTEMCurrentControlSetServicesMSSQLServer and SQL ServerAgent keys. You also might need to change each entry in sysdatabases if you moved the data files. See Kalen Delaney, Inside SQL Server, "Direct Access," May 2000, for information about how to directly update system tables.

I'm trying to set up mail with SQL Mail on SQL Server 7.0 (running on Windows NT 4.0) and talking to a Microsoft Exchange Server system. The mail profile is in Exchange Server. When I try to test the profile name through Support Services' SQL Mail Configuration from the SQL Mail option, I see the error message

Error 22030: A MAPI error ( error number: 273) occurred. Unable to get the default MAPI Message Store due to MAPI error 273: The information store could not be opened.

Then, when I execute xp_sendmail, I get the message MAPI login failure. I created a new account in NT and used it in MSSQLServer and the SQL Server Agent. What's wrong?

The login error message appears when you're logged in to the server as the MSSQLServer service account when you create the mail profile. To use the service name SQLService to run the SQL Mail option, you need to log in to the server under the SQLService login and create a client mail profile (I usually call it SQLMail). Then, set the MSSQLServer service to use the account SQLService. The system will then see the appropriate Registry keys, which are called the MAPI profile.

The messages about the Messaging API (MAPI) information store could signal that you're using a personal store (.pst) file to store the mail. If so, you need to use a server-side mailbox and eliminate the .pst file as your default mail store.

I need to truncate a transaction log to minimize the size of a transaction log file (maximum of 1.2MB) for an 11MB SQL Server 7.0 database. The database option trunc.log on chkpt isn't set, and I've set a daily backup schedule. The backup log performs the log truncation, but now my transaction log is 1MB; I don't heavily update the database, so the size should be no more than 200KB. How did the log size change, and how can I shrink it?

You need to use sp_dboption to turn on the database auto-shrink option. Keep in mind that SQL Server won't shrink the log size below its initial creation size, so if you created the log with an initial size of 1MB, it won't shrink below that size. For details about shrinking your database, see Kalen Delaney, "Database Sizing in SQL Server 7.0," premiere issue.

My problem is that SQL Server 7.0 and 6.5 don't recognize each other. My SQL Server 7.0 machine doesn't have SQL Server 6.5 installed on it, so I thought I needed to install 6.5 first. Before I tried to install SQL Server 6.5 on the 7.0 machine, I removed 7.0. After I installed SQL Server 6.5, the MSSQLService on 6.5 started, but only the Enterprise Manager could connect to the server, and the error message DB Library cannot connect appeared. Does the problem occur because SQL Server 7.0 already exists? I looked at the Upgrade Wizard menu that I use to upgrade from a tape device. Can I migrate the SQL Server 6.5 Master, msdb, and user databases directly from backup tape?

The problem doesn't occur because SQL Server 7.0 already exists. SQL Server 7.0's DB-Library is fully backward-compatible with 6.5, so you can connect to it. My system has SQL Server 7.0 and 6.5, and I switch between the two regularly without problems. Try connecting with isqlw.exe or isql.exe; these applications aren't as complicated as Enterprise Manager.

You can't migrate directly from SQL Server backup tape, but you can use the Upgrade Wizard. You need to put the data on tape, pause the upgrade, delete the SQL Server 6.5 .dat files, then finish the upgrade. A good time to use the Upgrade Wizard is when you're short of disk space and can't have both SQL Server 7.0 and SQL Server 6.5 data files on the system at the same time.

I'm using the SQL Server 7.0 ODBC driver to develop a module in an application in which I want to use the Microsoft Word 97 mail-merge option. I opened Word and selected Tools, Mail Merge. Then, I created a new data source, connected to the Pubs database, and ran the simple select statement

SELECT au_id FROM authors

The query executed, and the results appeared in the MSQuery window. However, when I tried to merge the query with the Word document, the error Invalid object name 's' appeared.

Regardless of the Pubs object I used, SQL Server trimmed the first three characters (e.g., Pub). I connected to my application database and found the same three characters trimmed in all the objects. How can I fix the trimming problem?

To find where the problem lies, turn on SQL Server 7.0's SQL Profiler to learn which SQL command the server thinks it's receiving. Also, to see whether the problem lies with ODBC, you can go to the ODBC control panel and turn on detailed ODBC tracing. You'll probably need someone experienced in ODBC tracing to interpret the results of the ODBC trace correctly.