customising deployment script

waqas

Hi,

when I generate deployment script by comparing two databases I want to add additional check before I deploy the script on target database. for example I have a table in database called tblSettings that contains version information '1.6.0.0' .
if database version is correct then execute the rest of the script otherwise do not execute any sql statements in the rest of the script in a transaction safe manner. this is my condition check
DECLARE @DBVersion varchar(10)

SELECT @DBVersion=[Value] FROM tblSettings WHERE [Name]='Version'

IF @DBVersion <> '1.5.0.0'
BEGIN
PRINT ' database is wrong version ' + @DBVersion
ROLLBACK TRANSACTION
END
and I want to add this in the begining of following deployment script

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[spIsEPActive]'
GO
IF OBJECT_ID(N'[dbo].[spIsEPActive]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[spIsEPActive]
GO

@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[spContractRead]'
GO

GO
@ERROR <> 0 SET NOEXEC ON
GO

IF OBJECT_ID(N'[dbo].[spIsEPActive]', 'P') IS NULL
EXEC sp_executesql N'CREATE PROCEDURE [dbo].[spIsEPActive]

@EmployeeId INT

AS



SELECT EPActive FROM tblEPBranch where EmployeeId = @EmployeeId
'
GO

@ERROR <> 0 SET NOEXEC ON
GO

UPDATE tblSettings SET [Value]='1.7.0.0' WHERE [Name]='Version'

GO
END
COMMIT TRANSACTION
GO
@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT 'The database update failed'
END
GO

Mike U

I'm afraid there's no way to automatically add custom SQL to deployment scripts generated by Compare. You can, of course, edit the generated scripts before running them.

However, you might be interested in our new SQL Release product (http://www.red-gate.com/products/dlm/dl ... ql-release) which has the target database state checking behaviour you're trying to implement here built in.