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

MORE INFORMATION

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:

TABLEA     COLA1      (primary key)
           COLA2           
           COLA3

TABLEB     COLB1
           COLB2      (foreign key on TABLEA)

VIEWA:     schema-bound view on TABLEA
				

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.