Solved

error in sql_indexes during generation after upgrading to SF2022.2

  • 5 August 2022
  • 3 replies
  • 47 views

Userlevel 3
Badge +11

We have upgraded the SF to 2022.2. During the generation of the project the following error occurs on sql_indexes:

The MERGE statement conflicted with the FOREIGN KEY constraint "ref_prog_object_item_prog_object_item_parmtr". The conflict occurred in database "sf_raijmakers", table "dbo.prog_object_item".

We were unable to find missing or false keys.

We analyzed the generated code which looks like this :

/* Create index 'besteld' on table 'inkoop_order_regel'. */

if exists (select 1 from sys.fulltext_indexes fx
                    join sysobjects ot on ot.id = fx.object_id
                    join sysindexes ix on ix.id = ot.id and ix.indid = fx.unique_index_id
                    where ot.xtype = 'U'
                      and ot.name  = 'inkoop_order_regel'
                      and ix.name  = 'besteld')
   drop fulltext index on "inkoop_order_regel"
go

if exists (select 1 from sysindexes ix
                    join sysobjects ot on ot.id = ix.id
                    where ot.xtype = 'U'
                      and ot.name  = 'inkoop_order_regel' 
                      and ix.name  = 'besteld')
   drop index "besteld" on "inkoop_order_regel"
go

create nonclustered index "besteld"
on "inkoop_order_regel"
(
   "ik_type"
)
 include
 (
"ik_aantal_order", 
"ik_artikel_id"
 )
[INDX_WHERE_CLAUSE]
go

 

The [INDX_WHERE_CLAUSE] was not removed, however, we do not use where clauses here, all should be empty and removed.

 

Any help would be appreciated.

icon

Best answer by Mark Jongeling 5 August 2022, 12:59

View original

This topic has been closed for comments

3 replies

Userlevel 7
Badge +23

Hi Hugo,

This should indeed not happen. Have you run all the available hotfixes for the 2022.2? It could be that 1 of those solves this issue.

If that doesn't help, I do think we need to dive deeper to find the exact issue here. Could you create a Ticket for this in TCP and attach a model.dat / export of this project version? That way we can inspect the model and see the exact reason why this parameter does not get removed.

Userlevel 3
Badge +11

No hotfixes will be applied to database 'sf_raijmakers' because it already has all detected hotfixes applied to it.

 

I will create a ticket.

Userlevel 7
Badge +23

We found the cause.

When the Software Factory runs the control procedure that generates the objects for indexes it was generating objects for other Table types which is incorrect. In this case, a View had an Index which is not allowed and also validated. Due to the Generate definition step being in front of Validating definitions, developers tend to run into trouble earlier than seeing the Validation messages:

View has one or more indexes

The validation is a Warning which indicates that the situation is not entirely correct but should not cause any errors during development nor deploying on the end product. In this case it did lead to an error and therefore will be fixed in an upcoming platform improvement.

To resolve the error, remove the index from the View. Sorry for the inconvenience. We have received your ticket and will process it accordingly.

Edit: Improvement has been released: