customising deployment script
waqas
Posts: 2
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
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
Comments
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.
Redgate Software