Saved queries and favorites are missing after a Spider update

[#1]

Summary

After a migration from Spider LCM 5.x to 6.1.[0/1] the saved queries are missing in the Advanced Search form.

Symptoms

After a migration from Spider LCM 5.x to 6.1.[0/1] the saved queries are missing in the Advanced Search form

After a migration from Spider 6 to Spider 6.1 the saved queries and favorites are missing.

Cause

The MandatorID is mandatory for more tables.
After a migration the MandatorID is not set in the [SpiderXXX].sosys.SavedQuery and SpiderCore.sosys.[sosys].SearchFavorites table.

Resolution

Open the sosys.SavedQuery tables database and fill the MandatorID field with the appropriate mandator ID. (You can find the mandator ID in the table dbo.Mandator.

In a single mandator system (Mandator: Standard) you can use the following command to overcome this problem.

update [SpiderXXX].sosys.SavedQuery set MandatorID=(Select ID from SpiderCore.dbo.Mandator where [Name]='Standard');

update SpiderCore.sosys.SearchFavorites set MandatorID=(Select ID from SpiderCore.dbo.Mandator where [Name]='Standard');

delete from  [Spider_Core].[sosys].[UserSettings]
where id in
(
       select id from  [Spider_Core].[sosys].[UserSettings] us

       inner join
       (

             select [UserID] ,
       
                    [ApplicationGUID] ,
                    [Namespace] ,
                    [Identifier]
                    ,count(*) as c
       
                    from [Spider_Core].[sosys].[UserSettings]

                    group by [UserID] ,
                    [ApplicationGUID] ,
                    [Namespace] ,
                    [Identifier]

                    having count(*) = 2 
       ) t2 on us.UserID=t2.UserID and us.ApplicationGUID=t2.ApplicationGUID and us.[Namespace]=t2.[Namespace] and us.Identifier=t2.Identifier

       where us.MandatorID='-1'
)

update [Spider_Core].[sosys].[UserSettings]
set MandatorID=0 where MandatorID <> 0

Additional Information

Caution: Even if you have a single mandator system the MandatorID has to be filled!

Products

LCM6, Spider 6.1