PRB: Using the Visual Studio 6.0 and Access 2000 Visual Database Tools with SQL Server 2000

SQL Server 2000 was released more than a year after Access 2000 and Visual Studio 6.0. As a result, the original versions of those products do not support some of the new features of SQL Server. When you attempt to design SQL Server 2000 databases with either Visual Studio 6.0 SP3 or earlier, or Microsoft Office 2000 (previous to SR-1), you may experience severe incompatibility issues.

To address this problem, Microsoft released updated versions of the Visual Database Tools in Visual Studio SP4 and Office 2000 SR-1. The goal of this update is to prevent you from making the design tools read-only and consequently damaging your databases. A second update is currently available in SQL Server 2000 and will be available in the next service pack of Visual Studio 6.0 and the next service release of Office 2000. This update contains fixes that allow you to design SQL Server 2000 databases at a SQL Server 7.0 level of functionality.

To take full advantage of the new features in SQL Server 2000, you are advised to design your databases using the SQL Server Enterprise Manager.

If you want to design SQL Server 2000 databases in Visual Studio or Access, you must install the latest version of the Visual Database Tools (see the "Resolution" section for details.)

This article only describes the incompatibilities that are found when you use the database design elements of Visual Studio 6.0 and Office 2000. This article does not describe the issues that arise in the database runtime components of Visual Studio 6.0. These issues will be addressed in the next service pack of Visual Studio 6.0.

Access 2000

The Access 2000 database design element incompatibilities are as follows:

• Access Data Project (ADP) forms, reports, and data pages do not run if they are bound to a stored procedure. This results in an error that the stored procedure does not exist.
• The Access 2000 Database Designer and Table Designer fail to load. They display a dimmed window and then close without an error message.
• Stored procedure names are displayed with a ";1" prefix in the Access Database window. This prevents you from executing stored procedures from within the Access Database window. Also, you will not be able to design stored procedures because Access interprets the ";1" prefix as part of the name.
• When you use the Access 2000 Record Source builder button to build queries, the Query Designer acts as if it is in ANSI SQL mode and does not enable SQL Server-specific features.

Visual Studio 6.0 SP3 and Earlier

The Visual Studio 6.0 SP3 and earlier database design element incompatibilities are similar to those that are listed for Access 2000, plus the following additional items:

• Designers fail to load.
• Missing commands in the dataview in Microsoft Visual InterDev, Microsoft Visual Basic, and Microsoft Visual C++.
• Cannot edit stored procedures.

As previously mentioned, Microsoft implemented a twofold solution to the SQL Server 2000 incompatibility issues.

The first step was a hotfix that was included in Visual Studio 6.0 SP4 and Office 2000 SR-1. This interim step fixed a few of the issues with Stored Procedure enumeration, and made the Table, View and Diagram Designers read-only. The designers were made read-only to prevent the serious data and meta-data loss bugs that may have occurred.

The second step was a SQL Server 2000 compatibility release of the Visual Database Tools. This version is included in SQL Server 2000 Client Tools, and will be available in the next service pack of Visual Studio 6.0 and the next service release of Office 2000.

Goals of the SQL Server 2000 Compatibility Release

• If you use SQL Server 2000 databases and have not implemented any of the new features of SQL Server 2000, you will not experience any loss of functionality when you use Visual Studio 6.0 or Access 2000 with the compatibility release to design SQL Server 2000 databases.
• If you design a database object that takes advantage of SQL Server 2000-specific functionality, you will not encounter data loss under any circumstances.
• In most cases, if you design a database object that takes advantage of SQL Server 2000-specific functionality, you will not encounter meta-data loss. In the few cases that can result in meta-data loss, a dialog box will warn you.

Where to get the SQL Server 2000 Compatibility Release

The SQL Server 2000 compatibility release is included with the SQL Server 2000 Client Tools. To install the SQL Server 2000 compatibility release, you can install the SQL Server 2000 Client Tools on the required computers.

NOTE: It is not sufficient to install the Client Tools on just the SQL Server computer; you must install the Client Tools on each development computer.

The compatibility release will also be included with Visual Studio 6.0 SP5 and Office 2000 SP-2, when they are released.

If the second update is not yet available in Visual Studio 6.0 or Office 2000, and you do not want to use the second update without installing the SQL Server 2000 Client Tools on your development computer, you can install the SQL Server 2000 compatibility release of the Visual Database Tools as follows:

1. Install the SQL Server 2000 Client Tools on a computer other than the development computer.
2. Browse to the following folder:
   Program Files\Common Files\Microsoft Shared\MSDesigners98
3. Copy the contents of the folder, including all sub-folders, to the same folder on your development computer. This replaces the old files with the updated ones.

This section describes what to expect when you design SQL Server 2000 databases with the compatibility release. This section also describes which issues you may encounter when you use the Visual Studio 6.0 SP4 or Access 2000 SR-1 version of the Visual Database Tools.

General Information

• The compatibility release was tested with Microsoft Data Access Components (MDAC) versions 2.5 and 2.6.
• When you use either the Schema Designer, the Table Designer, or the View Designer in Office 2000 or Visual Studio 6.0, a warning message presents you with specific restrictions. You can suppress this message in the future; if you do so, that information is retained in the Registry. After you suppress a warning in one of the designers in either Office or Visual Studio, the message is not shown again in any designer in either product.
• SQL Scripts are not supported. To create SQL Scripts in Visual Studio 6.0, you can right-click the table name in the Data View, and then paste the contents of the clipboard to a file. This functionality has been disabled, and the script is not generated to the clipboard if the connected SQL Server has a version number greater than 7.0.

Interacting with new SQL 2000 features

One of the goals of the compatibility release is to let you design SQL Server 2000 databases. However, the tools cannot always accommodate the new SQL Server 2000 features. The following items describe the ways in which the design tools accommodate the new feature set.

New Data Types

The Results pane returned by the Query Designer and View Designer recognizes the new bigint and sql_variant data types. The bigint data type is completely supported in the Results pane. Sql_variant is displayed as <sql_variant> in the cells of all sql_variant columns. If the result set is otherwise updateable, but any column in the primary key is sql_variant, you cannot update the Results pane.

You can create an UPDATE, INSERT, or DELETE statement that operates on a table that has a primary key with a bigint and/or sql_variant column.

The Grid pane supports a Convert function that casts an expression to bigint. The Grid pane does not support a Convert function that casts an expression to sql_variant. However, you can use Convert to cast to sql_variant in the SQL pane. In this case, the resultant sql_variant column is treated as it was in the Results pane.

The Cast function is not supported in either the Grid pane or the SQL pane. You cannot use this function to create an expression of sql_variant or bigint data type.

However, you can specify scalar functions (either user-defined or built-in) that return either bigint or sql_variant in either the Grid pane or the SQL pane. In all of these cases, the resultant bigint or sql_variant column is treated as it was in the Results pane.

In the Database Designer and Table Designer, the bigint and sql_variant data types are recognized and even inserted in the drop-down list of data types.

Extended Properties

The Visual Database Tools does not recognize any of the properties that use SQL Server 2000's new extended properties mechanism. The Database Designer and the Table Designer check to see if an object that is being dropped in order to carry out a "genius edit" has any extended properties. "Genius edits" are described in the "Schema Bound Functions and Views" section to follow. If a property is going to be lost, a warning message states that extended properties will be lost and asks if you would like to proceed with the operation.

Because changes to views in the View Designer are always implemented by ALTER VIEW statements, any existing extended properties are retained because you do not drop or recreate the view.

Cascading Relational Integrity

In the Database Designer and the Table Designer, you cannot specify the Cascading Relational Integrity property for foreign keys. If you directly change any foreign key relationship, and the current foreign key has either CASCADE DELETE or CASCADE UPDATE specified, you are warned that the changes will be lost and asked if you want to continue with the change.

To indirectly change a relationship, you can change the data type of a column. This can result in a long chain of changes, and any one of the links in this chain may be a relationship with a cascade/delete relationship. These properties are not recognized when you produce change scripts, so the clauses are not present in the foreign key definitions in generated scripts.

Schema-Bound Functions and Views

Note that views and functions can be bound to tables, to other views, and to user-defined functions. The View Designer recognizes the schema-bound property and preserves it if it is necessary to recreate a view. This property is not exposed in the view property page of Office 2000 SR-1 or Visual Studio 6.0 SP4; therefore, you cannot specify this property for new views or alter it for existing views in the View Designer.

If some object is bound to a view that you are altering, dropping, or renaming, the View Designer does nothing different; the back end makes this happen. When an object (Object1) has another object (Object2) bound to it, SQL Server prevents Object1 from being dropped or renamed. SQL Server also prevents modifications to Object1 that would impact Object2. The Database Designer and the Table Designer do not object if someone tries to modify, drop, or rename Object1; rather, they rely on the back end to prevent illegal operations. The fact that they are relying on the back end could result in the loss of meta-data. The following example illustrates how this can happen.

The following objects are in a database:

When you attempt to insert a new column between COLA2 and COLA3 in TABLEA, a "genius edit" results where TABLEA must be re-created. The following steps are a simplified version of the steps that the Schema/Table Designer carries out in order to accomplish this "genius edit:"

1. Drop the foreign key from TABLEB to TABLEA.
2. Drop and then re-create TABLEA.
3. Re-create the foreign key from TABLEB to TABLEA.

However, because there is a schema-bound view on TABLEA, step 2 fails and the table is not dropped. The chain stops when any step fails, but the designer commits any steps before the failure. Thus, the foreign key from TABLEB to TABLEA is lost.

Indexed Views

You cannot specify indexes on views in the Visual Studio 6.0 View Designer. Even though ALTER VIEW statements always implement changes to views in the View Designer, the ALTER VIEW statement itself drops indexes. Because of this behavior, the View Designer presents a warning message whenever you design an existing view that has indexes. This warning states that if the user makes changes to the view, all indexes will be dropped.

Column Level Collation

In the View Designer and Query Designer, if a COLLATE clause is specified in the SQL pane, the Designer goes into "ghost mode." "Ghost mode" means that the Diagram and Grid panes appear dimmed because the COLLATE clause is not graphically supported. The SQL pane remains available, and you can continue to modify the query. The statement runs properly against the back end if the clause is specified correctly. If the COLLATE clause is specified in the Grid pane, it is treated as an unrecognized word. In most cases, the COLLATE clause is considered part of the column and is delimited with the "real" column name.

You cannot specify the collation property of a column in the Database Designer or the Table Designer. However, if you alter a table in a way that requires a genius edit, existing collation properties are saved. The collation property of columns is also recognized when you produce change scripts.

Ascending and Descending Indexes

The ASC and DESC properties on index and key columns are irrelevant to the View Designer and Query Designer. You cannot specify these properties within the Database and Table Designers. If you use a genius edit to change a table, without warning, you lose any DESC properties and any index or key column. All indexes become Ascending by default.

The DESC property is not recognized when you produce change scripts, so it is not present in the generated scripts.

New Trigger Functionality

SQL Server 2000 adds the ability to specify triggers on views. Because you always use ALTER VIEW statements to change to views in the View Designer rather than drop and recreate the view, you retain any existing triggers. The Database Explorer in Visual Studio does not enumerate triggers on views, nor does it support an Add Trigger command for views.

First and Last properties on triggers are set with the new sp_SetTriggerOrder stored procedure. If you use a genius edit on a table with such triggers in the Database and Table Designers, you are warned that you will lose these properties if you make the change. These properties are not recognized when you produce change scripts, so they are not present in the scripts that are generated. INSTEAD OF triggers do not impact Visual Database Tools because the syntax of the CREATE TRIGGER statement is not recognized. The new trigger template does not show this variation. The new optional AFTER syntax also works.

User-Defined Function Support

The Database Explorer in Visual Studio does not enumerate or properly support user-defined functions of any type.

In the View Designer and Query Designer, you cannot code scalar-valued functions into the Grid pane because they must be qualified with the database owner (DBO). Visual Database Tools version 6.0 does not allow such qualification in the Grid pane; however, you can specify scalar-valued functions in the SQL pane. Because you cannot properly show these functions in the Grid pane, the designer is placed into ghost mode if the functions appear in the SQL pane.

