Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

SQL Utilities

No description
by

Luk Luyckx

on 10 October 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of SQL Utilities

SQL Utilities SQL Compiler DB Installer SQL Unit tests Solution with ".Test" suffix Add namespace directories (Test.XXX) Add TestFixture directories Add Unit tests (.sql files) Add settings.xml <?xml version="1.0" encoding="utf-8" ?>
<settings>
<connectionstring>...</connectionstring>
</settings> Create settings file with connectionstring Create .sql files with test code DECLARE
@szSN VARCHAR(32),
@lTrafficID INT,
@szType VARCHAR(20),
@clip VARCHAR(32),
@destination VARCHAR(32),
@dLastActivity DATETIME
SELECT
@szSN = '32486999991', /*Active, Prepaid*/
@lTrafficID = 1,
@szType = 'F',
@clip = '32486000001',
@destination = '32486000002'

SELECT @dLastActivity = dLastActivity FROM PinInfo WHERE szSN = @szSN
EXEC Assert.IsNull @dLastActivity
INSERT INTO CDR (szSN, lTrafficID, szType, clip, destination)
VALUES (@szSN, @lTrafficID, @szType, @clip, @destination)
SELECT @dLastActivity = dLastActivity FROM PinInfo WHERE szSN = @szSN
EXEC Assert.IsNull @dLastActivity Assert procedures Add the tool to Visual Studio ... and run it... ... this will execute NUnit and you can run the tests Some remarks Running the tool wil generate a NUnit assembly with Tests for each .sql file, adding .sql files means regenerating the assembly


The NUnit tests make calls to the .sql files, so changing the content of the files doesn't mean you need to regenerate the assembly


The VS.Net Tool calls NUnit from the References folder, so make sure these are relatively at the same place -- VERSION $TFSCHANGESET$
-- GRANT EXECUTE TO public

ALTER FUNCTION fnGetRecentDate
(
@date1 DATETIME,
@date2 DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @returndate DATETIME
SELECT @returndate = MAX(dateval)
FROM
(
SELECT @date1 AS dateval UNION ALL
SELECT @date2
) dates

RETURN @returndate
END ----------------------------------------------------------------------------------------------------
-- Name: fnGetRecentDate
-- Path: C:\Sources\tfs.artilium.com\ARTA7\Db\Artilium\Trunk\LifeCycleManagement\LifeCycleManagement\Functions\fnGetRecentDate.sql
-- Object: dbo.fnGetRecentDate
-- Dependencies: (none)
----------------------------------------------------------------------------------------------------

DECLARE @MD5 VARCHAR(32), @ModifyDateProperty DATETIME, @ActualModifyDate DATETIME
IF OBJECT_ID('dbo.fnGetRecentDate', 'FN') IS NOT NULL
BEGIN
SELECT @MD5 = CONVERT(VARCHAR(32), value)
FROM sys.Extended_Properties
WHERE major_id = OBJECT_ID('dbo.fnGetRecentDate', 'FN')
AND name = 'MD5'

SELECT @ModifyDateProperty = CONVERT(DATETIME, value)
FROM sys.Extended_Properties
WHERE major_id = OBJECT_ID('dbo.fnGetRecentDate', 'FN')
AND name = 'ModifyDate'

SELECT @ActualModifyDate = modify_date
FROM sys.Objects
WHERE object_id = OBJECT_ID('dbo.fnGetRecentDate', 'FN')
END

EXEC ('IF OBJECT_ID(''tempdb..#InstallParams'') IS NOT NULL
UPDATE #InstallParams SET value = value WHERE name = ''@ForceInstall'' AND value = ''1''')
IF @@ROWCOUNT > 0 OR ISNULL(@MD5, '0') <> '37AAE7EA1012BA69F8847E523035261E' OR ISNULL(@ModifyDateProperty, '1970-01-01') <> ISNULL(@ActualModifyDate, GETDATE())
BEGIN TRY
IF OBJECT_ID('dbo.fnGetRecentDate', 'FN') IS NULL
BEGIN
PRINT 'CREATE FUNCTION dbo.fnGetRecentDate...'
EXEC ('CREATE FUNCTION [dbo].[fnGetRecentDate]() RETURNS INT AS BEGIN RETURN NULL END')
END --IF OBJECT_ID('dbo.fnGetRecentDate', 'FN') IS NULL
ELSE PRINT 'ALTER FUNCTION dbo.fnGetRecentDate...'
EXEC sp_executesql N'-- VERSION $TFSCHANGESET$
-- GRANT EXECUTE TO public

ALTER FUNCTION fnGetRecentDate
(
@date1 DATETIME,
@date2 DATETIME
)
RETURNS DATETIME
AS
BEGIN

DECLARE @returndate DATETIME
SELECT @returndate = MAX(dateval)

FROM
(
SELECT @date1 AS dateval UNION ALL
SELECT @date2
)
dates

RETURN @returndate
END'

GRANT EXECUTE ON dbo.fnGetRecentDate TO public
-- ADD Version Information
DECLARE
@Version NVARCHAR(64),
@ModuleId NVARCHAR(64),
@App_name SYSNAME,
@ModifyDate DATETIME

IF OBJECT_ID('tempdb..#InstallParams') IS NOT NULL
BEGIN
SELECT @Version = value FROM #InstallParams WHERE name = 'version'
SELECT @ModuleId = value FROM #InstallParams WHERE name = 'moduleid'
END
SET@App_name = APP_NAME()

SET @Version = ISNULL(@Version,'X') + '.$TFSCHANGESET$'

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Version', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL))
EXEC sp_updateextendedproperty 'Version', @Version, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Version', @Version, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Module', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL))
EXEC sp_updateextendedproperty 'Module', @ModuleId, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Module', @ModuleId, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('ChangeSet', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL))
EXEC sp_updateextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Install Application', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL))
EXEC sp_updateextendedproperty 'Install Application', @App_name, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Install Application', @App_name, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL

