Share via

Troubleshooting Database Project, Build, and Deployment Issues

  • Article

You might encounter the following issues when you create, modify, build, or deploy database projects:

  • Errors when Creating Database Projects

  • Errors when Attempting to use SQL Server 2000 Database for Design-time Validation

  • Errors in Database Objects

  • Use of New Reserved Words in Microsoft SQL Server 2005

  • Differences in Command-line Build

  • Symmetric Keys, Asymmetric Keys, and Certificates

  • Dependencies and Update Scripts

  • Errors when You Add Yourself as a User

  • Quoted Identifiers May Cause Problems with Project Re-load

  • The Internal State of the Database is Inconsistent with its Contents

  • Full text search

  • SQLCLR objects

  • Undo Pending Changes

  • Windows NT user or group not found

  • Duplicate Object Names and Exclude Files

  • CREATE ASSEMBLY does not support referencing a file

  • Conversion issues from SQL Server 2000 to SQL Server 2005

  • Database and Server-qualified Names

  • Extended Property Support

  • Performance of Import Database Schema

  • Build Error Navigation

  • Database projects and the TRUSTWORTHY setting

  • Linked servers and import scripts

  • Syntax errors when using cross-database references

  • XML Indexes with Extended Properties

Errors when Creating Database Projects

An error message appears if you create a database project and you do not have permissions to create a database in your local instance of Microsoft SQL Server used for design-time validation.

Note

Use a tool such as SQL Server Management Studio to configure your permissions for your instance of SQL Server. If you cannot log on with administrative credentials, you might need to ask your administrator to grant you permissions to create databases in SQL Server.

You must ensure that the instance name that is specified for your design-time validation database is correct. For more information, see How to: Specify the Local Instance of SQL Server to use for Design-time Validation.

If you run Visual Studio without administrative permissions, the error "CREATE DATABASE permission denied in database 'master'" might appear. To resolve this error, a user with sysadmin permissions should run the following script on your design-time validation database:

USE master
GO
GRANT EXECUTE ON sp_detach_db TO public
GO

Errors when Attempting to use SQL Server 2000 Database for Design-time Validation

An error appears if you change the database that is used for design-time validation to an instance of SQL Server 2000 and then try to create or modify a database project. The error might read something like, "Incorrect syntax near 'ENABLE BROKER'". The design-time validation database uses SQL Server 2005 features.

Note

You must specify a valid instance of a SQL Server 2005 database as your design-time validation database. For more information, see How to: Specify the Local Instance of SQL Server to use for Design-time Validation.

Errors in Database Objects

When a database object contains one or more syntax errors, the icon for that database displays the error icon (a red "!"), and associated error messages appear in the Error List window. The correct row number is reported for errors returned during design-time validation by the local instance of SQL Server. However, the column number is always reported to be column one. Both the row and column are correct for SQL syntax errors.

Note

The error message that appears in the Error List window should provide information about what you can do to resolve the error. After you resolve the error and save the database object, the icon for that database object returns to its typical state.

Use of New Reserved Words in Microsoft SQL Server 2005

The following are new reserved keywords in SQL Server 2005: EXTERNAL, PIVOT, REVERT, TABLESAMPLE, and UNPIVOT. An error appears in the Output window if you use these reserved keywords as schema object names in a database project that is targeted for SQL Server 2000.

Note

To work around the restriction, you can enclose the schema object names in quotation marks. For example, you can use "CREATE TABLE [External] (c1 INT)".

Differences in Command-line Build

If you perform a command-line build when you have the project open in Visual Studio, you might not receive all the build errors that you receive when you build in the user interface.

Note

To work around the issue, close the database project in Visual Studio before you perform a command-line build.

Symmetric Keys, Asymmetric Keys, and Certificates

In Team Edition for Database Professionals, you cannot create symmetric keys, asymmetric keys, or certificates as database objects. When you import a database schema, placeholder comments are put into the pre-deployment script with the names of the keys and certificates. You must change the pre-deployment script to create those objects. Similarly, when you compare database schemas, the Schema Update Script does not contain the necessary Transact-SQL (T-SQL) commands to create missing symmetric keys, asymmetric keys, or certificates. You must export the update script to the editor and add statements to create those objects.

Dependencies and Update Scripts

To generate the correct order of objects in an update script, Schema Compare examines object dependencies. For example, if a view depends on a table, the table must be created before the view. If the object that depends on the second object does not use a schema-qualified name, the dependency might not be identified and the update or creation script might have statements in an incorrect order. This discrepancy can cause errors when you update a target to match a source or deploy changes to a database. This issue also applies to database build scripts.

Note

To work around this issue, make sure to schema-qualify the names of objects that are involved in dependent relationships. In the following example, you can guarantee that the dependency will be correctly identified if you change the end of the statement to reference [dbo].[KeysTable] instead of just KeysTable:

