SQL Server Q & A Online - 26 Jan 2000

When I do a SQL Server 6.5 query from the sysobjects table using SQL Server 7.0, SQL Server doesn’t support it. For example, when I run a query that picks the objects, such as

SELECT name FROM sysobjects WHERE type='v' ORDER BY name 

it returns the wrong results. What do I need to do to make this query work correctly?

This query will return all views, but you shouldn't directly query the system tables, such as sysobjects. I recommend you look at the INFORMATION_SCHEMA views. The same information you’re trying to query from this table can be found through these views.

SQL Server 7.0 doesn’t appear to handle null in the simple and elegant way it did in 6.5. When I migrated to SQL Server 7.0, my application had several processing problems because of null handling. 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 a case such as this one?

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

In SQL Server 7.0, what’s the best way to transfer a database to a new server?

The easist way to transfer a current database to a new server is to use the sp_detach_db and sp_attach_db stored procedures. However, this option won't move your settings from the master and msdb, so you might want to back up these databases on your current server and restore them on your new server. You can then put the files in the same drive letter locations as before, and the server won’t know the difference. However, you need to change the name of the server in the sysservers table. For more details about sp_dropserver and sp_addserver, see the SQL Server Books Online (BOL).

When I tried to import a 350-column dBase (.dbf) table into SQL Server 7.0, DTS returned an error that said I’d defined too many fields. I also tried to import the same table into Access 2000 and 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 these tables into SQL Server 7.0 without seeing errors? Also, does SQL Server 7.0 have any settings or options that limit the number of columns or table size you’re importing? If SQL Server 7.0 has settings to regulate the column or table size, how can I change them?

You can import the dBase table, 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, the message says 65), run:

sp_dbcmptlevel 'pubs',70 

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