Problem with Drill down

Slawek_Guzek

This is what I am trying to tell you - it is very unlikely that the problem is caused by the SQL Server.

Post your solution here - the source table, the matrix form, the drill down form, maybe other objects which are involved, all as text inserts or attachments, not as a screenshots. As David Singleton said before you are hiding too much information to be able to get good help.

pruebasEx

Slawek_Guzek wrote: »

This is what I am trying to tell you - it is very unlikely that the problem is caused by the SQL Server.

Post your solution here - the source table, the matrix form, the drill down form, maybe other objects which are involved, all as text inserts or attachments, not as a screenshots. As David Singleton said before you are hiding too much information to be able to get good help.

Which type of attachment are allowed?

Because I can't put all the code/information in a (only 1) message.

pruebasEx

EDITED

pruebasEx

EDITED

pruebasEx

EDITED

pruebasEx

Slawek_Guzek wrote: »

This is what I am trying to tell you - it is very unlikely that the problem is caused by the SQL Server.

Post your solution here - the source table, the matrix form, the drill down form, maybe other objects which are involved, all as text inserts or attachments, not as a screenshots. As David Singleton said before you are hiding too much information to be able to get good help.

I posted the form and the tables involved in the comments.

Slawek_Guzek

Could you also post the source of the Form 50033, which shows drill down details for table 50203 Cab. ticket please.

Try to attach the text or zip file rather than posting it in the body as it makes reading the object definition quite difficult. If you cannot attach text or zip insert it as Code

pruebasEx

EDITED

David_Singleton

pruebasEx wrote: »

I only assume that I have same objects with same code, same object properties, same formulas etc... That's why I thought that...

You probably don't remember a comedienne named Lucille Ball, but she had a funny skit that is appropriate here.

Lucy is in the living room looking for something, her husband comes in and asks "Lucy what are you looking for",
she replies "I lost an earring"
he help to search, but after an hour of no luck asks her "Where did you lose the earring."
She replies "In the bedroom"
"WHAT then why are we searching in the living room"
Lucy says "Because the light is broken in the bedroom, so it's easier to search here in the living room where the light is better"

Point is to focus your energy on likely causes of a problem, instead of wasting time in areas that are unlikely to be the source of the problem.

pruebasEx

I do not know if anyone said anything similar, but the solution to this problem was to remove the key that was being used by the SUMINDEX.

1-Disable de check of the Key.
2- Compile
3- Enable check again.
4- Compile.


When I try to use a new variable with the value of the bad SUMINDEX, I got an error that seems to be a bug or an inconsistence with SQL. The error was about the option NOEXPAND from SQL query.

Slawek_Guzek

Hi,

I'm glad that you have found the problem but I have two questions.

Have you tried disabling and re-enabling MaintainSIFTIndex as it was suggested?

Do you have any database checking jobs running against the customer database?

Slawek

pruebasEx

Slawek_Guzek wrote: »

Hi,

I'm glad that you have found the problem but I have two questions.

Have you tried disabling and re-enabling MaintainSIFTIndex as it was suggested?

Do you have any database checking jobs running against the customer database?

Slawek

1- No
2- No

I think I forgot to test that option @Slawek_Guzek .

It's any difference beetwen tick and untick ENABLE option in key and tick(untick) MaintainSIFTIndex option?.

I saw this now that I know the error but I not remember exactly the error. The last message fix the problem re entering again some keys, but I still can not understand this error.

https://forum.mibuso.com/discussion/56972/g-l-entry-sql-server-error

Slawek_Guzek

It's any difference beetwen tick and untick ENABLE option in key and tick(untick) MaintainSIFTIndex option?.

Yes, there is a difference.

If you unclick Enable on the key NAV drops the underlying index and also any if you have a SIFT fields defined on the key it also drops indexed views handling those SIFT fields

If you untick MaintainSIFTIndex NAV only drops the indexed views, but the key's index remain unchanged. If you untick MaintainSIFTIndex and try to CALCFIELD a flowfield using that key NAV would simply summarize the data from the table.

Sorry if it sounds a bit confusing. A key on a table is the NAV thing, an index is the SQL thing, they usually live together but you can separate them. A key on a table in NAV is a logical structure required if you want particular sorting, it usually has a corresponding index on the SQL table.

Then on the top of that there is a SIFT key - a key where you have defined SIFT fields. Again this is a NAV thing, and it usually has its own corresponding SQL structure - an indexed view.


In your case both operations, disable/enable the key or disable/enable MaintainSIFTIndex option, would produce the same results - the indexed view would be dropped and then rebuild.


The error with NOEXPAND has something to do with indexed view. When you define a SIFT field on a key NAV creates a view, and then creates a clustered index on that view (hence the "indexed view" name) . When you do CALCFIELDS or CALCSUMS NAV queries one of those indexed views, using WITH(NOEXPAND) hint.

The hint WITH(NOEXPAND) is only valid for indexed views - therefore an error "Hint 'noexpand' ... is Invalid" suggests that the index on the view has been deleted. It is not possible to delete it from NAV, but a database administrator can delete it.

Question arises - does your customer have a database administrator who is trying to play around with the indexes outside NAV? Or perhaps the error is the result of some sort of data corruption - which should be detected if you've had a job checking the database integrity run periodically.

Slawek