SELECT @ModifyDate = o.modify_date
FROM sys.objects o
WHERE object_id = (SELECT OBJECT_ID(objname) FROM fn_listextendedproperty('Version', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL))

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('ModifyDate', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL))
EXEC sp_updateextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('MD5', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL))
EXEC sp_updateextendedproperty 'MD5', '37AAE7EA1012BA69F8847E523035261E', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'MD5', '37AAE7EA1012BA69F8847E523035261E', 'SCHEMA', 'dbo', 'FUNCTION', 'fnGetRecentDate', NULL, NULL




PRINT ' [ OK ] FUNCTION dbo.fnGetRecentDate created/changed.'
END TRY -- IF ISNULL(@ChangeSet, 0) < 1
BEGIN CATCH
PRINT ' ERROR: ' + ERROR_MESSAGE()
PRINT ' Nr: ' + ISNULL(CONVERT(VARCHAR,ERROR_NUMBER()),'') +
', Lvl: ' + ISNULL(CONVERT(VARCHAR,ERROR_SEVERITY()),'') +
', State: ' + ISNULL(CONVERT(VARCHAR,ERROR_STATE()),'') +
', Line: ' + ISNULL(CONVERT(VARCHAR,ERROR_LINE()),'') +
', Proc: ' + ISNULL(ERROR_PROCEDURE(),'')
PRINT ' [FAILED] FUNCTION dbo.fnGetRecentDate'
-- Rethrow error
DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)
END CATCH
ELSE PRINT 'FUNCTION dbo.fnGetRecentDate is up to date.'


GO -- NOCONVERT

IF NOT EXISTS(SELECT 1 FROM SETTINGS WHERE Id = 10125001)
BEGIN
INSERT INTO SETTINGS (ID, VALUE, TEXT, Description)
VALUES (10125001, 0, 'fnLCMGetJobsToQueue',
'Decorator object for dbo.fnGetJobsToQueue')
END
ELSE IF EXISTS
(SELECT 1 FROM SETTINGS WHERE Id = 10125001
AND ISNULL(TEXT, 'fnGetJobsToQueue') = 'fnGetJobsToQueue')BEGIN
UPDATE SETTINGS SET TEXT = 'fnLCMGetJobsToQueue' WHERE Id = 10125001
END ----------------------------------------------------------------------------------------------------
-- Name: SystemSetting
-- Path: C:\Sources\tfs.artilium.com\ARTA7\Db\Artilium\Trunk\LifeCycleManagement\LifeCycleManagement\_Upgrade Scripts\SystemSetting.sql
-- Dependencies: (none)
----------------------------------------------------------------------------------------------------
-- NOCONVERT

IF NOT EXISTS(SELECT 1 FROM SETTINGS WHERE Id = 10125001)
BEGIN
INSERT INTO SETTINGS (ID, VALUE, TEXT, Description)
VALUES (10125001, 0, 'fnLCMGetJobsToQueue',
'Decorator object for dbo.fnGetJobsToQueue')
END
ELSE IF EXISTS
(SELECT 1 FROM SETTINGS WHERE Id = 10125001
AND ISNULL(TEXT, 'fnGetJobsToQueue') = 'fnGetJobsToQueue')BEGIN
UPDATE SETTINGS SET TEXT = 'fnLCMGetJobsToQueue' WHERE Id = 10125001
END
GO -- VERSION $TFSCHANGESET$

CREATE TABLE LCMPinInfo (
szSN VARCHAR(32) NOT NULL,
dLastActivity DateTime NULL,
dStatusModify DateTime NULL,
cLCMManualMovement int NOT NULL DEFAULT 0,
cLCMDisableReactivation int NOT NULL DEFAULT 0

CONSTRAINT PK_LCMPinInfo PRIMARY KEY CLUSTERED (szSN ASC)
) CREATE TABLE - LCMPinInfo.sql -- VERSION $TFSCHANGESET$
-- USES LCMPinInfo

