If (db_name() in('master','model','msdn','tempdb')) begin DECLARE @ErrorMsg varchar(255) SET @ErrorMsg = 'Don''t add these procedures and functions to ' + db_name() PRINT '==========================================================================' PRINT '' PRINT @ErrorMsg PRINT '' PRINT 'You probably want to be in the VEPROMS database' PRINT '' PRINT '==========================================================================' RAISERROR (@ErrorMsg, 20, -1) with log RETURN end print 'Adding procedures and functions to ' + db_name() Go /* ========================================================================================================== Begin: B2022-026 RO Memory reduction coding (Jakes Merge) [Version 2.0] Notes** If you need to rollback to the previous version for some reason and undo the changes for v2.0 then run this sql file ========================================================================================================== */ /* --------------------------------------------------------------------------------------------------- -- Remove any new tables/procs/functions/view specifically for [Version 2.0] --------------------------------------------------------------------------------------------------- */ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstHeader]') AND type in (N'U')) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FK_RofstHeader_ROFsts') AND type in (N'F')) ALTER TABLE [dbo].[RofstHeader] DROP CONSTRAINT [FK_RofstHeader_ROFsts] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstDefaultValue]') AND type in (N'U')) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FK_RofstDefaultValue_RofstHeader') AND type in (N'F')) ALTER TABLE [dbo].[RofstDefaultValue] DROP CONSTRAINT [FK_RofstDefaultValue_RofstHeader] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstDatabase]') AND type in (N'U')) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FK_RofstDatabase_RofstHeader') AND type in (N'F')) ALTER TABLE [dbo].[RofstDatabase] DROP CONSTRAINT [FK_RofstDatabase_RofstHeader] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstChild]') AND type in (N'U')) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'FK_RofstChild_RofstDatabase') AND type in (N'F')) ALTER TABLE [dbo].[RofstChild] DROP CONSTRAINT [FK_RofstChild_RofstDatabase] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstHeader]') AND type in (N'U')) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'DF_RofstHeader_CreateDate') AND type in (N'D')) ALTER TABLE [dbo].[RofstHeader] DROP CONSTRAINT [DF_RofstHeader_CreateDate] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstHeader]') AND type in (N'U')) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'DF_RofstHeader_CreateUserID') AND type in (N'D')) ALTER TABLE [dbo].[RofstHeader] DROP CONSTRAINT [DF_RofstHeader_CreateUserID] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstDatabase]') AND type in (N'U')) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'DF_RofstDatabase_ParentID') AND type in (N'D')) ALTER TABLE [dbo].[RofstDatabase] DROP CONSTRAINT [DF_RofstDatabase_ParentID] GO IF (@@Error = 0) PRINT 'Table/FK Constraints Deleted: Succeeded' ELSE PRINT 'Table/FK Constraints Deleted: Error on Deletion' Go /****** Object: Index [IX_RofstID_Roid] Script Date: 6/6/2022 11:18:32 AM ******/ IF EXISTS (SELECT * FROM sys.indexes Where name = 'IX_RofstID_Roid') DROP INDEX [IX_RofstID_Roid] ON [dbo].[RofstChild] GO IF (@@Error = 0) PRINT 'Index Deleted: [IX_RofstID_Roid] Succeeded' ELSE PRINT 'Index Deleted: [IX_RofstID_Roid] Error on Deletion' Go /****** Object: Index [IX_RofstID_DbiID_ParentID] Script Date: 6/6/2022 11:18:32 AM ******/ IF EXISTS (SELECT * FROM sys.indexes Where name = 'IX_RofstID_DbiID_ParentID') DROP INDEX [IX_RofstID_DbiID_ParentID] ON [dbo].[RofstChild] GO IF (@@Error = 0) PRINT 'Index Deleted: [IX_RofstID_DbiID_ParentID] Succeeded' ELSE PRINT 'Index Deleted: [IX_RofstID_DbiID_ParentID] Error on Deletion' Go /****** Object: Table [dbo].[RofstHeader] Script Date: 6/6/2022 11:18:32 AM ******/ IF Exists(SELECT * FROM sys.objects Where name = 'RofstHeader' AND type in (N'U')) DROP TABLE [dbo].[RofstHeader] GO IF (@@Error = 0) PRINT 'Table Deleted: [RofstHeader] Succeeded' ELSE PRINT 'Table Deleted: [RofstHeader] Error on Deletion' Go /****** Object: Table [dbo].[RofstDefaultValue] Script Date: 6/6/2022 11:18:32 AM ******/ IF Exists(SELECT * FROM sys.objects Where name = 'RofstDefaultValue' AND type in (N'U')) DROP TABLE [dbo].[RofstDefaultValue] GO IF (@@Error = 0) PRINT 'Table Deleted: [RofstDefaultValue] Succeeded' ELSE PRINT 'Table Deleted: [RofstDefaultValue] Error on Deletion' Go /****** Object: Table [dbo].[RofstDatabase] Script Date: 6/6/2022 11:18:32 AM ******/ IF Exists(SELECT * FROM sys.objects Where name = 'RofstDatabase' AND type in (N'U')) DROP TABLE [dbo].[RofstDatabase] GO IF (@@Error = 0) PRINT 'Table Deleted: [RofstDatabase] Succeeded' ELSE PRINT 'Table Deleted: [RofstDatabase] Error on Deletion' Go /****** Object: Table [dbo].[RofstChild] Script Date: 6/6/2022 11:18:32 AM ******/ IF Exists(SELECT * FROM sys.objects Where name = 'RofstChild' AND type in (N'U')) DROP TABLE [dbo].[RofstChild] GO IF (@@Error = 0) PRINT 'Table Deleted: [RofstChild] Succeeded' ELSE PRINT 'Table Deleted: [RofstChild] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstHeaderInsert] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstHeaderInsert' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstHeaderInsert] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstHeaderInsert] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstHeaderInsert] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstHeaderFinalizeLoad] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstHeaderFinalizeLoad' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstHeaderFinalizeLoad] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstHeaderFinalizeLoad] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstHeaderFinalizeLoad] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataSearch] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataSearch' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataSearch] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataSearch] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataSearch] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetValueDifferences] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetValueDifferences' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetValueDifferences] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetValueDifferences] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetValueDifferences] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetRofstByID] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetRofstByID' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetRofstByID] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetRofstByID] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetRofstByID] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetHeaderRofstByID] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetHeaderRofstByID' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetHeaderRofstByID] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetHeaderRofstByID] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetHeaderRofstByID] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetDatabases] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetDatabases' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetDatabases] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetDatabases] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetDatabases] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetDatabaseByID] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetDatabaseByID' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetDatabaseByID] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetDatabaseByID] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetDatabaseByID] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByType] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetChildrenByType' AND type in (N'P')) DROP PROCEDURE[dbo].[vesp_RofstDataGetChildrenByType] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByType] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByType] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByRoid] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetChildrenByRoid' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetChildrenByRoid] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByRoid] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByRoid] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByRofstID] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetChildrenByRofstID' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetChildrenByRofstID] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByRofstID] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByRofstID] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByID] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetChildrenByID' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetChildrenByID] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByID] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetChildrenByID] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByRoid] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetChildByRoid' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetChildByRoid] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetChildByRoid] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetChildByRoid] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByID] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetChildByID' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetChildByID] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetChildByID] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetChildByID] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByAccPageID] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDataGetChildByAccPageID' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDataGetChildByAccPageID] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDataGetChildByAccPageID] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDataGetChildByAccPageID] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstDatabaseInsert] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstDatabaseInsert' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstDatabaseInsert] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstDatabaseInsert] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstDatabaseInsert] Error on Deletion' Go /****** Object: StoredProcedure [dbo].[vesp_RofstChildInsert] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vesp_RofstChildInsert' AND type in (N'P')) DROP PROCEDURE [dbo].[vesp_RofstChildInsert] GO IF (@@Error = 0) PRINT 'Procedure Deleted: [vesp_RofstChildInsert] Succeeded' ELSE PRINT 'Procedure Deleted: [vesp_RofstChildInsert] Error on Deletion' Go /****** Object: View [dbo].[vwRofstData_RofstDefaultValues] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vwRofstData_RofstDefaultValues' AND type in (N'V')) DROP VIEW [dbo].[vwRofstData_RofstDefaultValues] GO IF (@@Error = 0) PRINT 'View Deleted: [vwRofstData_RofstDefaultValues] Succeeded' ELSE PRINT 'View Deleted: [vwRofstData_RofstDefaultValues] Error on Deletion' Go /****** Object: View [dbo].[vwRofstData_RofstDatabases] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vwRofstData_RofstDatabases' AND type in (N'V')) DROP VIEW [dbo].[vwRofstData_RofstDatabases] GO IF (@@Error = 0) PRINT 'View Deleted: [vwRofstData_RofstDatabases] Succeeded' ELSE PRINT 'View Deleted: [vwRofstData_RofstDatabases] Error on Deletion' Go /****** Object: View [dbo].[vwRofstData_RofstChildren] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vwRofstData_RofstChildren' AND type in (N'V')) DROP VIEW [dbo].[vwRofstData_RofstChildren] GO IF (@@Error = 0) PRINT 'View Deleted: [vwRofstData_RofstChildren] Succeeded' ELSE PRINT 'View Deleted: [vwRofstData_RofstChildren] Error on Deletion' Go /****** Object: View [dbo].[vwRofstData_RofstHeaders] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vwRofstData_RofstHeaders' AND type in (N'V')) DROP VIEW [dbo].[vwRofstData_RofstHeaders] GO IF (@@Error = 0) PRINT 'View Deleted: [vwRofstData_RofstHeaders] Succeeded' ELSE PRINT 'View Deleted: [vwRofstData_RofstHeaders] Error on Deletion' Go /****** Object: View [dbo].[vwRofstData_RofstHeaderStatuses] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vwRofstData_RofstHeaderStatuses' AND type in (N'V')) DROP VIEW [dbo].[vwRofstData_RofstHeaderStatuses] GO IF (@@Error = 0) PRINT 'View Deleted: [vwRofstData_RofstHeaderStatuses] Succeeded' ELSE PRINT 'View Deleted: [vwRofstData_RofstHeaderStatuses] Error on Deletion' Go /****** Object: UserDefinedFunction [dbo].[vefn_RofstDataReplaceVars] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vefn_RofstDataReplaceVars' AND type in (N'FN')) DROP FUNCTION [dbo].[vefn_RofstDataReplaceVars] GO IF (@@Error = 0) PRINT 'Function Deleted: [vefn_RofstDataReplaceVars] Succeeded' ELSE PRINT 'Function Deleted: [vefn_RofstDataReplaceVars] Error on Deletion' Go /****** Object: UserDefinedFunction [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vefn_RofstDataReplaceApplTagsWithDefaults' AND type in (N'FN')) DROP FUNCTION [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults] GO IF (@@Error = 0) PRINT 'Function Deleted: [vefn_RofstDataReplaceApplTagsWithDefaults] Succeeded' ELSE PRINT 'Function Deleted: [vefn_RofstDataReplaceApplTagsWithDefaults] Error on Deletion' Go /****** Object: UserDefinedFunction [dbo].[vefn_RofstDataGetLoadStatus] ******/ If Exists(SELECT * FROM sys.objects Where name = 'vefn_RofstDataGetLoadStatus' AND type in (N'FN')) DROP FUNCTION [dbo].[vefn_RofstDataGetLoadStatus] GO IF (@@Error = 0) PRINT 'Function Deleted: [vefn_RofstDataGetLoadStatus] Succeeded' ELSE PRINT 'Function Deleted: [vefn_RofstDataGetLoadStatus] Error on Deletion' Go /* --------------------------------------------------------------------------------------------------- -- Replace any previously existing stored procs before [Version 2.0] back to their previous version --------------------------------------------------------------------------------------------------- */ IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getDocVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getDocVersion]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getDocVersion] ( @VersionID int ) WITH EXECUTE AS OWNER AS SELECT [VersionID], [FolderID], [VersionType], [Name], [Title], [ItemID], [FormatID], [Config], [DTS], [UserID], [LastChanged], (SELECT Count(1) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount] FROM [DocVersions] WHERE [VersionID]=@VersionID SELECT [Associations].[AssociationID], [Associations].[VersionID], [Associations].[ROFstID], [Associations].[Config], [Associations].[DTS], [Associations].[UserID], [Associations].[LastChanged], [ROFsts].[RODbID] [ROFst_RODbID], [ROFsts].[ROLookup] [ROFst_ROLookup], [ROFsts].[Config] [ROFst_Config], [ROFsts].[DTS] [ROFst_DTS], [ROFsts].[UserID] [ROFst_UserID] FROM [Associations] JOIN [ROFsts] ON [ROFsts].[ROFstID]=[Associations].[ROFstID] WHERE [Associations].[VersionID]=@VersionID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getDocVersion] Succeeded' ELSE PRINT 'Procedure Creation: [getDocVersion] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getFiguresByImageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getFiguresByImageID]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getFiguresByImageID] ( @ImageID int ) WITH EXECUTE AS OWNER AS SELECT [Figures].[FigureID], [Figures].[ROFstID], [Figures].[ImageID], [Figures].[Config], [Figures].[DTS], [Figures].[UserID], [Figures].[LastChanged], [ROFsts].[RODbID] [ROFst_RODbID], [ROFsts].[ROLookup] [ROFst_ROLookup], [ROFsts].[Config] [ROFst_Config], [ROFsts].[DTS] [ROFst_DTS], [ROFsts].[UserID] [ROFst_UserID] FROM [Figures] JOIN [ROFsts] ON [ROFsts].[ROFstID]=[Figures].[ROFstID] WHERE [Figures].[ImageID]=@ImageID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getFiguresByImageID] Succeeded' ELSE PRINT 'Procedure Creation: [getFiguresByImageID] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getJustROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getJustROFst]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getJustROFst] ( @ROFstID int ) WITH EXECUTE AS OWNER AS SELECT [ROFstID], [RODbID], [ROLookup], [Config], [DTS], [UserID], [LastChanged], (SELECT Count(1) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount], (SELECT Count(1) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount] FROM [ROFsts] WHERE [ROFstID]=@ROFstID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getJustROFst] Succeeded' ELSE PRINT 'Procedure Creation: [getJustROFst] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getRODb]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getRODb]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getRODb] ( @RODbID int ) WITH EXECUTE AS OWNER AS SELECT [RODbID], [ROName], [FolderPath], [DBConnectionString], [Config], [DTS], [UserID], [LastChanged], (SELECT Count(1) FROM [DROUsages] WHERE [DROUsages].[RODbID]=[RODbs].[RODbID]) [DROUsageCount], (SELECT Count(1) FROM [ROFsts] WHERE [ROFsts].[RODbID]=[RODbs].[RODbID]) [ROFstCount], (SELECT Count(1) FROM [ROImages] WHERE [ROImages].[RODbID]=[RODbs].[RODbID]) [ROImageCount], (SELECT Count(1) FROM [RoUsages] WHERE [RoUsages].[RODbID]=[RODbs].[RODbID]) [RoUsageCount] FROM [RODbs] WHERE [RODbID]=@RODbID SELECT [DROUsages].[DROUsageID], [DROUsages].[DocID], [DROUsages].[ROID], [DROUsages].[Config], [DROUsages].[DTS], [DROUsages].[UserID], [DROUsages].[LastChanged], [DROUsages].[RODbID], [Documents].[LibTitle] [Document_LibTitle], [Documents].[DocContent] [Document_DocContent], [Documents].[DocAscii] [Document_DocAscii], [Documents].[Config] [Document_Config], [Documents].[DTS] [Document_DTS], [Documents].[UserID] [Document_UserID], [Documents].[FileExtension] [Document_FileExtension] FROM [DROUsages] JOIN [Documents] ON [Documents].[DocID]=[DROUsages].[DocID] WHERE [DROUsages].[RODbID]=@RODbID SELECT [ROFsts].[ROFstID], [ROFsts].[RODbID], [ROFsts].[ROLookup], [ROFsts].[Config], [ROFsts].[DTS], [ROFsts].[UserID], [ROFsts].[LastChanged] FROM [ROFsts] WHERE [ROFsts].[RODbID]=@RODbID SELECT [ROImages].[ImageID], [ROImages].[RODbID], [ROImages].[FileName], [ROImages].[Content], [ROImages].[Config], [ROImages].[DTS], [ROImages].[UserID], [ROImages].[LastChanged] FROM [ROImages] WHERE [ROImages].[RODbID]=@RODbID SELECT [RoUsages].[ROUsageID], [RoUsages].[ContentID], [RoUsages].[ROID], [RoUsages].[Config], [RoUsages].[DTS], [RoUsages].[UserID], [RoUsages].[LastChanged], [RoUsages].[RODbID], [Contents].[Number] [Content_Number], [Contents].[Text] [Content_Text], [Contents].[Type] [Content_Type], [Contents].[FormatID] [Content_FormatID], [Contents].[Config] [Content_Config], [Contents].[DTS] [Content_DTS], [Contents].[UserID] [Content_UserID] FROM [RoUsages] JOIN [Contents] ON [Contents].[ContentID]=[RoUsages].[ContentID] WHERE [RoUsages].[RODbID]=@RODbID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getRODb] Succeeded' ELSE PRINT 'Procedure Creation: [getRODb] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getROFst]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getROFst] ( @ROFstID int ) WITH EXECUTE AS OWNER AS SELECT [ROFstID], [RODbID], [ROLookup], [Config], [DTS], [UserID], [LastChanged], (SELECT Count(1) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount], (SELECT Count(1) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount] FROM [ROFsts] WHERE [ROFstID]=@ROFstID SELECT [Associations].[AssociationID], [Associations].[VersionID], [Associations].[ROFstID], [Associations].[Config], [Associations].[DTS], [Associations].[UserID], [Associations].[LastChanged], [DocVersions].[FolderID] [DocVersion_FolderID], [DocVersions].[VersionType] [DocVersion_VersionType], [DocVersions].[Name] [DocVersion_Name], [DocVersions].[Title] [DocVersion_Title], [DocVersions].[ItemID] [DocVersion_ItemID], [DocVersions].[FormatID] [DocVersion_FormatID], [DocVersions].[Config] [DocVersion_Config], [DocVersions].[DTS] [DocVersion_DTS], [DocVersions].[UserID] [DocVersion_UserID] FROM [Associations] JOIN [DocVersions] ON [DocVersions].[VersionID]=[Associations].[VersionID] WHERE [Associations].[ROFstID]=@ROFstID SELECT [Figures].[FigureID], [Figures].[ROFstID], [Figures].[ImageID], [Figures].[Config], [Figures].[DTS], [Figures].[UserID], [Figures].[LastChanged], [ROImages].[RODbID] [ROImage_RODbID], [ROImages].[FileName] [ROImage_FileName], [ROImages].[Content] [ROImage_Content], [ROImages].[Config] [ROImage_Config], [ROImages].[DTS] [ROImage_DTS], [ROImages].[UserID] [ROImage_UserID] FROM [Figures] JOIN [ROImages] ON [ROImages].[ImageID]=[Figures].[ImageID] WHERE [Figures].[ROFstID]=@ROFstID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getROFst] Succeeded' ELSE PRINT 'Procedure Creation: [getROFst] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getROFstByRODbID_DTS]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getROFstByRODbID_DTS]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getROFstByRODbID_DTS] ( @RODbID int, @DTS datetime ) WITH EXECUTE AS OWNER AS SELECT [ROFstID], [RODbID], [ROLookup], [Config], [DTS], [UserID], [LastChanged], (SELECT Count(1) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount], (SELECT Count(1) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount] FROM [ROFsts] WHERE [RODbID]=@RODbID AND [DTS]=@DTS RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getROFstByRODbID_DTS] Succeeded' ELSE PRINT 'Procedure Creation: [getROFstByRODbID_DTS] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getRoFstBySize]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getRoFstBySize]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ create PROCEDURE [dbo].[getRoFstBySize] ( @RODbID int, @Len int ) WITH EXECUTE AS OWNER AS select [ROFstID], [RODbID], [ROLookup], [Config], [DTS], [UserID], [LastChanged], (SELECT Count(1) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount], (SELECT Count(1) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount] from [ROFsts] where len(rolookup) = @len and [RODbID] = @RODbID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getRoFstBySize] Succeeded' ELSE PRINT 'Procedure Creation: [getRoFstBySize] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getROFsts]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getROFsts]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getROFsts] WITH EXECUTE AS OWNER AS SELECT [ROFstID], [RODbID], [ROLookup], [Config], [DTS], [UserID], [LastChanged], (SELECT Count(1) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount], (SELECT Count(1) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount] FROM [ROFsts] RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getROFsts] Succeeded' ELSE PRINT 'Procedure Creation: [getROFsts] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getROFstsByRODbID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getROFstsByRODbID]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getROFstsByRODbID] ( @RODbID int ) WITH EXECUTE AS OWNER AS SELECT [ROFsts].[ROFstID], [ROFsts].[RODbID], [ROFsts].[ROLookup], [ROFsts].[Config], [ROFsts].[DTS], [ROFsts].[UserID], [ROFsts].[LastChanged], (SELECT COUNT(1) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount], (SELECT COUNT(1) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount] FROM [ROFsts] WHERE [ROFsts].[RODbID]=@RODbID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getROFstsByRODbID] Succeeded' ELSE PRINT 'Procedure Creation: [getROFstsByRODbID] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[getROImage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[getROImage]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[getROImage] ( @ImageID int ) WITH EXECUTE AS OWNER AS SELECT [ImageID], [RODbID], [FileName], [Content], [Config], [DTS], [UserID], [LastChanged], (SELECT COUNT(1) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount] FROM [ROImages] WHERE [ImageID]=@ImageID SELECT [Figures].[FigureID], [Figures].[ROFstID], [Figures].[ImageID], [Figures].[Config], [Figures].[DTS], [Figures].[UserID], [Figures].[LastChanged], [ROFsts].[RODbID] [ROFst_RODbID], [ROFsts].[ROLookup] [ROFst_ROLookup], [ROFsts].[Config] [ROFst_Config], [ROFsts].[DTS] [ROFst_DTS], [ROFsts].[UserID] [ROFst_UserID] FROM [Figures] JOIN [ROFsts] ON [ROFsts].[ROFstID]=[Figures].[ROFstID] WHERE [Figures].[ImageID]=@ImageID RETURN GO IF (@@Error = 0) PRINT 'Procedure Creation: [getROImage] Succeeded' ELSE PRINT 'Procedure Creation: [getROImage] Error on Creation' GO IF EXISTS (Select * From dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[updateROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP Procedure [dbo].[updateROFst]; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2022 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[updateROFst] ( @ROFstID int, @RODbID int, @ROLookup varbinary(MAX), @Config nvarchar(MAX)=null, @DTS datetime, @UserID nvarchar(100), @LastChanged timestamp, @newLastChanged timestamp output ) WITH EXECUTE AS OWNER AS BEGIN TRY -- Try Block BEGIN TRANSACTION UPDATE [ROFsts] SET [RODbID]=@RODbID, [ROLookup]=@ROLookup, [Config]=@Config, [DTS]=@DTS, [UserID]=@UserID WHERE [ROFstID]=@ROFstID AND [LastChanged]=@LastChanged IF @@ROWCOUNT = 0 BEGIN IF NOT exists(select * from [ROFsts] WHERE [ROFstID]=@ROFstID) RAISERROR('ROFst record has been deleted by another user', 16, 1) ELSE RAISERROR('ROFst has been edited by another user', 16, 1) END SELECT @newLastChanged=[LastChanged] FROM [ROFsts] WHERE [ROFstID]=@ROFstID IF( @@TRANCOUNT > 0 ) COMMIT END TRY BEGIN CATCH -- Catch Block IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback EXEC vlnErrorHandler END CATCH GO IF (@@Error = 0) PRINT 'Procedure Creation: [updateROFst] Succeeded' ELSE PRINT 'Procedure Creation: [updateROFst] Error on Creation' GO /* ========================================================================================================== End: B2022-026 RO Memory reduction coding (Jakes Merge) [Version 2.0] ========================================================================================================== */ ----------------------------------------------------------------------------- /* --------------------------------------------------------------------------- | ADD New Code Before this Block | | Change Date and Description | --------------------------------------------------------------------------- */ ----------------------------------------------------------------------------- -- Rebuild / Reorganize All Indexes exec [dbo].[vesp_UtilityCheckIndexes]; IF (@@Error = 0) PRINT 'Running vesp_UtilityCheckIndexes Succeeded' ELSE PRINT 'Running vesp_UtilityCheckIndexes Failed to Execute' GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vesp_GetSQLCodeRevision]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1) DROP PROCEDURE [vesp_GetSQLCodeRevision]; GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2021 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ CREATE PROCEDURE [dbo].[vesp_GetSQLCodeRevision] WITH EXECUTE AS OWNER AS BEGIN TRY -- Try Block BEGIN TRANSACTION -- Change information in the next line when you are done set nocount on DECLARE @RevDate varchar(255) DECLARE @RevDescription varchar(255) set @RevDate = '06/02/2022 12:30 PM' set @RevDescription = 'getGridIds fix for table searches' Select cast(@RevDate as datetime) RevDate, @RevDescription RevDescription PRINT 'SQL Code Revision ' + @RevDate + ' - ' + @RevDescription IF( @@TRANCOUNT > 0 ) COMMIT END TRY BEGIN CATCH -- Catch Block IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback EXEC vlnErrorHandler END CATCH GO -- Display the status of Proc creation IF (@@Error = 0) PRINT 'StoredProcedure [vesp_GetSQLCodeRevision] Succeeded' ELSE PRINT 'StoredProcedure [vesp_GetSQLCodeRevision] Error on Creation' go Exec vesp_GetSQLCodeRevision;