Symptoms
During Database Upgrade (or when running
SP_UPGRADE_PROJECT_DATABASE_V2) you receive:
Error occurred while upgrading the project database. The INSERT statement conflicted with the FOREIGN KEY constraint "fk_tbl_pj_GroupRightAssociation_GroupId_tbl_pj_GroupInfo". The conflict occurred in database "prj_XXXXXX", table "dbo.tbl_pj_GroupInfo", column 'GroupId'.
Followed by hundreds of harmless warnings:
Warning: Null value is eliminated by an aggregate or other SET operation.
Cause
Very old Venio installations (pre-2018) often contain duplicate/orphaned security groups that were created automatically over the years. These groups:
- Have no users assigned
- Still exist in rights/association tables
- Violate the stricter foreign-key constraints introduced in later versions
Resolution
Always take a full database backup before running any script.
- Run the official cleanup script below on the affected project database (e.g., prj_WILE0001_RR_Acme).
- After the script completes successfully, re-run the project upgrade:
exec SP_UPGRADE_PROJECT_DATABASE_V2 <ProjectID>
Official Cleanup Script (Issue in groupid when upgrading.sql)
IF OBJECT_ID('tbl_pj_GroupInfo_bak_101800') IS NULL
SELECT * INTO tbl_pj_GroupInfo_bak_101800 FROM tbl_pj_GroupInfo
DECLARE @GroupIdTable TABLE (GroupId BIGINT)
INSERT INTO @GroupIdTable
SELECT GroupId
FROM (
SELECT ROW_NUMBER() OVER(
PARTITION BY SUBSTRING(G.GROUPNAME, LEN(G.GROUPNAME)-CHARINDEX('-',REVERSE(G.GroupName))+1, LEN(G.GROUPNAME))
ORDER BY U.groupid desc, G.GROUPID DESC) AS RN,
G.GroupId ,
u.GroupId as UserGroupId
FROM tbl_pj_GroupInfo G
LEFT JOIN (SELECT DISTINCT GROUPID FROM tbl_pj_GroupUserAssociation) U
ON G.GROUPID = U.GroupId
) S
WHERE RN 1 AND UserGroupId IS NULL
BEGIN TRAN
DELETE g FROM [dbo].[tbl_fld_GroupFieldComponentAssociation] g INNER JOIN @GroupIdTable t ON g.UserGroupId = t.groupid
DELETE g FROM [dbo].[tbl_fld_GroupFieldAssociation] g INNER JOIN @GroupIdTable t ON g.UserGroupId = t.groupid
DELETE g FROM [dbo].[tbl_tg_TagProjectGroupAssociation] g INNER JOIN @GroupIdTable t ON g.GroupId = t.groupid
DELETE g FROM [dbo].[tbl_pj_GroupRightAssociation] g INNER JOIN @GroupIdTable t ON g.GroupId = t.groupid
DELETE g FROM tbl_pj_GroupInfo g INNER JOIN @GroupIdTable t ON g.GroupId = t.groupid
DECLARE @ProjectName nvarchar(MAX)
SELECT @ProjectName = ProjectName
FROM tbl_pj_ProjectSetup WITH(NOLOCK)
WHERE DatabaseInstanceName = DB_NAME()
UPDATE tbl_pj_GroupInfo
SET GroupName = @ProjectName + SUBSTRING(GROUPNAME,
LEN(GROUPNAME)-CHARINDEX('-',REVERSE(GroupName))+1, LEN(GROUPNAME))
COMMIT
If the script and SP_UPGRADE_PROJECT_DATABASE_V2 both complete without error
→ the project is fully upgraded.
Is the script safe?
Yes. It has been used by Venio Support on hundreds of legacy upgrades. It only removes duplicate groups that:
- Have no users assigned
- Are not the most recent version of that logical group
A backup of tbl_pj_GroupInfo is automatically created as
tbl_pj_GroupInfo_bak_101800 the first time the script runs.
Prevention / Best Practice
- Keep this script in your Venio toolkit. You will need it for every remaining legacy project on old servers.
- After upgrading to 11.8+, this issue no longer occurs because the group creation logic was fixed years ago.
References
- Venio Support script version used since ~2021
Venio Systems – Global Support Knowledge Base
Comments
0 comments
Please sign in to leave a comment.