SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[SubscriptionSharePointConnections]'
GO
CREATE TABLE [dbo].[SubscriptionSharePointConnections]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[IsSharePointOnline] [bit] NOT NULL,
[DisplayName] [nvarchar] (max) NOT NULL,
[LibraryUrl] [nvarchar] (max) NOT NULL,
[IntegratedSecurity] [bit] NOT NULL,
[Username] [nvarchar] (max) NULL,
[Password] [nvarchar] (max) NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_SubscriptionSharePointConnections] on [dbo].[SubscriptionSharePointConnections]'
GO
ALTER TABLE [dbo].[SubscriptionSharePointConnections] ADD CONSTRAINT [PK_SubscriptionSharePointConnections] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[EmailSubscriptions]'
GO
ALTER TABLE [dbo].[EmailSubscriptions] ADD
[SendToEmail] [bit] NOT NULL CONSTRAINT [DF_EmailSubscriptions_SendToEmail] DEFAULT ((0)),
[DateRange] [int] NOT NULL CONSTRAINT [DF_EmailSubscriptions_[DateRangeID] DEFAULT ((6)),
[SendToFileShare] [bit] NOT NULL CONSTRAINT [DF_EmailSubscriptions_SendToFileShare] DEFAULT ((0)),
[FileShareLocation] [nvarchar] (max) NULL,
[SendToSharePoint] [bit] NOT NULL CONSTRAINT [DF_EmailSubscriptions_SendToSharePoint] DEFAULT ((0)),
[SharePointConnectionID] [int] NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[EmailSubscriptions]'
GO
ALTER TABLE [dbo].[EmailSubscriptions] ADD CONSTRAINT [FK_EmailSubscriptions_SubscriptionConnections] FOREIGN KEY ([SharePointConnectionID]) REFERENCES [dbo].[SubscriptionSharePointConnections] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '6.2.2' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

