Upgrade 4.02 => 6.02 SQL Error

deurges

Hi guys,
I try to upgrade our Testsystem from NAV 4.02 to NAV 6.02.

I've executed all necessary steps till step 8 from the Upgrade Quick Guide 400_2009SP1 document.

1. Customize new standard objects.
2. Prepare for the data conversion.
3. Convert the database to Microsoft Dynamics NAV 2009 SP1.
4. In Object Designer, import the Upgrade4002009.1.fob file.
5. For each company, perform any manual data or object changes to prepare the data for step 1 of the upgrade.
6. Run codeunit 104045, Upgrade NAV 2009 Step 1.
7. On form 104001, click Delete Objects.

8. Import the new customized objects that you created in the compare and merge process (part of step 1).

When I try to import the customized tables I will get several sql error messages for some obejcts.
For example: Table 48 "Invt. Posting Buffer"

5074,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The statistics 'Posting Group 1' is dependend on column 'Location Code'.
4922,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE ALTER COLUMN Location Code failed because one or more objects access this column.

There were no customization done for this object. It's absolute standard.
I've cleared any boxes in the Maintain View, Maintain Relationships, and Maintain Defaults fields, as described in step 2.

Has anybody an idea what is going wrong?

Thanks in advance.

Regards Mike

matteo_montanari

deurges wrote:

Hi guys,
I try to upgrade our Testsystem from NAV 4.02 to NAV 6.02.

I've executed all necessary steps till step 8 from the Upgrade Quick Guide 400_2009SP1 document.

1. Customize new standard objects.
2. Prepare for the data conversion.
3. Convert the database to Microsoft Dynamics NAV 2009 SP1.
4. In Object Designer, import the Upgrade4002009.1.fob file.
5. For each company, perform any manual data or object changes to prepare the data for step 1 of the upgrade.
6. Run codeunit 104045, Upgrade NAV 2009 Step 1.
7. On form 104001, click Delete Objects.

8. Import the new customized objects that you created in the compare and merge process (part of step 1).

When I try to import the customized tables I will get several sql error messages for some obejcts.
For example: Table 48 "Invt. Posting Buffer"

5074,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The statistics 'Posting Group 1' is dependend on column 'Location Code'.
4922,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE ALTER COLUMN Location Code failed because one or more objects access this column.

There were no customization done for this object. It's absolute standard.
I've cleared any boxes in the Maintain View, Maintain Relationships, and Maintain Defaults fields, as described in step 2.

Has anybody an idea what is going wrong?

Thanks in advance.

Regards Mike

Hi

I think you have some manually created sql statistics (not automatic statistics).
You must delete them to resolve this error.

Try this query on a test database:

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME 
DECLARE @sql NVARCHAR(4000) 
DECLARE dropstats CURSOR FOR 

SELECT stats.name, objects.name, schemas.name 
FROM sys.stats 
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID 
JOIN sys.schemas ON objects.schema_id = schemas.schema_id 
WHERE stats.stats_id > 0 
  AND stats.stats_id < 255 
  AND objects.is_ms_shipped = 0
  AND stats.user_created <> 0
ORDER BY objects.OBJECT_ID, stats.stats_id DESC 

OPEN dropstats 
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername 
WHILE @@fetch_status = 0 
BEGIN 
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname) 
  EXEC sp_executesql @sql   
  PRINT @sql 
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername 
END 
CLOSE dropstats 
DEALLOCATE dropstats 

this query drops manually created statistics.

Bye

Matteo