Global vs. Local Tables mixup in Objects and SQL

geschwint

Did a search on the Internet and has not seen anyone else having a similar issue. Read on:

SQL Server 2005
Nav 2009 SP1 Build 29626, Classic client only

Problem indication:
It is not possible to rename a Company. A message is displayed after some time requesting you to open a Company to work with this table. Error message is: "". There is nothing in Navision indicating issues with the table in question, code or properties. It is a local table and works fine per-Company with data in it. The debugger will not trigger on this since the problem is in the client, not in code.

Analysis and resolution:
After looking into the Objects table in SQL, it turns out this table, let's call it table1, exists both as a local table and global. In SQL I can find the physical table with all companynames, but I can also find it without companyname, thus a global table. After making this Discovery, I checked the objects table a little more in detail and found 32 tables with this flaw, existing as local and global at the same time. All tables with this flaw has an id >=50000, referring to both customer tables and add-on tables.

So Navision could not handle the fact that a table was defined in Navision as local but existed also as global. Using the table was OK but to rename a Company, the strange message surfaced.

The following can be run in the database to see what objects exists both as local and global:

SELECT *
FROM [dbo].[Object] o1
WHERE [Company Name] = ''
AND [Type] = 0
AND [BLOB Reference] IS NULL
AND EXISTS (
	SELECT 'X'
	FROM	[dbo].[Object] o2
	WHERE	o2.[Company Name] <> ''
	AND o2.[Type] = 0
	AND o2.[BLOB Reference] IS NULL
	AND o2.ID = o1.ID
)

The resulting list is the objects that must be removed to make the database Clean, all listed objects were checked in Navision and defined as local per company.

The resolution was to remove the physical tables in SQL that was global in error, then remove the 32 lines in Objects table that should not be global.

To remove the lines from Objects table, run the following (same select as above but with delete instead):

Do not forget to make a backup before this destructive operation!!

DELETE [dbo].[Object]
FROM [dbo].[Object] o1
WHERE [Company Name] = ''
AND [Type] = 0
AND [BLOB Reference] IS NULL
AND EXISTS (
	SELECT 'X'
	FROM	[dbo].[Object] o2
	WHERE	o2.[Company Name] <> ''
	AND o2.[Type] = 0
	AND o2.[BLOB Reference] IS NULL
	AND o2.ID = o1.ID
)

After the above adjustment, a Company could be renamed without problems and the database is fresh again.

Has anyone seen anything even close to this total mixup of local vs. global tables?