Save Incomplete Dialog Box

Appears when errors are encountered while trying to save a database diagram or selected tables. This dialog box lists the following: the objects that were successfully saved in the database, the objects that were not saved, and the errors that were encountered.

OK

Returns to the diagram.

Save Text File

Displays the Save As dialog box, prompting you for a location in which to save a text file listing the tables.  This file provides a record of the database changes that were successfully saved as well as the changes that could not be saved due to errors.

For more information about the errors that can occur, see:

• Error modifying column properties.
  
• Invalid cursor state
  
  
• Unable to add constraint.
  
  
• Unable to create index.
  
  
• Unable to create relationship.
  
  
• Unable to modify table.
  
  
• Unable to preserve trigger.

If the error you want to troubleshoot does not appear in this list, see System errors. for additional messages returned by Microsoft® SQL Server™.

Error modifying column properties

Appears when your constraint expression contains an error.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]The name '[column value]' is illegal in this context. Only constants, constant expressions, or variables allowed here. Column names are illegal.

Cause

A default value defined for a character column is not enclosed in single quotation marks (').

Recommended solution

Enclose the value in single quotation marks in the database column's Default Value cell and then save the table.

Invalid cursor state

Appears when Microsoft® SQL Server™ runs out of resources while attempting to save selected tables or a database diagram.

ODBC error text

[Microsoft][ODBC SQL Server Driver]Invalid cursor state.

Cause

There is insufficient space in your database or transaction log to complete the save process.

Recommended solution

Check to see if the database or the transaction log is full. If so, increase the size of the database to accommodate the change. Check other system resources or contact your System Administrator.

For more information about increasing the size of your database, see Expanding a Database.

Unable to add constraint

Appears when a new constraint has failed on existing data or your constraint expression contains an error. Compare the ODBC error text that appears in the Save Incomplete dialog box with the error text shown below to determine the appropriate solution.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Data exists in table '[table name]', database '[database name]', that violates CHECK constraint '[constraint name]' being added. ALTER command has been aborted.

[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to create constraint. See previous errors.

Cause

Existing data does not match the check constraint.

Recommended solution

Change the data (for example, by using Query Designer) to match the constraint. For details, see Query Designer.

-or-

Clear the Check existing data on creation check box in the Tables property page for the check constraint in question. For more information about disabling this property, see Checking Existing Data when Creating a Relationship.

-or-

Change the constraint expression in the Tables property page for the check constraint in question.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column '[column name]' specified in constraint definition.

[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to create constraint. See previous errors.

Cause

The text value in the check constraint expression on the Tables property page is not enclosed in single quotation marks (').

-or-

A column that participates in the check constraint has been renamed. For example, if the original constraint had the expression (cityname = 'Paris') and you renamed the column to city, you would see this error.

Recommended solution

Correct the expression and save the table.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Line [line number]: Incorrect syntax near '[operator]'.

Cause

The expression defined for the check constraint (in the Tables property page) or the default constraint (in the Default Value cell) is not valid SQL syntax. For example, the check constraint expression 'city equals Paris' was typed instead of 'city = Paris'.

Recommended solution

Correct the expression and save the table.

For more information about constraints, see Constraints.

Unable to create index

Appears when a new index has failed on existing data.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Create unique index aborted on duplicate key. Primary key is '[primary key data]'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to create constraint. See previous errors.

Cause

A unique index was created in the Indexes/Keys property page but duplicate data exists in the database.

Recommended solution

Remove duplicate data from the database. For more information, see Designing Queries.

-or-

Change the option in the Indexes/Keys property page to allow duplicate rows in the index.

For more information about creating unique indexes, see Creating a Unique Index.

Unable to create relationship

Appears when a new referential integrity constraint has failed on existing data.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Data exists in table '[table name]', database '[database name]', that violates FOREIGN KEY constraint '[constraint name]' being added. ALTER command has been aborted.

[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to create constraint. See previous errors.

Cause

Existing data fails the foreign key constraint.

Recommended solution

Change the data that fails the foreign key constraint by running a query to show all the foreign key values that do not match primary key values. For example, to find foreign key values in the job_id column of the employee table that do not match primary key values in the jobs table, run a query with this SQL syntax:

SELECT  employee.emp_id, employee.job_id
FROM  employee LEFT OUTER JOIN jobs ON employee.job_id = jobs.job_id
WHERE (jobs.job_id IS NULL)

For more information, see Creating Queries.

-or-

Clear the Check existing data on creation check box in the Relationships property page. For more information, see Checking Existing Data when Creating a Relationship.

Unable to modify table

Appears when a new constraint has failed on existing data. Compare the ODBC error text that appears in the Save Incomplete dialog box with the two ODBC errors shown below to determine the appropriate solution.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]The column [column name] in table Tmp_ [table name] may not be null.

Cause

A new database column has been added that does not allow null values and does not provide a default value. The table name in question appears after "Tmp_".

Recommended solution

Change the column properties. Either select the Allow Nulls property or type a Default Value setting. For more information about setting properties for database columns, see Column Properties.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Attempt to insert the value NULL into column '[column name]', table '[database name] TMP_ [table name]'; column does not allow nulls. INSERT fails.

[Microsoft][ODBC SQL Server Driver][SQL Server]Command has been aborted.

Cause

The Allow Nulls property on an existing database column has been cleared, but the column has existing null values in it.

Recommended solution

Go to the column and select the Allow Nulls property.

For more information about setting properties for database columns, see Column Properties.

Unable to preserve trigger

Appears when your trigger text references a column that has been renamed, deleted, or assigned a different data type.

ODBC error text

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '[column name]'.
- Unable to preserve trigger '[trigger name]'.

Cause

A change to the table required the table to be recreated. When a table is recreated, the triggers attached to that table are automatically recreated as well.

Recommended solution

The recommended solution depends on the type of change made to the column referenced by the trigger.

To preserve a trigger that references a renamed column

• Rename the column to its original name and then save the table. This action will allow the table to be recreated. You can now rename the column, save the table again, and then edit the trigger to fix the renamed columns.

To preserve a trigger that references a deleted column

1. Expand the table that the trigger is attached to.
   
   
2. Right-click the trigger you want to change and choose Open from the shortcut menu.
   
   
3. Edit the trigger text and save the trigger.
   
   
4. Save the table or database diagram.

For more information about triggers, see Triggers and Enforcing Business Rules with Triggers.

System errors

System errors can appear in the Save Incomplete dialog box when you exceed Microsoft® SQL Server™ limitations that are not controlled by the Database Designer.  One such error is described here.

ODBC error text

[Microsoft][ODBC SQL Server Driver]Timeout expired.

Cause

The timeout can occur when you are updating the database with any Transact-SQL changes.

Recommended solution

Try again later to save the diagram or selected tables.

-or-

Save a change script and apply it to the database at a later time. For more information, see Saving a Change Script.

-or-

Increase the SQL Query Time-out value and try to save the diagram or selected tables again.

See Also

Database Designer Dialog Boxes | Database Designer Considerations for SQL Server Databases