IF EXISTS (
SELECT 1 FROM sys.indexes
WHERE name = N'PK_LCMPinInfo'
AND object_id = OBJECT_ID(N'LCMPinInfo')
AND type = 2)
BEGIN
ALTER TABLE LCMPinInfo DROP CONSTRAINT PK_LCMPinInfo

ALTER TABLE [dbo].[LCMPinInfo]
ADD CONSTRAINT [PK_LCMPinInfo] PRIMARY KEY CLUSTERED ([szSN] ASC)
END
GO UPDATE TABLE - LCMPinInfo_Update.sql ----------------------------------------------------------------------------------------------------
-- Name: LCMPininfo
-- Path: C:\Sources\tfs.artilium.com\ARTA7\Db\Artilium\Trunk\LifeCycleManagement\LifeCycleManagement\Tables\LCMPininfo.sql
-- Object: dbo.LCMPinInfo
-- Dependencies: (none)
----------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.LCMPinInfo') IS NULL
BEGIN TRY
PRINT 'CREATE TABLE dbo.LCMPinInfo...'
-- VERSION $TFSCHANGESET$

CREATE TABLE LCMPinInfo (
szSN VARCHAR(32) NOT NULL,
dLastActivity DateTime NULL,
dStatusModify DateTime NULL,
cLCMManualMovement int NOT NULL DEFAULT 0,
cLCMDisableReactivation int NOT NULL DEFAULT 0

CONSTRAINT PK_LCMPinInfo PRIMARY KEY CLUSTERED (szSN ASC)
)
-- ADD Version Information
DECLARE
@Version NVARCHAR(64),
@ModuleId NVARCHAR(64),
@App_name SYSNAME,
@ModifyDate DATETIME

IF OBJECT_ID('tempdb..#InstallParams') IS NOT NULL
BEGIN
SELECT @Version = value FROM #InstallParams WHERE name = 'version'
SELECT @ModuleId = value FROM #InstallParams WHERE name = 'moduleid'
END
SET @App_name = APP_NAME()

SET @Version = ISNULL(@Version,'X') + '.$TFSCHANGESET$'

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Version', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'Version', @Version, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Version', @Version, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Module', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'Module', @ModuleId, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Module', @ModuleId, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('ChangeSet', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Install Application', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'Install Application', @App_name, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Install Application', @App_name, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

SELECT @ModifyDate = o.modify_date
FROM sys.objects o
WHERE object_id = (SELECT OBJECT_ID(objname) FROM fn_listextendedproperty('Version', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('ModifyDate', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('MD5', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'MD5', '056486656F1299CE09E97B671F5FFD76', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'MD5', '056486656F1299CE09E97B671F5FFD76', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

DECLARE
@SchemaName SYSNAME,
@TableName SYSNAME,
@ColumnName SYSNAME,
@ColumnId INT

SELECT @SchemaName = 'dbo', @TableName = 'LCMPinInfo', @ColumnName = '', @ColumnId = 0

WHILE 0=0
BEGIN
SELECT TOP 1 @ColumnName = c.name, @ColumnId = c.column_id
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.SCHEMA_ID = SCHEMA_ID(@SchemaName) AND t.name = @TableName
AND c.column_id > @ColumnId
IF @@ROWCOUNT = 0 OR @ColumnName IS NULL BREAK

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('Version', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'Version', @Version, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('Module', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'Module', @ModuleId, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('ChangeSet', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('Install Application', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'Install Application', @App_name, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

SELECT @ModifyDate = o.modify_date
FROM sys.objects o
WHERE object_id = (SELECT OBJECT_ID(objname) FROM fn_listextendedproperty('Version', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('ModifyDate', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('ColumnInstallType', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'ColumnInstallType', 'CREATE', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName
END

PRINT ' [ OK ] TABLE dbo.LCMPinInfo created.'
END TRY --IF OBJECT_ID('dbo.LCMPinInfo') IS NULL
BEGIN CATCH
PRINT ' ERROR: ' + ERROR_MESSAGE()
PRINT ' Nr: ' + ISNULL(CONVERT(VARCHAR,ERROR_NUMBER()),'') +
', Lvl: ' + ISNULL(CONVERT(VARCHAR,ERROR_SEVERITY()),'') +
', State: ' + ISNULL(CONVERT(VARCHAR,ERROR_STATE()),'') +
', Line: ' + ISNULL(CONVERT(VARCHAR,ERROR_LINE()),'') +
', Proc: ' + ISNULL(ERROR_PROCEDURE(),'')
PRINT ' [FAILED] TABLE dbo.LCMPinInfo'
-- Rethrow error
DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)
END CATCH
ELSE PRINT 'TABLE dbo.LCMPinInfo already exists.'
GO ----------------------------------------------------------------------------------------------------
-- Name: LCMPinInfo_Update
-- Path: C:\Sources\tfs.artilium.com\ARTA7\Db\Artilium\Trunk\LifeCycleManagement\LifeCycleManagement\Tables\LCMPinInfo_Update.sql
-- Object: dbo.LCMPinInfo
-- Dependencies: LCMPininfo,
----------------------------------------------------------------------------------------------------

IF OBJECT_ID('dbo.LCMPinInfo') IS NOT NULL
BEGIN TRY
PRINT 'ALTER TABLE dbo.LCMPinInfo...'
EXEC sp_executesql N'-- VERSION $TFSCHANGESET$
-- USES LCMPinInfo

IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = N''PK_LCMPinInfo'' AND object_id = OBJECT_ID(N''LCMPinInfo'') AND type = 2)
BEGIN
ALTER TABLE LCMPinInfo DROP CONSTRAINT PK_LCMPinInfo

ALTER TABLE [dbo].[LCMPinInfo] ADD CONSTRAINT [PK_LCMPinInfo] PRIMARY KEY CLUSTERED ([szSN] ASC)
END
'

-- ADD Version Information
DECLARE
@Version NVARCHAR(64),
@ModuleId NVARCHAR(64),
@App_name SYSNAME,
@ModifyDate DATETIME

IF OBJECT_ID('tempdb..#InstallParams') IS NOT NULL
BEGIN
SELECT @Version = value FROM #InstallParams WHERE name = 'version'
SELECT @ModuleId = value FROM #InstallParams WHERE name = 'moduleid'
END
SET @App_name = APP_NAME()

SET @Version = ISNULL(@Version,'X') + '.$TFSCHANGESET$'

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Version', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'Version', @Version, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Version', @Version, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Module', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'Module', @ModuleId, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Module', @ModuleId, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('ChangeSet', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('Install Application', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'Install Application', @App_name, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'Install Application', @App_name, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

SELECT @ModifyDate = o.modify_date
FROM sys.objects o
WHERE object_id = (SELECT OBJECT_ID(objname) FROM fn_listextendedproperty('Version', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('ModifyDate', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

IF EXISTS (SELECT 1 FROM fn_listextendedproperty('MD5', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL))
EXEC sp_updateextendedproperty 'MD5', '6F3E30808BA81EC5E9B350A4773987F3', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL
ELSE
EXEC sp_addextendedproperty 'MD5', '6F3E30808BA81EC5E9B350A4773987F3', 'SCHEMA', 'dbo', 'TABLE', 'LCMPinInfo', NULL, NULL

DECLARE
@SchemaName SYSNAME,
@TableName SYSNAME,
@ColumnName SYSNAME,
@ColumnId INT

SELECT @SchemaName = 'dbo', @TableName = 'LCMPinInfo', @ColumnName = '', @ColumnId = 0

WHILE 0=0
BEGIN
SELECT TOP 1 @ColumnName = c.name, @ColumnId = c.column_id
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE t.SCHEMA_ID = SCHEMA_ID(@SchemaName) AND t.name = @TableName
AND c.column_id > @ColumnId
IF @@ROWCOUNT = 0 OR @ColumnName IS NULL BREAK

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('Version', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'Version', @Version, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('Module', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'Module', @ModuleId, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('ChangeSet', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'ChangeSet', '$TFSCHANGESET$', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('Install Application', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'Install Application', @App_name, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

SELECT @ModifyDate = o.modify_date
FROM sys.objects o
WHERE object_id = (SELECT OBJECT_ID(objname) FROM fn_listextendedproperty('Version', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('ModifyDate', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'ModifyDate', @ModifyDate, 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName

IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty('ColumnInstallType', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName))
EXEC sp_addextendedproperty 'ColumnInstallType', 'ALTER', 'SCHEMA', @SchemaName, 'TABLE', @TableName, 'COLUMN', @ColumnName
END

PRINT ' [ OK ] TABLE dbo.LCMPinInfo changed.'
END TRY --IF OBJECT_ID('dbo.LCMPinInfo') IS NULL
BEGIN CATCH
PRINT ' ERROR: ' + ERROR_MESSAGE()
PRINT ' Nr: ' + ISNULL(CONVERT(VARCHAR,ERROR_NUMBER()),'') +
', Lvl: ' + ISNULL(CONVERT(VARCHAR,ERROR_SEVERITY()),'') +
', State: ' + ISNULL(CONVERT(VARCHAR,ERROR_STATE()),'') +
', Line: ' + ISNULL(CONVERT(VARCHAR,ERROR_LINE()),'') +
', Proc: ' + ISNULL(ERROR_PROCEDURE(),'')
PRINT ' [FAILED] TABLE dbo.LCMPinInfo'
-- Rethrow error
DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorLine)
END CATCH
ELSE PRINT 'TABLE dbo.LCMPinInfo doesn''t exists.'
GO /*<scriptInfo>
<version>$BRANCHNUMBER$.$BUILDLABEL$</version>
<moduleId>20119000</moduleId>
<description>VERSION DB Life Cycle Management</description>
<dependsOn moduleId="20000000" version="7.48.00.004" />
<dependsOn moduleId="20102000" version="7.48.00.002" />
<dependsOn moduleId="20103000" version="7.48.00.000" />
<dependsOn moduleId="20122000" version="7.48.00.000" />
<dependsOn moduleId="20125000" version="7.48.00.001" />
<dbType>All</dbType>
<installer>CHECKED</installer>
<parameter name="@JOB_LCM_AutomaticHandlingJob" type="INT" default="0" description="Installs/Updates the 'LCM Automatic Handling' job. (LCMAutomaticHandlingJob)" />
</scriptInfo>*/
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULL_DFLT_ON ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
GO
IF DB_NAME()='master'
BEGIN
RAISERROR ('May not be loaded in the master DB.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('May not be loaded in the master DB.', 20, 1) WITH LOG
END
GO
IF OBJECT_ID('tempdb..#InstallParams') IS NOT NULL
DROP TABLE #InstallParams

CREATE TABLE #InstallParams (name NVARCHAR(256), value NVARCHAR(256))
INSERT INTO #InstallParams VALUES ('@InstallStartTime', CONVERT(NVARCHAR, GETUTCDATE(), 126))
INSERT INTO #InstallParams VALUES ('@ForceInstall', '0')
GO
IF OBJECT_ID('Settings', 'U') IS NOT NULL
BEGIN
IF EXISTS (SELECT 1 FROM Settings WHERE ID = 1000000000 AND Description LIKE '%DBType=RESELLER%')
INSERT INTO #InstallParams VALUES ('@DBType', 'RESELLER')
ELSE IF EXISTS (SELECT 1 FROM Settings WHERE ID = 1000000000 AND Description LIKE '%DBType=CENTRAL%')
INSERT INTO #InstallParams VALUES ('@DBType', 'CENTRAL')
END
GO
IF OBJECT_ID('tempdb..#ModuleInfo') IS NOT NULL
DROP TABLE #ModuleInfo

SELECT
XML.Module.value('./@id', 'INT') ModuleID,
XML.Module.value('./@description', 'VARCHAR(255)') Description,
XML.Module.value('./@version', 'VARCHAR(255)') Version,
XML.Module.value('./@date', 'DATETIME') Date
INTO #ModuleInfo
FROM (
SELECT CONVERT(XML, CONVERT(VARCHAR(8000), value)) value
FROM sys.Extended_Properties
WHERE class = 0
AND name LIKE '{%}'
AND ISNUMERIC(SUBSTRING(name, 2, LEN(name) - 2)) = 1) AS properties
CROSS APPLY value.nodes('Module') AS XML(Module)

IF OBJECT_ID('Settings', 'U') IS NOT NULL
BEGIN
INSERT INTO #ModuleInfo (ModuleID, Description, Version, Date)
SELECT
ID AS ModuleID,
Description AS Description,
SUBSTRING(STR(value, 8), 1, 1) + '.' +
SUBSTRING(STR(value, 8), 2, 2) + '.' +
SUBSTRING(STR(value, 8), 4, 2) + '.' +
SUBSTRING(STR(value, 8), 6, 3) AS Version,
CONVERT(DATETIME, TEXT) AS Date
FROM Settings
WHERE ID BETWEEN 20000000 AND 30000000
AND ID NOT IN (SELECT ModuleID FROM #ModuleInfo)
END
GO
----------------------------------------------------------------------------------------------------
-- Name: ScriptInfo
-- Path: C:\Sources\tfs.artilium.com\ARTA7\Db\Artilium\Trunk\LifeCycleManagement\LifeCycleManagement\ScriptInfo.sql
-- Dependencies: (none)
----------------------------------------------------------------------------------------------------
DECLARE @Version VARCHAR(256), @InstalledVersion VARCHAR(16)
SET @Version = REPLACE('7.48.00.004', '.', '')
IF ISNUMERIC(@Version) = 0
BEGIN
RAISERROR ('Illegal version 7.48.00.004 for package 20000000 specified.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Illegal version 7.48.00.004 for package 20000000 specified.', 20, 1) WITH LOG
END
ELSE
BEGIN
SELECT @InstalledVersion = REPLACE(Version, '.', '') FROM #ModuleInfo WHERE ModuleID = 20000000

IF @InstalledVersion IS NULL
BEGIN
RAISERROR ('Expected minimum version 7.48.00.004 for package 20000000 not installed.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Expected minimum version 7.48.00.004 for package 20000000 not installed.', 20, 1) WITH LOG
END
ELSE IF @InstalledVersion < @Version
BEGIN
DECLARE @Message VARCHAR(256)
SET @Message = 'Package 20000000 does not have minimum version 7.48.00.004. (Installed version: ' + @InstalledVersion + ')'
RAISERROR (@Message, 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR (@Message, 20, 1) WITH LOG
END
END
GO
DECLARE @Version VARCHAR(256), @InstalledVersion VARCHAR(16)
SET @Version = REPLACE('7.48.00.002', '.', '')
IF ISNUMERIC(@Version) = 0
BEGIN
RAISERROR ('Illegal version 7.48.00.002 for package 20102000 specified.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Illegal version 7.48.00.002 for package 20102000 specified.', 20, 1) WITH LOG
END
ELSE
BEGIN
SELECT @InstalledVersion = REPLACE(Version, '.', '') FROM #ModuleInfo WHERE ModuleID = 20102000

IF @InstalledVersion IS NULL
BEGIN
RAISERROR ('Expected minimum version 7.48.00.002 for package 20102000 not installed.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Expected minimum version 7.48.00.002 for package 20102000 not installed.', 20, 1) WITH LOG
END
ELSE IF @InstalledVersion < @Version
BEGIN
DECLARE @Message VARCHAR(256)
SET @Message = 'Package 20102000 does not have minimum version 7.48.00.002. (Installed version: ' + @InstalledVersion + ')'
RAISERROR (@Message, 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR (@Message, 20, 1) WITH LOG
END
END
GO
DECLARE @Version VARCHAR(256), @InstalledVersion VARCHAR(16)
SET @Version = REPLACE('7.48.00.000', '.', '')
IF ISNUMERIC(@Version) = 0
BEGIN
RAISERROR ('Illegal version 7.48.00.000 for package 20103000 specified.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Illegal version 7.48.00.000 for package 20103000 specified.', 20, 1) WITH LOG
END
ELSE
BEGIN
SELECT @InstalledVersion = REPLACE(Version, '.', '') FROM #ModuleInfo WHERE ModuleID = 20103000

IF @InstalledVersion IS NULL
BEGIN
RAISERROR ('Expected minimum version 7.48.00.000 for package 20103000 not installed.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Expected minimum version 7.48.00.000 for package 20103000 not installed.', 20, 1) WITH LOG
END
ELSE IF @InstalledVersion < @Version
BEGIN
DECLARE @Message VARCHAR(256)
SET @Message = 'Package 20103000 does not have minimum version 7.48.00.000. (Installed version: ' + @InstalledVersion + ')'
RAISERROR (@Message, 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR (@Message, 20, 1) WITH LOG
END
END
GO
DECLARE @Version VARCHAR(256), @InstalledVersion VARCHAR(16)
SET @Version = REPLACE('7.48.00.000', '.', '')
IF ISNUMERIC(@Version) = 0
BEGIN
RAISERROR ('Illegal version 7.48.00.000 for package 20122000 specified.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Illegal version 7.48.00.000 for package 20122000 specified.', 20, 1) WITH LOG
END
ELSE
BEGIN
SELECT @InstalledVersion = REPLACE(Version, '.', '') FROM #ModuleInfo WHERE ModuleID = 20122000

IF @InstalledVersion IS NULL
BEGIN
RAISERROR ('Expected minimum version 7.48.00.000 for package 20122000 not installed.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Expected minimum version 7.48.00.000 for package 20122000 not installed.', 20, 1) WITH LOG
END
ELSE IF @InstalledVersion < @Version
BEGIN
DECLARE @Message VARCHAR(256)
SET @Message = 'Package 20122000 does not have minimum version 7.48.00.000. (Installed version: ' + @InstalledVersion + ')'
RAISERROR (@Message, 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR (@Message, 20, 1) WITH LOG
END
END
GO
DECLARE @Version VARCHAR(256), @InstalledVersion VARCHAR(16)
SET @Version = REPLACE('7.48.00.001', '.', '')
IF ISNUMERIC(@Version) = 0
BEGIN
RAISERROR ('Illegal version 7.48.00.001 for package 20125000 specified.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Illegal version 7.48.00.001 for package 20125000 specified.', 20, 1) WITH LOG
END
ELSE
BEGIN
SELECT @InstalledVersion = REPLACE(Version, '.', '') FROM #ModuleInfo WHERE ModuleID = 20125000

IF @InstalledVersion IS NULL
BEGIN
RAISERROR ('Expected minimum version 7.48.00.001 for package 20125000 not installed.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Expected minimum version 7.48.00.001 for package 20125000 not installed.', 20, 1) WITH LOG
END
ELSE IF @InstalledVersion < @Version
BEGIN
DECLARE @Message VARCHAR(256)
SET @Message = 'Package 20125000 does not have minimum version 7.48.00.001. (Installed version: ' + @InstalledVersion + ')'
RAISERROR (@Message, 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR (@Message, 20, 1) WITH LOG
END
END
GO
INSERT INTO #InstallParams VALUES ('version', '1.00.00.000')
GO
INSERT INTO #InstallParams VALUES ('moduleid', '20119000')
INSERT INTO #InstallParams VALUES ('moduledescription', 'VERSION DB Life Cycle Management')
INSERT INTO #InstallParams VALUES ('MinimumClientVersion', '')


DECLARE
@Version VARCHAR(256),
@CurrentVersion VARCHAR(256),
@ModuleID INT,
@ModuleDescription VARCHAR(256),
@Date VARCHAR(32),
@VersionINT INT,
@CurrentVersionINT INT,
@MinimumClientVersion VARCHAR(256)
SELECT
@ModuleID = 20119000,
@ModuleDescription = 'VERSION DB Life Cycle Management',
@Date = CONVERT(VARCHAR(32), GETDATE(), 120),
@MinimumClientVersion = ''
SELECT @Version = value FROM #InstallParams WHERE name = 'version'
SELECT @CurrentVersion = Version FROM #ModuleInfo WHERE ModuleID = @ModuleID

INSERT INTO #InstallParams VALUES ('current version', ISNULL(@CurrentVersion, ''))

SELECT
@VersionINT = CONVERT(INT, ISNULL(REPLACE(@Version, '.', ''), '0')),
@CurrentVersionINT = CONVERT(INT, ISNULL(REPLACE(@CurrentVersion, '.', ''), '0'))

IF @CurrentVersion IS NULL
SET @ModuleDescription = @ModuleDescription + ' [' + @Date + '] INSTALLING ' + @Version
ELSE IF @CurrentVersionINT > @VersionINT
BEGIN
IF NOT EXISTS(SELECT 1 FROM #InstallParams WHERE name = '@ForceInstall' AND value = '1')
BEGIN
RAISERROR ('Unable to downgrade to a previous version.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Unable to downgrade to a previous version.', 20, 1) WITH LOG
END
ELSE
BEGIN
PRINT 'WARN forcing reinstall of older version.'
SET @ModuleDescription = @ModuleDescription + ' [' + @Date + '] DOWNGRADING ' + @CurrentVersion + ' TO ' + @Version
END
END
ELSE
BEGIN
IF @CurrentVersionINT = @VersionINT
BEGIN
PRINT 'WARN reinstalling current version'
SET @ModuleDescription = @ModuleDescription + ' [' + @Date + '] REINSTALLING ' + @Version
END
ELSE
BEGIN
SET @ModuleDescription = @ModuleDescription + ' [' + @Date + '] UPGRADING ' + @CurrentVersion + ' TO ' + @Version
END
END

IF OBJECT_ID('Settings', 'U') IS NOT NULL
BEGIN
IF EXISTS (SELECT 1 FROM Settings WHERE ID = @ModuleID)
UPDATE Settings SET Description = @ModuleDescription WHERE ID = @ModuleID
ELSE
INSERT Settings VALUES (@ModuleID, NULL, @Date, @ModuleDescription)
END

DECLARE @ModuleProperty SYSNAME, @ModuleInfo VARCHAR(8000)
SELECT
@ModuleProperty = '{' + LTRIM(STR(@ModuleID)) + '}',
@ModuleInfo = CONVERT(VARCHAR(8000), (SELECT 1 AS Tag, 0 AS Parent,
@ModuleID [Module!1!id],
@ModuleDescription [Module!1!description],
NULL [Module!1!version],
@Date [Module!1!date],
@MinimumClientVersion [Module!1!minimumClientVersion]
FOR XML EXPLICIT))

IF EXISTS (SELECT 1 FROM fn_listextendedproperty(@ModuleProperty, NULL, NULL, NULL, NULL, NULL, NULL))
EXEC sp_updateextendedproperty @ModuleProperty, @ModuleInfo, NULL, NULL, NULL, NULL, NULL, NULL
ELSE
EXEC sp_addextendedproperty @ModuleProperty, @ModuleInfo, NULL, NULL, NULL, NULL, NULL, NULL
GO
INSERT INTO #InstallParams VALUES ('@JOB_LCM_AutomaticHandlingJob', '$@JOB_LCM_AutomaticHandlingJob$') --<-- Change '$@JOB_LCM_AutomaticHandlingJob$' to correct parameter value
UPDATE #InstallParams SET value = '0' WHERE name = '@JOB_LCM_AutomaticHandlingJob' AND value = '$@JOB_LCM_AutomaticHandlingJob$'
IF EXISTS (SELECT 1 FROM #InstallParams WHERE name = '@JOB_LCM_AutomaticHandlingJob' AND value = '$@JOB_LCM_AutomaticHandlingJob$')
BEGIN
RAISERROR ('Parameter @JOB_LCM_AutomaticHandlingJob must have a value.', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:00.500'
RAISERROR ('Parameter @JOB_LCM_AutomaticHandlingJob must have a value.', 20, 1) WITH LOG
END

GO

-- ................

GO
DECLARE
@Version VARCHAR(255),
@ModuleID INT,
@ModuleDescription VARCHAR(256),
@Date DATETIME,
@ModuleProperty SYSNAME,
@MinimumClientVersion VARCHAR(255)

SELECT @Version = value FROM #InstallParams WHERE name = 'version'
SELECT @ModuleID = value FROM #InstallParams WHERE name = 'moduleid'
SELECT @MinimumClientVersion = value FROM #InstallParams WHERE name = 'minimumClientVersion'
SET @ModuleProperty = '{' + LTRIM(STR(@ModuleID)) + '}'

IF NOT EXISTS(SELECT 1 FROM #InstallParams WHERE name = '@InstallFail' AND value = '1')
SELECT @ModuleDescription = value FROM #InstallParams WHERE name = 'moduledescription'
ELSE
SELECT @ModuleDescription = XML.Module.value('./@description', 'VARCHAR(255)')
FROM (
SELECT CONVERT(XML, CONVERT(VARCHAR(8000), value)) value
FROM sys.Extended_Properties
WHERE class = 0 AND name = @ModuleProperty) AS properties
CROSS APPLY value.nodes('Module') AS XML(Module)

SET @Date = GETDATE()

IF OBJECT_ID('Settings', 'U') IS NOT NULL
BEGIN
IF EXISTS (SELECT 1 FROM Settings WHERE ID = @ModuleID)
UPDATE Settings
SET value = REPLACE(@Version, '.', ''),
text = CONVERT(VARCHAR(32), @Date, 120),
description = @ModuleDescription
WHERE ID = @ModuleID
ELSE
INSERT Settings VALUES (@ModuleID, REPLACE(@Version, '.', ''), @Date, @ModuleDescription)
END

DECLARE @InstallDuration NVARCHAR(16)
SELECT @InstallDuration = CONVERT(NVARCHAR, GETUTCDATE() - value, 114) FROM #InstallParams WHERE name = '@InstallStartTime'

DECLARE @ModuleInfo VARCHAR(8000)
SET @ModuleInfo = CONVERT(VARCHAR(8000), (SELECT 1 AS Tag, 0 AS Parent,
@ModuleID [Module!1!id],
@ModuleDescription [Module!1!description],
@Version [Module!1!version],
@Date [Module!1!date],
@MinimumClientVersion [Module!1!minimumClientVersion],
@InstallDuration [Module!1!duration]
FOR XML EXPLICIT))

IF EXISTS (SELECT 1 FROM fn_listextendedproperty(@ModuleProperty, NULL, NULL, NULL, NULL, NULL, NULL))
EXEC sp_updateextendedproperty @ModuleProperty, @ModuleInfo, NULL, NULL, NULL, NULL, NULL, NULL
ELSE
EXEC sp_addextendedproperty @ModuleProperty, @ModuleInfo, NULL, NULL, NULL, NULL, NULL, NULL

GO
SET NOCOUNT OFF
GO -- VERSION $BRANCHNUMBER$.$BUILDLABEL$
-- MODULE ID 20119000
-- MODULE DESCRIPTION VERSION DB Life Cycle Management

-- INSTALLER CHECKED

-- DEPENDS ON 20000000 7.48.00.004 (Reseller Core)
-- DEPENDS ON 20102000 7.48.00.002 (PriceManagement)
-- DEPENDS ON 20103000 7.48.00.000 (DataExchangeManagement)
-- DEPENDS ON 20122000 7.48.00.000 (JobExecuter)
-- DEPENDS ON 20125000 7.48.00.001 (State Management)

-- PARAM @JOB_LCM_AutomaticHandlingJob INT DEFAULT 0
-- PARAM DESCRIPTION @JOB_LCM_AutomaticHandlingJob Installs/Updates the 'LCM Automatic Handling' job. (LCMAutomaticHandlingJob) -- VERSION $BRANCHNUMBER$.$BUILDLABEL$
-- MODULE ID 20119000
-- MODULE DESCRIPTION VERSION DB Life Cycle Management

-- INSTALLER CHECKED

-- DEPENDS ON 20000000 7.48.00.004 (Reseller Core)
-- DEPENDS ON 20102000 7.48.00.002 (PriceManagement)
-- DEPENDS ON 20103000 7.48.00.000 (DataExchangeManagement)
-- DEPENDS ON 20122000 7.48.00.000 (JobExecuter)
-- DEPENDS ON 20125000 7.48.00.001 (State Management)

-- PARAM @JOB_LCM_AutomaticHandlingJob INT DEFAULT 0
-- PARAM DESCRIPTION @JOB_LCM_AutomaticHandlingJob Installs/Updates the 'LCM Automatic Handling' job. (LCMAutomaticHandlingJob) Assembly Installer.exe Installer.exe Customer ILMerge
Full transcript