Issue:
Company Users cannot log into the application due to duplicate values in the Preferences table.
We have seen cases where a duplicate preference value is created for a SedonaCloud Company. When this happens, users will see errors trying to login to the SedonaCloud User Interface.
When a user tries to log into the company, they will usually see the message that an error occurred while logging in.
Reviewing the Logs, the message An Item with the same key has already been added is seen on the failed login attempt.
Resolution:
To correct this issue, you will need access to the users SQL server.
From a machine that has SQL Server Management Studio installed, type SSMS in the Windows Search.
Log into the correct server using the SedonaUser SQL account.
Click the New Query button in SSMS.
Once the Query window opens, select SedonaCloud database.
You can use the script below to find the ID for the Company.
--- Find the ID for the correct company ---
Use SedonaCloud
select Id, CompanyName from HostCompany
This will return a list of the companies available.
SQL Script to find and delete the duplicate preference:
This will return a list of preferences in the table. Copy and paste the script below into the Query window in SSMS.
NOTE: You will need to Change the script to point to the correct company ID from the previous script. You will need to set the value for BaseCompanyId in the Where clause to match the companies ID that is having the problem.
--- Check for duplicate preference keys ---
Use SedonaCloud
select PreferenceKey, PreferenceValue, PreferenceId
from Preference
where BaseCompanyId = 1 and PreferenceLevel = 0 and PreferenceKey is not null and
PreferenceKey not in ('CompanyLogo', 'HostLogo') and
PreferenceExtension not in ('base64/image', 'text/large')
order by PreferenceKey
This will return a list of preferences in the table. Review the list and make note of the highest PreferenceID for any duplicate entries.
The PreferenceID will be used in the next step to delete the duplicates.
Backup the table before making any change.
Once you have found the duplicate preference values, you will need to create a backup of the table before making changes.
--- Create backup of data before making changes ---
select * into Preferences_Backup_Duplicates from Preference
Delete duplicate records from the table.
Use the PreferenceID from the duplicate entries in the Where clause to remove the extra records.
If there are more than one duplicate that needs removed separate each of the PreferenceIDs with a comma.
Example: Preferenceid in (526, 534,548)
--- Delete the duplicate records ---
--- There should be one of each preference per company ---
--- If there are duplicates delete extra entries so only one of each is left ---
Delete from Preference where Preferenceid in (526)
You should see that the script executed successfully with the correct number of rows removed.
You should now be able to login as a Company User without any issues.
