Skip to main content
Solved

Migrate to new server tsf_user function cannot be created


Kasper Reijnders
Forum|alt.badge.img+5

I 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'
 

View original
This topic has been closed for comments

8 replies

Kasper Reijnders
Forum|alt.badge.img+5

It turns out that the schemabinding on this function has been added twice.

 

 


Forum|alt.badge.img+16
  • Moderator
  • 737 replies
  • January 5, 2023

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. 

 

 


Kasper Reijnders
Forum|alt.badge.img+5

Nope, 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

 


Forum|alt.badge.img+16
  • Moderator
  • 737 replies
  • Answer
  • January 5, 2023

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'
 


Kasper Reijnders
Forum|alt.badge.img+5

It 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. 

 


Forum|alt.badge.img+16
  • Moderator
  • 737 replies
  • January 10, 2023

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'


Kasper Reijnders
Forum|alt.badge.img+5

No 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. 

 


Forum|alt.badge.img+16
  • Moderator
  • 737 replies
  • January 10, 2023

The delete i send you earlier deletes only 1 row. If you have 7 entries you have to run the delete multiple times. 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings