48 lines
1.2 KiB
Transact-SQL
48 lines
1.2 KiB
Transact-SQL
USE [dba]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[drop_db_product_superset] Script Date: 15.04.2025 10:21:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
/*
|
|
drop_db_product_superset
|
|
|
|
This procedure is called after the call to sl2007.dbo.usp_Daily_Batch_Update
|
|
It will drop the local database "product_superset", which is only needed for the update of sl2007.
|
|
|
|
--Changelog (dd.mm.yyyy)
|
|
12.02.2025 TSC Creation
|
|
*/
|
|
CREATE OR ALTER PROCEDURE [dbo].[drop_db_product_superset]
|
|
AS
|
|
BEGIN
|
|
DECLARE @q NVARCHAR(MAX)='';
|
|
DECLARE @tplDrop NVARCHAR(MAX)='
|
|
IF EXISTS(SELECT 1 FROM sys.databases WHERE name=''Product_Superset'')
|
|
DROP DATABASE [Product_Superset];
|
|
';
|
|
--terminate connections to product_superset
|
|
SELECT @q = @q + 'KILL '+CONVERT(varchar(5), c.session_id) + ';'
|
|
FROM sys.dm_exec_connections AS c
|
|
JOIN sys.dm_exec_sessions AS s
|
|
ON c.session_id = s.session_id
|
|
WHERE DB_NAME([s].[database_id]) = 'product_superset'
|
|
AND c.session_id <> @@SPID
|
|
ORDER BY c.connect_time ASC;
|
|
|
|
EXEC sp_executesql @q,N'';
|
|
PRINT 'connections to product_superset killed';
|
|
|
|
--drop product_superset database
|
|
SELECT @q = @tplDrop;
|
|
EXEC sp_executesql @q,N'';
|
|
PRINT 'product_superset dropped';
|
|
END
|
|
GO
|
|
|
|
|