Questions, Answers, and Tips About SQL Server

I installed a new instance of SQL Server 2000, and all my SQL Server 7.0 tools have disappeared. What happened to them?

Although you can run multiple instances of the server engine on one machine, you can't keep multiple instances of the client and administrator tools on the same machine. When you install multiple instances of SQL Server, you'll use the new SQL Server 2000 versions of SQL Server tools (e.g., Query Analyzer, SQL Enterprise Manager—SEM, Microsoft Data Access Components—MDAC) to connect to the SQL Server 7.0 instance. Don't install a new instance if you must keep the old tools.

You might be able to modify executables, DLLs, and Registry entries to hack around this limitation, but you can easily trash a system when you experiment with tricky areas such as the Registry. Until we learn how to work around this limitation, exercise extreme caution.

I want to store column definitions associated with a column in a table, and I want the definitions to reveal the business meaning of the column. For example, if I have a column called OrderDate in my Order table, I want to store a definition that indicates whether the column contains the date the customer placed the order or the date the company fulfilled the order. How do I use the Microsoft Repository to configure this setup?

SQL Server 2000 lets you store extended properties with many database object types. Extended properties are user defined and store a value of type SQL_VARIANT. Visual Basic (VB) programmers are familiar with the new variant data type. Like VB's variant data type, SQL_VARIANT lets you store different data types' data values in one column, parameter, or variable. Each instance of a SQL_VARIANT column records two items: the data value and the meta data that describes the value (i.e., the value's base data type, maximum size, scale, precision, and collation). You can use the SQL_VARIANT_PROPERTY function to get the meta data information for any SQL_VARIANT instance.

For example, if you want to store a description of the au_id column in the authors table in the pubs database, right-click the column name in the new Object Browser that the Query Analyzer interface provides, then select Extended Properties. We added a new property called WhatAmI with a value of I am the author id column!!! Alternatively, you can use the sp_addextendedproperty procedure to accomplish the same task.

sp_addextendedproperty
   'WhatAmI2', 'This is a new
   property value', 'user', dbo,
   'table', authors, 'column',
   au_id

You can then use a standard SELECT statement with a new function called fn_listextendedproperty to retrieve the information, as the following example shows:

SELECT   * FROM
   ::fn_listextendedproperty
   (NULL, 'user', 'dbo',
   'table', 'authors', 'column',
   default)
objtype objname   name value
COLUMN au_id WhatAmI I am the 
   author id column!!!
COLUMN au_id WhatAmI2
   This is a new property value
SELECT   *
FROM  
   ::fn_listextendedproperty
   (NULL, 'user', 'dbo',
   'table', 'authors', 'column',
   default)

I'd like to start playing with SQL Server 2000 beta 2, but I don't want to trash the current working version of SQL Server 7.0 that I've installed on my personal developer workstations. Can I simultaneously run SQL Server 2000 and SQL Server 7.0?

You can simultaneously run SQL Server 2000 and SQL Server 7.0 on the same machine. Previous upgrade processes let you install two SQL Server versions on the same machine, but you couldn't run both versions at the same time. To solve this problem, SQL Server 2000 supports multiple instance functionality.

To simultaneously run SQL Server 2000 and SQL Server 7.0, run the SQL Server 2000 setup program on a system running SQL Server 7.0. You'll move quickly through the Welcome Screen, and the program will ask you to choose between a local or remote installation. Next, the Installation Selection dialog box, which Screen 1 shows, will present you with several installation options. Select Create a new installation of SQL Server. Don't select Upgrade, remove, or add components to an existing installation of SQL Server; upgrading your existing installation will convert your existing databases to the new SQL Server 2000 format.

Brian's former SQL Server 7.0 network name was NITTANY, and the name of his new SQL Server 2000 instance is NITTANY_SQL2000. Brian can connect to the default instance NITTANY that uses a SQL Server 7.0 engine or connect to NITTANY\NITTANY_SQL2000 to access the SQL Server 2000 instance. Before you decide to install multiple instances of SQL Server, be aware that SQL Server 2000 tools will replace the old SQL Server 7.0 administrative tools. SQL Server Books Online (BOL) provides detailed information about multiple instances.