MSSQL report on users and groups

Aruna_Tata_(acteluser1_-_(deleted))

A while ago, I found a livereports discussion in the knowledge center with an Oracle DB report showing how to trace what groups/projects a user belongs to in Livelink. Since this was a hierarchical query, I converted it to MSSQL using a UDF. My report does not return all groups and projects for the user. I am not able to figure out what I am doing wrong. Can anyone take a look at the attached reports and help?The link to the original report (Oracle) is below:https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=2627549&objAction=view&show=0My Livereport is as attached. My UDF is as below:CREATE function dbo.ufn_getChildUsers(@userID int)returns @t table(userID int, lvl int)asbegindeclare @lvl intset @lvl=1insert @t values(@rowcount > 0begin insert @t select distinct a.ID, @lvl+1 from livelink900.KUAFChildren a inner join @t b on a.ChildID = b.UserID and b.lvl = @lvl set @lvl = @lvl + 1endreturnend

volvostephen

Message from via eLinkThere used to be a KUAFRightsList. This was dropped with Livelink 9.1 SP3 Ithink. In any case, I found the table useful because it listed in a nonhierarchical manner who was in what group. It had all userID's mapped toevery group they were in nested or not. When it was dropped, I setup a SQLServer Job to re-populate it nightly. Here is the SQL that runs.... SorryI don't have time to look at your UDF but I thought this might be usefulanyways. Note: I create a new table and then in a single transactionrename the new table and drop the old one - this way a user always sees apopulated table.Here it is...if exists (select * from dbo.sysobjects where id =object_id(N'[livelink].[KUAFRightsList_NEW]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [livelink].[KUAFRightsList_NEW]GOCREATE TABLE [livelink].[KUAFRightsList_NEW] ( [RLID] [int] NOT NULL , [RLRightID] [int] NOT NULL , [RLProxyType] [int] NULL ) ON [PRIMARY]CREATE TABLE #tmp ( [RLID] [int] NOT NULL , [RLRightID] [int] NOT NULL , [RLProxyType] [int] NULL ) ON [PRIMARY]GO CREATE INDEX [KUAFRightsList_RLID] ON[livelink].[KUAFRightsList_NEW]([RLID]) ON [PRIMARY]GO CREATE INDEX [KUAFRightsList_RLProxyType] ON[livelink].[KUAFRightsList_NEW]([RLProxyType]) ON [PRIMARY]GO CREATE INDEX [KUAFRightsList_RLRightID] ON[livelink].[KUAFRightsList_NEW]([RLRightID]) ON [PRIMARY]GOdeclare @ID Intdeclare @c_curs cursorset @c_curs = cursor local FORWARD_ONLY STATIC for select ID from livelink.kuaf where type = 0 and deleted = 0open @c_curs fetch next from @c_curs into @fetch_status = 0) begin Insert into #tmp select @ID [RLID],A.ID [RLRightID], 0[RLProxyType] from livelink.kuafchildren A with (nolock) inner join livelink.kuaf B with (nolock) on A.ID =B.ID where A.ChildID = @Rowcount > 0 Begin insert into #tmp Select @ID [RLID],A.ID[RLRightID], 0 [RLProxyType] from livelink.KuafChildren A with (nolock) inner join livelink.kuaf B with (nolock) onA.ID = B.ID where B.Type = 1 and B.Deleted = 0 and A.ChildID in (select RLRightID from #tmpwhere RLID = @ID) and A.ID not in (select RLRightID from #tmpwhere RLID = @ID) end fetch next from @c_curs into @ID endclose @c_cursdeallocate @c_cursInsert into [livelink].[KUAFRightsList_NEW] select distinct RLID, RLRightID,RLProxyType from #tmpdrop table #tmpif exists (select * from dbo.sysobjects where id =object_id(N'[livelink].[KUAFRightsList_OLD]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [livelink].[KUAFRightsList_OLD]GOBEGIN TRANSACTION EXEC sp_rename 'livelink.KUAFRightsList', 'KUAFRightsList_OLD','object' EXEC sp_rename 'livelink.KUAFRightsList_NEW', 'KUAFRightsList','object'COMMIT-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: March 3, 2005 8:32 PMTo: eLink RecipientSubject: MSSQL report on users and groupsMSSQL report on users and groupsPosted by Tata, Aruna on 03/03/2005 08:22 PMA while ago, I found a livereports discussion in the knowledge center withan Oracle DB report showing how to trace what groups/projects a user belongsto in Livelink. Since this was a hierarchical query, I converted it to MSSQLusing a UDF. My report does not return all groups and projects for the user. I am notable to figure out what I am doing wrong. Can anyone take a look at theattached reports and help?The link to the original report (Oracle) is below:https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=2627549&objAction=view&show=0My Livereport is as attached. My UDF is as below:CREATE function dbo.ufn_getChildUsers(@userID int)returns @t table(userID int, lvl int)asbegindeclare @lvl intset @lvl=1insert @t values(@rowcount > 0begin insert @t select distinct a.ID, @lvl+1 from livelink900.KUAFChildrena inner join @t b on a.ChildID = b.UserID and b.lvl = @lvl set @lvl = @lvl + 1endreturnend[To reply to this thread, use your normal E-mail reply function.]============================================================Attachment link: UsersandGroups.rpthttps://knowledge.opentext.com/knowledge/livelink.exe/3870056/UsersandGroups.rpt?func=doc.Fetch&nodeid=3870056============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe