Troubleshooting Database Project, Build, and Deployment Issues

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

• Errors in Database Objects

• Use of New Reserved Words in Microsoft SQL Server 2005

• Differences in Command-Line Build

• Dependencies and Update Scripts

• Quoted Identifiers May Cause Problems with Project Reload

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

• Undo Pending Changes

• Windows NT User or Group Not Found

• Duplicate Object Names and Excluded Files

• Database-Qualified and Server-Qualified Names

• Performance of Import Database Schema

• Build Error Navigation

• Syntax Errors when Using Cross-Database References

• Renaming SQLCLR Projects and Assemblies

• Unsupported Syntax for FULLTEXT STOPLIST

• SORT_IN_TEMPDB Clause Is Not Imported

• The Quoted Identifiers State Is Lost when You Import Table-Valued Functions that Contain Multiple Statements

• Some Project Errors Are Not Cleared After Building the Project

• Incorrect Syntax in Post-Deployment Script

Errors in Database Objects

When a database object contains one or more syntax errors, the associated error messages appear in the Error List window.

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 Microsoft SQL Server 2005 or SQL Server 2008.

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.

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 difference 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.

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

Quoted Identifiers May Cause Problems with Project Reload

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.

The Internal State of the Database Is Inconsistent with its Contents

You might receive the following error when you work with Visual Studio Premium: "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. This error can also result if problems occur when you import a database schema.

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.

Windows NT User or Group Not Found

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

Duplicate Object Names and Excluded 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.

Database-Qualified and Server-Qualified Names

When you create an object in Visual Studio Premium, 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 a database-qualified or server-qualified name, a warning appears.

Performance of Import Database Schema

If you import a database schema while the Test List Editor window or Test View window 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 List Editor and Test View windows before you import the database schema.

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.

Syntax Errors when Using 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.

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

Renaming SQLCLR Projects and Assemblies

You will not be able to deploy a database project that contains a reference to a SQLCLR project or assembly if you rename it but do not update the reference.

Unsupported Syntax for FULLTEXT STOPLIST

Visual Studio Premium does not model the optional FROM { [DatabaseName.] SourceStoplistName } | SYSTEM STOPLIST ] syntax for the CREATE FULLTEXT STOPLIST statement. That syntax is ignored if you import an object that uses it.

SORT_IN_TEMPDB Clause Is Not Imported

Visual Studio Premium cannot import the SORT_IN_TEMPDB clause for indexes. For example, you might have the following code:

CREATE TABLE [dbo].[IndexTable]
{
[Column1] [INT] NOT NULL
}
GO
CREATE NONCLUSTERED INDEX [SortInTempDbOn] ON [dbo].[IndexTable]
{
[Column1] ASC
} 
WITH (SORT_IN_TEMPDB = ON)
GO

The index is imported without the SORT_IN_TEMPDB clause.

The Quoted Identifiers State Is Lost when You Import Table-Valued Functions that Contain Multiple Statements

You can import table-valued functions that contain multiple statements, but the state of the Quoted Identifier string is not saved in SQL Server. When you import these functions, errors might appear because the default setting for Quoted Identifiers is ON.

Some Project Errors Are Not Cleared After Rebuilding the Project

Some errors that you might encounter in your database project do not go away after you fix the cause of the error, even if you rebuild your project. For example, if you delete the .sqlpermissions file that is referenced by your project by using Windows Explorer, an error occurs, as expected. If you use Solution Explorer to remove the file from your project and then rebuild, the error remains, even though you have corrected it.

Incorrect Syntax in Post-Deployment Script

By default, SQLCMD mode is off in the Transact-SQL editor in Visual Studio 2010. This change was made because Intellisense is not active when SQLCMD mode is enabled. When you deploy, you might see the following error if you have included statements in your post-deployment script.

Incorrect syntax near ':'. C:\MySandbox\MySolution\MyProject\Scripts\Post-Deployment\Script.PostDeployment.sql

See Also

Tasks

How to: Modify Database Objects

How to: Compare and Synchronize the Data of Two Databases

Concepts

Rename All References to a Database Object

Rename All References to a Database Object