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
Errors in Database Objects
When a database object contains one or more syntax errors, the associated error messages appear in the Error List window.
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 and the error is removed from 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.
Note
To work around this 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 this issue, close the database project in Visual Studio before you perform a command-line build.
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.
Note
To work around this issue, make sure to schema-qualify the names of objects that are involved in dependent relationships. In Visual Studio Premium and Visual Studio Ultimate, you can use database refactoring to fully qualify object names. For more information, see Fully Qualify the Names of Database Objects.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
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.
Note
To work around this 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 DatabaseProject Properties, 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 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.
Note
To work around this issue, you can click Synchronize in Schema View. If that does not work, you can unload and reload the database project. To do this, 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.
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 this 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 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.
Note
You can create the user, omitting the FOR LOGIN clause. For example, you can use the following: CREATE USER [Domain\someLogin]. The user will be created when you deploy the database project and will be associated with the [Domain\someLogin] login, but you do not need a reference to that login in your 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.
Note
To work around this issue, you can click Refresh or you can modify the file that contains the object definition, rename the object, and save the file.
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.
Note
To resolve this warning, you must define a cross-database reference. For more information about cross-database references, see Using References in Database Projects and How to: Add References to Database Projects.
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. If you are using database-qualified or server-qualified names, you must clear the Treat warnings as errors check box.
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.
Note
To work around this issue, you must close the Test List Editor and Test View windows, shut down and restart Visual Studio, and then import the database schema. For smaller schemas, you might not need to perform these steps; for the AdventureWorks sample database, the import schema operation took 27 seconds without the Test List Editor window open and 48 seconds with the Test List Editor window 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 Script.PostDeployment.sql contains an error, you must modify Script.PostDeployment.sql instead of the build script.
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.
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
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.
Note
To fix this issue, remove the old reference, and add a reference to the SQLCLR project or assembly with the updated name.
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.
Note
To fix this issue, you must manually add stopwords to your post-deployment script. For more information, see How to: Specify Pre-Deployment or Post-Deployment Scripts.
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.
Note
To fix this issue, you must manually correct the index definition whenever you import changes from the database into your project.
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.
Note
To fix this issue, you must change the Quoted Identifiers setting for any table-valued functions that contain multiple statements after you import objects and settings or after you write updates from your database to your project.
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.
Note
To fix this issue, you must click Reanalyze Project on the toolbar in Solution Explorer.
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
Note
To fix this issue, you must enable SQLCMD mode. To enable SQLCMD mode, click SQLCMD Mode on the Transact-SQL Editor toolbar.
See Also
Tasks
How to: Modify Database Objects
How to: Compare and Synchronize the Data of Two Databases