Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Troubleshooting Database Project, Build, and Deployment Issues

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.

NoteNote:

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.

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.

NoteNote:

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)".

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.

NoteNote:

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

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.

NoteNote:

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

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.

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.

NoteNote:

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.

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.

NoteNote:

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

NoteNote:

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

NoteNote:

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.

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.

NoteNote:

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

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.

NoteNote:

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

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.

NoteNote:

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

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.

NoteNote:

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.

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.

NoteNote:

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

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.

NoteNote:

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.

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.

NoteNote:

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.

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.

NoteNote:

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.

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.

NoteNote:

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>')

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.

NoteNote:

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

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)".

NoteNote:

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')

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.

NoteNote:

To fix this issue, remove the old reference, and add a reference to the SQLCLR project or assembly with the updated name.

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.

NoteNote:

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.

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.

NoteNote:

To fix this issue, you must manually correct the index definition whenever you import changes from the database into your project.

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.

NoteNote:

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.

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.

NoteNote:

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2015 Microsoft