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
Errors When You Add Yourself as a User
Quoted Identifiers May Cause Problems with Project Reload
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 Excluded Files
Database-Qualified and Server-Qualified Names
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
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
Error TSD03006 When Referencing Objects in the [sys] Schema
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 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 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 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 Team System Database Edition: "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 must 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.
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 this issue, you must turn on full-text search in the design-time validation database. For more information, see this topic on the Microsoft Web site: 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 cannot have a top-level data manipulation language (DML) statement. Please remove that statement and retry the operation."
Note
To work around this 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, SQL common language run-time (SQLCLR) integration is disabled in 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 this issue, you must run the Transact-SQL (T-SQL) editor from Database Edition, 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. For example, if 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 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 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
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 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 change 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 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 names 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.
.gif "Important note") Important Note: |
|---|
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. |
Performance of Import Database Schema
If you import a database schema while the Test List Editor window or the 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 have 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 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 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 the above 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')
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
Database Edition 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
Database Edition 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 2000 or SQL Server 2005. 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.
Error TSD03006 When Referencing Objects in the [sys] Schema
You can reference objects that are defined in the [sys] schema from your database project. If you do not have a reference to master.dbschema, error TSD03006 appears.
Note
To fix this issue, you must add a reference to master.dbschema. In Solution Explorer, right-click the References folder and click Add Database Reference. Select the Database project schema (.dbschema) radio button and click the Browse button. Navigate to [Program Files]\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas and specify the master.dbschema file. If you are not using SQL Server 2008, browse to the folder that corresponds to your version of SQL Server.
See Also
Tasks
How to: Modify Database Objects
Concepts
Terminology Overview of Database Edition
Rename All References to a Database Object