CREATE VIEW [NewUser].[ViewReferencingScalarFunction] AS SELECT Column2, dbo.SimpleMultiplyParamByTwo(PK_Column) AS [Function] FROM KeysTable

Errors when You Add Yourself as a User

If you are a member of the sysadmin role and you try to add yourself as a user, the following error appears: "The login already has an account under a different user name." This error occurs because you are the database owner of the design-time validation database, which means that you are also the user dbo in that database. Therefore, you cannot add yourself again as a database user.

Quoted Identifiers May Cause Problems with Project Re-load

Errors will appear when you save objects or load a database that contains quoted identifiers if the SET QUOTED_IDENTIFIER check box is cleared in the database properties. This situation might occur if you import a database schema from a database that used quoted identifiers.

Note

To work around the issue, you have two options. You can modify the object definitions to use square brackets instead of quotation marks. For example, you can change "My Table" to [My Table]. As an alternative, you can open the Project menu, click DatabaseProjectProperties, click the Database Properties tab, and select the SET QUOTED_IDENTIFIER check box.

The Internal State of the Database is Inconsistent with its Contents

You might receive the following error when you work with Team Edition for Database Professionals: "The internal state of the database project is inconsistent with its contents. Unload the project and then reload it to resolve the issue." This error indicates that somehow, the project, which maintains a list of the files that it believes that it contains, is no longer synchronized with the state of the files. The most common reason why you might encounter this error is if one of the files in your project is deleted from the disk when the database project is not open. The error can also result if problems occur when you import a database schema.

Note

To work around the issue, you must unload and reload the database project. To do so, click it in Solution Explorer. Open the Project menu, and click Unload Project. After the project is unloaded, open the Project menu, and click Reload Project.

Full text search

Full text search and the design-time validation database

If you turn off full text search in your design-time validation database and you import a schema from a database that has full text indexed objects, the objects will be imported. However, errors will appear in the Error List window for any objects that use full text indexes. The same errors will appear if, after you import the objects, you then turn off full text search in your design-time validation database.

Note

To work around the issue, you must turn on full text search in the design-time validation database. For more information, see Full-Text Search Administrator InfoCenter.

sp_fulltext_table actions in full text index definitions

Only the CREATE action is allowed in the definition of your full text index. If you want to perform an action such as ACTIVATE, you must perform it in the post-deployment script for the database. If you add other actions, the following error will appear: The main batch can not have a top level data manipulation language (DML) statement. Please remove that statement and retry the operation."

Note

To work around the issue, you must move the sp_fulltext_table statement to your post-deployment script or to a script that your post-deployment script includes. For more information about post-deployment scripts, see How to: Specify Pre-Deployment or Post-Deployment Scripts.

SQLCLR objects

By default, SQLCLR integration is disabled in Microsoft SQL Server 2005. If you import a schema from a database that has SQLCLR objects and SQLCLR integration is disabled in your design-time validation database, errors will not appear in the Error List window. However, you will receive errors if you try to execute those objects.

Note

To work around the issue, you must run the Transact-SQL editor from Team Edition for Database Professionals or a tool such as SQL Server Management Studio and connect to the server as a system administrator. Then, in a query window, execute the following:

exec sp_configure 'clr enabled', 1
reconfigure

Undo pending changes

Schema View does not automatically refresh after you use your version control system's Undo Pending Changes command. If, for example, you rename a table or a column and then revert those changes, the message "External file change, resynchronization required…" appears in Schema View.

Note

To work around the issue, you must click Synchronize on the Schema View toolbar.

Windows NT user or group not found

If your database project references a login that is not available, the error "Windows NT user or group 'DomainName\LoginName' not found. Check the name again."appears. You might encounter this issue if you work on a computer that is in a different domain than the database whose schema was imported, for example. This situation typically arises if you work at home on a database project that is created elsewhere. In this situation, you cannot build or deploy the database project.

Note

There is no workaround for this issue. You can build and deploy a database project only where the referenced logins are valid.

Duplicate Object Names and Exclude Files

If you have duplicate object names in your database project (for example, two tables that are named Orders), an error appears in the Error List window. Even if you resolve the issue by excluding the file that contains the definition for one of the objects, the error message does not immediately disappear.

Note

To work around the issue, you can click Refresh or you can edit the file that containing the object definition, rename the object, and save the file.

CREATE ASSEMBLY does not support referencing a file

You can add a CLR assembly to a Transact-SQL (T-SQL) CREATE ASSEMBLY statement by either including the binary code in the statement or specifying a file path to an assembly. Team Edition for Database Professionals does not support the latter method. If you attempt to use this syntax, the following error appears: CREATE ASSEMBLY statement can only have binary elements in its FROM clause.

Note

There is no workaround for this issue.

Conversion issues from SQL Server 2000 to SQL Server 2005

When you convert a database project from SQL Server 2000 to SQL Server 2005, the context menus for SQL Server 2005 objects do not appear in Schema View.

Note

To work around the issue, close and reopen your database project after you convert it to SQL Server 2005.

