Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
PRB: Using the Visual Studio 6.0 and Access 2000 Visual Database Tools with SQL Server 2000
Article ID: 266277 - View products that this article applies to.
This article was previously published under Q266277
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 2000The Access 2000 database design element incompatibilities are as follows:
Visual Studio 6.0 SP3 and EarlierThe 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:
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
Where to get the SQL Server 2000 Compatibility ReleaseThe 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:
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.
Interacting with new SQL 2000 featuresOne 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.
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:"
TABLEA COLA1 (primary key) COLA2 COLA3 TABLEB COLB1 COLB2 (foreign key on TABLEA) VIEWA: schema-bound view on TABLEA
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.
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:
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.
Unresolved IssuesIn Visual InterDev and Microsoft Visual J++, you receive an erroneous message when you perform the following steps:
Article ID: 266277 - Last Review: August 15, 2005 - Revision: 3.4