Every time you use the SQL Change Automation window to import a change, SQL Change Automation generates a migration script.

When you click Refresh (Verify Script), SQL Change Automation validates the script by running it against the shadow database. This identifies problems before the script is deployed.

If a script fails verification, this view is shown:

Fixing script verification failures

The verification error dialog explains how to alter scripts in order to fix issues with them. The Script verification failed view shows one SQL Server error at a time. If the script contains multiple errors, it'll keep failing verification until every error is fixed. To resolve the error by hand, click Edit script to open the script in the document window and make any necessary adjustments.

If you continue to receive errors, in spite of having fixed the problems in the affected scripts, try forcing a rebuild of the shadow database. This can be done by executing the Build... Clean Solution command in Visual Studio (which will drop the shadow db from your development instance) and then clicking Refresh within the SQL Change Automation tool-window.

The verification process can catch common issues before the script is deployed. For instance:

• Missing dependencies

  • A user that does not have a related login

  • A stored procedure that depends on a full-text index being present

  • A view that is missing a dependent view

  • Unsupported objects due to dependency chains

• Invalid code in legacy objects

  • Stored procedures that still use old-style outer joins

• Syntax errors (only possible if the script was manually edited)

Excluding objects from script verification

In some situations, it may be desirable to exclude certain objects or T-SQL batches from the verification process. In particular, if your project scripts make changes to server-level objects, such as linked-servers or SQL agent jobs, having them execute during the shadow database deployment may result in those objects being overwritten. It may also be desirable to exclude certain cross-database operations from verification, such as an UPDATE statement that selects data from another database.

This can be achieved by gating your batches with the $(IsShadowDeployment) system variable:

IF '$(IsShadowDeployment)' = 0
	BEGIN
		PRINT N'Creating linked-server object [LinkedServerAlias] because IsShadowDeployment=0...';
		EXEC sp_addlinkedserver @server=N'LinkedServerAlias'
		, @srvproduct=N'sql_server'
		, @provider=N'SQLOLEDB'
		, @datasrc=N'ActualServerName'
	END
ELSE
	BEGIN
		PRINT N'Skipping linked-server object [LinkedServerAlias] creation because IsShadowDeployment=1';
	END
GO