+5I have just exported and imported a project version from one Azure tenant to a new Azure tenant and when I now do the creation it fails on creating functions. The reason is that the function to create the tsf_user() has this layout:
create function "tsf_user"
(
)
returns varchar(128)
returns varchar(128)
with schemabinding
with schemabinding
as
begin
Who can help me with this?
Best answer by Erwin Ekkel
You can run this script to remove the double entries (use with care!). Please first use the select code on top before actually running the delete.
; -- select double entries.
with x as (
select project_id
,project_vrs_id
,max(subroutine_option_no) as max_subroutine_option_no
from subroutine_option
where subroutine_id = 'tsf_user'
and subroutine_type_option_id = 'SCHEMABINDING'
group by project_id
,project_vrs_id
having count(*) > 1
)
select *
from subroutine_option as so
join x
on so.project_id = x.project_id
and so.project_vrs_id = x.project_vrs_id
-- and so.subroutine_option_no = x.max_subroutine_option_no
where so.subroutine_id = 'tsf_user'
and so.subroutine_type_option_id = 'SCHEMABINDING'
; -- delete double entries
with x as (
select project_id
,project_vrs_id
,max(subroutine_option_no) as max_subroutine_option_no
from subroutine_option
where subroutine_id = 'tsf_user'
and subroutine_type_option_id = 'SCHEMABINDING'
group by project_id
,project_vrs_id
having count(*) > 1
)
delete so
from subroutine_option as so
join x
on so.project_id = x.project_id
and so.project_vrs_id = x.project_vrs_id
and so.subroutine_option_no = x.max_subroutine_option_no
where so.subroutine_id = 'tsf_user'
and so.subroutine_type_option_id = 'SCHEMABINDING'
+5It turns out that the schemabinding on this function has been added twice.
Did you recently upgrade, if so what version are you using right now? We seen this issue happening after a platform upgrade on some occasions.
+5Nope, I moved from 2022.2 to 2022.2. Just a migration.
I do see in the DB that there are 2 tsf_user subroutine_option_no's
You can run this script to remove the double entries (use with care!). Please first use the select code on top before actually running the delete.
; -- select double entries.
with x as (
select project_id
,project_vrs_id
,max(subroutine_option_no) as max_subroutine_option_no
from subroutine_option
where subroutine_id = 'tsf_user'
and subroutine_type_option_id = 'SCHEMABINDING'
group by project_id
,project_vrs_id
having count(*) > 1
)
select *
from subroutine_option as so
join x
on so.project_id = x.project_id
and so.project_vrs_id = x.project_vrs_id
-- and so.subroutine_option_no = x.max_subroutine_option_no
where so.subroutine_id = 'tsf_user'
and so.subroutine_type_option_id = 'SCHEMABINDING'
; -- delete double entries
with x as (
select project_id
,project_vrs_id
,max(subroutine_option_no) as max_subroutine_option_no
from subroutine_option
where subroutine_id = 'tsf_user'
and subroutine_type_option_id = 'SCHEMABINDING'
group by project_id
,project_vrs_id
having count(*) > 1
)
delete so
from subroutine_option as so
join x
on so.project_id = x.project_id
and so.project_vrs_id = x.project_vrs_id
and so.subroutine_option_no = x.max_subroutine_option_no
where so.subroutine_id = 'tsf_user'
and so.subroutine_type_option_id = 'SCHEMABINDING'
+5It did solve it temporarily but every time I run a creation the issue returns.
What I did to resolve this is I deleted the subroutine option row for date_helper, since that project was using a different option number. Then re-added the record with the same number as the other projects. Now it is the same across all projects.
If you run this select does the double entry show up in any of the base projects?
with x as (
select project_id
,project_vrs_id
,max(subroutine_option_no) as max_subroutine_option_no
from subroutine_option
where subroutine_id = 'tsf_user'
and subroutine_type_option_id = 'SCHEMABINDING'
group by project_id
,project_vrs_id
having count(*) > 1
)
select *
from subroutine_option as so
join x
on so.project_id = x.project_id
and so.project_vrs_id = x.project_vrs_id
-- and so.subroutine_option_no = x.max_subroutine_option_no
where so.subroutine_id = 'tsf_user'
and so.subroutine_type_option_id = 'SCHEMABINDING'
+5No just one.
However the subroutine_option_no for tsf_user was 7 for one of the projects and for all others it was 2.
When I ran creation both tsf_user subroutine_option_no started to appear. Also in any other environment I have the number is the same for all projects.
The delete i send you earlier deletes only 1 row. If you have 7 entries you have to run the delete multiple times.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
