IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[k_dashboard_downtime]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[k_dashboard_downtime]( [id] [int] NOT NULL, [startdate] [datetime] NOT NULL, [enddate] [datetime] NOT NULL, CONSTRAINT [PK_k_dashboard_downtime] PRIMARY KEY CLUSTERED ( [id] ASC, [startdate] ASC, [enddate] ASC ) ON [PRIMARY] ) ON [PRIMARY] END IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'authcrypto' AND OBJECT_NAME(object_id) = 'k_instances') BEGIN ALTER TABLE dbo.k_instances ADD authcrypto NVARCHAR(1024) END ALTER TABLE dbo.k_dashboard_historique ALTER COLUMN libelle VARCHAR (250); IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'transactionPer' AND OBJECT_NAME(object_id) = 'k_sauvegarde_Instance_Dashboard') BEGIN ALTER TABLE dbo.k_sauvegarde_Instance_Dashboard ADD transactionPer xml NULL , lazyWriterPer xml NULL , diskReadIoPer xml NULL , diskWriteIoPer xml NULL END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteGroup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DeleteGroup] AS' END ALTER PROCEDURE [dbo].[DeleteGroup] @ID INT AS BEGIN SET NOCOUNT ON; UPDATE dbo.k_serveur SET k_groupe_id = NULL WHERE k_groupe_id = @ID DELETE dbo.k_groupes WHERE id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteInstance]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DeleteInstance] AS' END ALTER PROCEDURE [dbo].[DeleteInstance] @ID INT AS BEGIN SET NOCOUNT ON; DELETE dbo.k_instances WHERE id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteServer]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DeleteServer] AS' END ALTER PROCEDURE [dbo].[DeleteServer] @ID int AS BEGIN SET NOCOUNT ON; DELETE dbo.k_serveur WHERE id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetGroupById]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetGroupById] AS' END ALTER PROCEDURE [dbo].[GetGroupById] @ID INT AS BEGIN SET NOCOUNT ON; SELECT g.name ,g.couleur FROM k_groupes g (NOLOCK) WHERE g.id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetGroupIdList]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetGroupIdList] AS' END ALTER PROCEDURE [dbo].[GetGroupIdList] AS BEGIN SET NOCOUNT ON; SELECT id FROM k_groupes (NOLOCK) ORDER BY name END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetGroupList]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetGroupList] AS' END ALTER PROCEDURE [dbo].[GetGroupList] AS BEGIN SET NOCOUNT ON; SELECT id , name , 1 AS checked FROM k_groupes (NOLOCK) ORDER BY name END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetIndexUsageHistory]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetIndexUsageHistory] AS' END ALTER PROCEDURE [dbo].[GetIndexUsageHistory] @SERVER NVARCHAR(128) , @INSTANCE NVARCHAR(128) , @DATABASE NVARCHAR(128) , @TABLE NVARCHAR(128) AS BEGIN SET NOCOUNT ON; SELECT serveur_name , instance_name , database_name , table_name , last_stat , index_name , user_updates , system_updates , user_seeks , user_scans , user_lookups , total_user_access FROM k_index_history (NOLOCK) WHERE serveur_name = @SERVER AND instance_name = @INSTANCE AND database_name = @DATABASE AND table_name = @TABLE END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetIndexUsageLastStat]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetIndexUsageLastStat] AS' END ALTER PROCEDURE [dbo].[GetIndexUsageLastStat] @SERVER NVARCHAR(128) , @INSTANCE NVARCHAR(128) , @DATABASE NVARCHAR(128) , @TABLE NVARCHAR(128) AS BEGIN SET NOCOUNT ON; SELECT MAX(last_stat) AS last_stat FROM k_index_history (NOLOCK) WHERE serveur_name = @SERVER AND instance_name = @INSTANCE AND database_name = @DATABASE AND table_name = @TABLE END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetInstanceById]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetInstanceById] AS' END ALTER PROCEDURE [dbo].[GetInstanceById] @ID INT AS BEGIN SET NOCOUNT ON; SELECT i.[name] , i.[description] , i.[ipport] , i.[authmode] , i.[authuser] , i.[authpsw] , i.k_serveur_id , s.ipname , i.authcrypto FROM k_instances i (NOLOCK) INNER JOIN k_serveur s (NOLOCK) ON i.k_serveur_id = s.id WHERE i.id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetInstanceIdListByServer]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetInstanceIdListByServer] AS' END ALTER PROCEDURE [dbo].[GetInstanceIdListByServer] @SERVERID INT AS BEGIN SET NOCOUNT ON; SELECT id FROM k_instances(NOLOCK) WHERE k_serveur_id = @SERVERID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServerById]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetServerById] AS' END ALTER PROCEDURE [dbo].[GetServerById] @ID INT AS BEGIN SET NOCOUNT ON; SELECT id , name , description , ipname , COALESCE(k_groupe_id, 0) AS k_groupe_id FROM k_serveur(NOLOCK) WHERE id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServerIdByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetServerIdByName] AS' END ALTER PROCEDURE [dbo].[GetServerIdByName] @NAME VARCHAR(50) AS BEGIN SET NOCOUNT ON; SELECT id FROM k_serveur(nolock) WHERE name = @NAME END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServerIdList]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetServerIdList] AS' END ALTER PROCEDURE [dbo].[GetServerIdList] AS BEGIN SET NOCOUNT ON; SELECT id FROM k_serveur (NOLOCK) ORDER BY name END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServerIdListByGroup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetServerIdListByGroup] AS' END ALTER PROCEDURE [dbo].[GetServerIdListByGroup] @GROUPID INT AS BEGIN SET NOCOUNT ON; SELECT id FROM k_serveur (NOLOCK) WHERE COALESCE(k_groupe_id, 0) = @GROUPID ORDER BY name END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServerIdListOrderByGroup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetServerIdListOrderByGroup] AS' END ALTER PROCEDURE [dbo].[GetServerIdListOrderByGroup] AS BEGIN SET NOCOUNT ON; SELECT s.id FROM k_serveur s (NOLOCK) LEFT JOIN k_groupes g (NOLOCK) ON s.k_groupe_id = g.id ORDER BY COALESCE(g.name, 'Default Group') , s.name END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServerIDListOrderByGroup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetServerIDListOrderByGroup] AS' END ALTER PROCEDURE [dbo].[GetServerIDListOrderByGroup] AS BEGIN SET NOCOUNT ON; SELECT s.id FROM k_serveur s (NOLOCK) LEFT JOIN k_groupes g (NOLOCK) ON s.k_groupe_id = g.id ORDER BY COALESCE(g.name, 'Default Group') , s.name END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetServerList]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetServerList] AS' END ALTER PROCEDURE [dbo].[GetServerList] AS BEGIN SET NOCOUNT ON; SELECT id , name FROM k_serveur (NOLOCK) ORDER BY name END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertGroup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertGroup] AS' END ALTER PROCEDURE [dbo].[InsertGroup] @NAME VARCHAR(100) , @COLOR VARCHAR(50) AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.k_groupes (name, couleur) VALUES(@NAME, @COLOR) END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertInstance]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertInstance] AS' END ALTER PROCEDURE [dbo].[InsertInstance] @NAME VARCHAR(50) , @DESCRIPTION VARCHAR(500) , @IPPORT VARCHAR(50) , @AUTHMODE INT , @AUTHUSER VARCHAR(128) , @AUTHPSW VARCHAR(128) , @SERVER INT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.k_instances (name, description, ipport, authmode, authuser, authcrypto, k_serveur_id) VALUES (@NAME, @DESCRIPTION, @IPPORT, @AUTHMODE, @AUTHUSER, @AUTHPSW, @SERVER) END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertServer]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertServer] AS' END ALTER PROCEDURE [dbo].[InsertServer] @NAME VARCHAR(50) , @DESCRIPTION VARCHAR(128) , @IPNAME VARCHAR(50) , @GROUP INT AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[k_serveur] (name, description, ipname, k_groupe_id) VALUES (@NAME, @DESCRIPTION, @IPNAME, @GROUP) END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateGroup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UpdateGroup] AS' END ALTER PROCEDURE [dbo].[UpdateGroup] @ID INT , @NAME VARCHAR(100) , @COLOR VARCHAR(50) AS BEGIN SET NOCOUNT ON; UPDATE dbo.k_groupes SET name = @NAME , couleur = @COLOR WHERE id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateInstance]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UpdateInstance] AS' END ALTER PROCEDURE [dbo].[UpdateInstance] @ID INT , @NAME VARCHAR(50) , @DESCRIPTION VARCHAR(500) , @IPPORT VARCHAR(50) , @AUTHMODE INT , @AUTHUSER VARCHAR(128) , @AUTHPSW VARCHAR(128) , @SERVER INT AS BEGIN SET NOCOUNT ON; UPDATE dbo.k_instances SET name = @NAME , description = @DESCRIPTION , ipport = @IPPORT , authmode = @AUTHMODE , authuser = @AUTHUSER , authcrypto = @AUTHPSW , k_serveur_id = @SERVER WHERE id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateServer]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UpdateServer] AS' END ALTER PROCEDURE [dbo].[UpdateServer] @ID int , @NAME VARCHAR(50) , @DESCRIPTION VARCHAR(128) , @IPNAME VARCHAR(50) , @GROUP INT AS BEGIN SET NOCOUNT ON; UPDATE dbo.k_serveur SET name = @NAME , description = @DESCRIPTION , ipname = @IPNAME , k_groupe_id = @GROUP WHERE id = @ID END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteHistory]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.DeleteHistory AS' END"); db.ExecuteWithResults(@"ALTER PROCEDURE DeleteHistory @TYPE CHAR(1) , @DAYS INT , @BATCHSIZE INT = 5000 AS BEGIN SET NOCOUNT ON; DECLARE @ROWCOUNT INT = @BATCHSIZE IF @DAYS > 0 BEGIN SET @DAYS = @DAYS * -1 END WHILE @ROWCOUNT > 0 BEGIN DELETE TOP (@BATCHSIZE) FROM dbo.k_dashboard_historique WHERE horodatage < DATEADD(DAY, @DAYS, GETDATE()) AND typesauvegarde = @TYPE SET @ROWCOUNT = @@ROWCOUNT END END ALTER PROCEDURE [dbo].[DeleteInstance] @ID INT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION deleteInstance DELETE dbo.k_dashboard_discard WHERE instanceid = @ID DELETE dbo.k_dashboard_downtime WHERE id = @ID DELETE dbo.k_dashboard_historique WHERE instanceid = @ID DELETE dbo.k_dashboard_instance WHERE instance = @ID DELETE dbo.k_dashboard_read WHERE instanceid = @ID DELETE dbo.k_instance_SSRS WHERE instanceid = @ID DELETE dbo.k_instances WHERE id = @ID COMMIT TRANSACTION deleteInstance END TRY BEGIN CATCH ROLLBACK TRANSACTION deleteInstance DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END ALTER PROCEDURE [dbo].[InsertInstance] @NAME VARCHAR(50) , @DESCRIPTION VARCHAR(500) , @IPPORT VARCHAR(50) , @AUTHMODE INT , @AUTHUSER VARCHAR(128) , @AUTHPSW VARCHAR(1024) , @SERVER INT AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.k_instances (name, description, ipport, authmode, authuser, authcrypto, k_serveur_id) VALUES (@NAME, @DESCRIPTION, @IPPORT, @AUTHMODE, @AUTHUSER, @AUTHPSW, @SERVER) END delete version; insert into version(version) values ('1.4.10.0')