What are the challenges you face when working across database platforms? Take the survey
Options

customising deployment script

waqaswaqas Posts: 2
edited April 10, 2015 5:39AM in SQL Compare 11
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

Comments

Sign In or Register to comment.