Database is in Single_User Mode and How to Change to Multi_User Mode

For unknown reason (due to operation failure), a database was stuck in Single_User mode.

Here are a few steps to kill the troubled session and change the database back to multi_user mode.

–1.Find the session_id to kill
Select request_session_id From sys.dm_tran_locks Where resource_database_id=DB_ID(‘DBName_In_Single_User_Mode’);

–2: Kill the found session_id

Kill theseessionid ;

–3. set the database to multi_user mode
USE [master]

ALTER DATABASE [DBName_In_Single_User_Mode] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Find All Related (directly or indirectly) IDs

  

DECLARE @Value INT
SET @Value = 6

CREATE TABLE People (ID INT PRIMARY KEY , name NVARCHAR(50), motherID INT, fatherID INT, sex NVARCHAR(50));

INSERT People VALUES(1, 'A', NULL, NULL, 'male');
INSERT People VALUES(2, 'B', NULL, NULL, 'female');
INSERT People VALUES(3, 'C', 1, 2, 'male');
INSERT People VALUES(4, 'X', NULL, NULL, 'male');
INSERT People VALUES(5, 'Y', NULL, NULL, 'female');
INSERT People VALUES(6, 'Z', 5, 4, 'female');
INSERT People VALUES(7, 'T', NULL, NULL, 'female');

CREATE TABLE marriages (
HusbandID INT REFERENCES People(id),
WifeID INT REFERENCES People(id)
)
INSERT marriages VALUES (1,2);
INSERT marriages VALUES (4,5);
INSERT marriages VALUES (1,5);
INSERT marriages VALUES (3,6);

--create source relation dataset
;with dataSource as (
select ID id, null relatedid from People
union
select ID, motherID relatedid from People
union
select ID, fatherID relatedid from People
union
Select HusbandID, WifeId relatedid  from marriages

)

, LeftIDs AS
(
  SELECT id, relatedid  FROM dataSource
  WHERE relatedid = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN LeftIDs b ON a.id = b.relatedid
)
, RightIDs AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN LeftIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
, RightIDs2 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)

)
 , RightIDs3 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id  = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs2 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
 , RightIDs4 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id  = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs3 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)

,finalcte as
(
  SELECT id  FROM LeftIDs
  UNION
  SELECT relatedid  FROM LeftIDs
  UNION
  SELECT id  FROM RightIDs
  UNION
  SELECT relatedid
  FROM RightIDs
  UNION
  SELECT id FROM RightIDs2
  UNION
  SELECT relatedid  FROM RightIDs2
  UNION
  SELECT id  FROM RightIDs3
  UNION
  SELECT relatedid  FROM RightIDs3
  UNION
  SELECT id  FROM RightIDs4
  UNION
  SELECT relatedid FROM RightIDs4

)

select id from finalcte
WHERE id is not null

---brutal force solution
--declare @id int=1
 
 
;with mycte as (
select ID, null relatedID from People
union
select ID, motherID relatedID from People
union
select ID, fatherID relatedID from People
union
Select HusbandID, WifeId relatedID  from marriages
)
 
 
,mycte2 as (
SELECT id  FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
union
SELECT   relatedID FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
)
 
 
select id from mycte 
where  id in (select id from mycte2 ) or relatedID in (select id from mycte2)
union
select relatedID from mycte 
where ( id in (select id from mycte2 )or relatedID in (select id from mycte2))
and relatedID is not null
 
 
 
drop table marriages, People




 

https://stackoverflow.com/questions/18601791/need-query-to-select-direct-and-indirect-customerid-IDses