Database-qualified and Server-qualified Names

When you create an object in Team Edition for Database Professionals, the object is named according to the [schema].[object].[child] naming convention. If you want to refer to an object in another database or on another server, you can include the name of the database and the server in the following way: [server].[database].[schema].[object].[child]. If you create a stored procedure or a view that refers to an object that requires an server-qualified or database-qualified name, a warning appears.

Note

To resolve the warning, you must define a cross-database reference. For more information about cross-database references, see Overview of Cross-Database References and How to: Create Cross-Database References.

Important

Deployment will fail if your project has unresolved warnings about database-qualified or server-qualified names and you select the Treat warnings as errors check box on the Build tab of the properties for the database project. This failure results because database-qualified or server-qualified names generate warnings. You must clear the Treat warnings as errors check box if you are using database-qualified or server-qualified names.

Extended Property Support

This release of Team Edition for Database Professionals does not support extended properties on filegroups, file names, and function constraints. If you import a database schema or a script, extended properties on those object types are skipped.

In addition, extended properties that store a value of TinyInt, SmallInt, UniqueIdentifier, or bit are skipped and placed into the ScriptsIgnoredOnImport.sql file.

Note

To work around this issue, you must manually create the extended properties in a post-deployment script. For more information, see How to: Specify Pre-Deployment or Post-Deployment Scripts.

Performance of Import Database Schema

If you import a database schema while the Test Manager or Test View is open, the import operation will take significantly longer to finish. This slowdown will occur both in the New Database Project Wizard (if you chose to import a database schema) and during the Import Database Schema operation. The problem occurs even if you close the Test Manager and Test View before you import the database schema.

Note

To work around this issue, you must close the Test Manager and Test View, shut down and restart Visual Studio, and then import the database schema. For smaller schemas, you might not need to perform this step. For the AdventureWorks sample database, the import schema operation took 27 seconds without the Test Manager open and 48 seconds with the Test Manager open.

Build Error Navigation

If deployment fails, you cannot correct the error by updating the generated build script. You must correct the source file that is used to generate that build script. If you double-click a deployment error in the Error List window, the build script appears in the editor, displaying the line that caused the error.

Note

To work around this issue, you must view the build script to determine the cause of the failure, but then you must modify the source file in the database project that contains the error. For example, if the post-deployment script Permissions.sql contains an error, you must modify Permissions.sql instead of the build script.

Database projects and the TRUSTWORTHY setting

You must have sysadmin permissions to enable the TRUSTWORTHY setting for a database project or to open a database project that has the TRUSTWORTHY setting enabled.

Note

To work around this issue, if the TRUSTWORTHY setting should not be enabled, have your administrator disable the setting for the database project. If the TRUSTWORTHY setting must be enabled, all developers working on the database project must be granted sysadmin permissions to that database. If each developer is working in an isolated development environment, then each has a private copy of the database and can safely be added to the sysadmin role for that database.

Linked servers and import scripts

You might receive an error when you deploy a database project if you imported multiple scripts into a database project. This situation can occur if the same linked server was defined more than one time between those scripts.

Note

To work around this issue, you can add the following T-SQL before each sp_addlinkedserver call in the LinkedServers.sql pre-deployment script:

IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = N'<serverName>')

Syntax errors when you use cross-database references

You might receive one or more syntax errors when you save an object definition that contains a reference to an object in another database. For example, you can add a reference to the database project, define variables that are named RefServer and RefDatabase, and assign them values. Then you could define a view as follows:

CREATE VIEW [dbo].[MyView]
AS
SELECT * FROM $(RefServer).$(RefDatabase).dbo.TableName

When you save this definition, you might receive one or more error messages that indicate incorrect syntax. The error messages might reference the name of your design-time validation database, which might be confusing.

Note

To resolve this issue, you must enclose the variable names in brackets. To correct this example, change it as follows:

CREATE VIEW [dbo].[MyView]
AS
SELECT * FROM [$(RefServer)].[$(RefDatabase)].dbo.TableName

XML Indexes with Extended Properties

One or more syntax errors might appear when you save an object definition for an XML index that contains definitions for one or more extended properties. This kind of error occurs because SQL Server 2005 does not remove extended properties when an XML index is dropped and recreated. When this situation occurs, the following error might appear: "TSD4001: Property cannot be added. Property 'ExtendedPropertyName' already exists for 'XMLIndexName'. (SQL error = 15233)".

Note

To resolve this issue, you must move the definition of the extended property or properties to the post-deployment script, and you must add the following statements immediately before the definition:

IF NOT EXISTS (SELECT * FROM fn_listextendedproperty('ExtendedPropertyName', 'SCHEMA', N'SchemaName', 'TABLE', N'TableName', 'INDEX', N'XMLIndexName')

See Also

Tasks

How to: Modify Database Objects
How to: View Data Differences

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

Renaming Database Objects
Transact-SQL Reference (Transact-SQL)