The Database Explorer in Visual Studio does not enumerate user-defined table-valued functions or inline functions.

The View Designer and Query Designer have been extended to allow the use of table-valued functions and inline functions. You must explicitly key such functions into the SQL pane. The Diagram pane is updated accordingly, and a box corresponding to the function appears in the Diagram pane. This does not check for metadata. There is no support for changing statement types to other statement types from such ECLs.

The View Designer and Query Designer do not distinguish inline functions from other table-valued functions. As a result, updateable functions are not recognized or allowed in INSERT, UPDATE, and DELETE statements.

Note that SQL Server 2000 ships with several functions (including fn_HelpCollations and fn_ListExtendedProperty) that utilize the user-defined table-valued function mechanism. You must use a special ::fn_name syntax to specify the function. To explicitly code these functions in the SQL pane, you can enclose the double colon and function name in square brackets.

Text in Row

"text in row" is a new property in SQL Server 2000. It is specified through the sp_tableOption stored procedure. You cannot specify this property within the Database and Table Designers. If you use a genius edit to change a table, you lose any "text in row" property without warning. The "text in row" property is not recognized when you produce change scripts, so it is not present in the scripts that are generated.

Count_BIG CHECKSUM_AGG

In the Grid pane of the Query Designer or View Designer, the new COUNT_BIG and CHECKSUM_AGG functions are not listed as supported aggregate functions in the Group By drop-down list box.

SET OPTION Requirements

SQL Server requires that you define indexed views and indexes on computed columns with the following SET statements. You must set these seven options:

• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER
• The NUMERIC_ROUNDABORT option must be set to off

Furthermore, any subsequent INSERT, UPDATE, or DELETE statements that affect the base data that is used to build the index fail unless these seven statements are set accordingly. SQL Server generates an error and rolls back any INSERT, UPDATE, or DELETE statements that you attempt by a connection that does not have the proper option settings. This impacts the updates that you make within the Results pane of the Query Designer. Although the Query Designer inherits most of the appropriate settings from OLE DB, it does not inherit the ARITHABORT ON setting. Therefore, when you change anything in the Results pane that updates the index of a view or the index on a computed column, you receive the following error message:

Unresolved Issues

In Visual InterDev and Microsoft Visual J++, you receive an erroneous message when you perform the following steps:

1. From the File menu, click New Project.
2. On the New tab in the left pane, click the plus sign (+) to expand the Visual Studio project node. Under the Visual Studio project node, click Database Projects.
3. On the New tab in the right pane, click the New Database Project icon, and then click Open.
4. In the resultant dialog box, use either the Jet 3.51 or 4.0 database providers to create a connection to a Jet database.
5. In the Data View window, notice that a node with the name of your project has been created. Click the plus sign (+) to expand the node. Expand all of the nodes under the project node until you can see the database tables. Double-click one of the database tables.
6. You encounter the following error message:
   This version of the database design tools is not compatible with SQL Server 2000, so you cannot save changes from this tool to the SQL Server 2000 database. To modify a SQL Server 2000 database, use the SQL Server 2000 Enterprise Manager.
   When you click OK, the query opens correctly.

Visual Basic does not provide Design table and New table menu items. When you perform the following steps, the Design table and New table menu items are missing. In addition, the Context menu in the Views folder has a similar problem.

1. In Visual Basic Enterprise Edition, create a new Data Project.
2. In the Project Explorer window, double-click the new Data Environment.
3. In the Data Environment window, right-click Connection1. From the Context menu, click Properties.
4. In the resultant dialog box, create a connection to a SQL Server 2000 database.
5. On the View menu, click Data View Window.
6. In Data View Window, click the plus sign (+) to expand the connection that you configured in step three. Click the plus sign (+) to expand the Tables folder.
7. Right-click one of the tables to open the Context menu.

NOTE: This problem is fixed in the second update, which is available in SQL Server 2000. This will be available in the next service pack of Visual Studio 6.0 and the next service release of Office 2000.