GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

(0) Share

Report

Posted on by Kaylasch

267

Hello,

I applied the 2018 year end update to GP. The upgrade failed while loading required data for ASIEXP86. I deleted the contents of the table per blog post https://community.dynamics.com/gp/b/dynamicsgp/archive/2018/09/07/microsoft-dynamics-gp-2018-r2-and-year-end-upgrade-known-upgrade-issues

I ran the upgrade again and it failed. I ran a dexsql.log and get the following:

Create statement

/* Date: 12/11/2018 Time: 14:28:26

stmt(138493976):*/

/*Begin_ASIEXP86_T*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ASIEXP86_T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ASIEXP86_T]

CREATE TABLE [dbo].[ASIEXP86_T]

Insert into the temp table

/* Date: 12/11/2018 Time: 14:28:26

stmt(138493976):*/

BULK INSERT [DYNAMICSR2]..[ASIEXP86_T] FROM 'C:\Program Files (x86)\Microsoft Dynamics\GP2018$R2\DATA\Temp\ASIEXP86.out' WITH (CODEPAGE = 'ACP', KEEPNULLS, FIELDTERMINATOR = '||', ROWTERMINATOR = '#EOR#\n')

Error message

/* Date: 12/11/2018 Time: 14:28:26

SQLSTATE:(01000) Native Err:(15472) stmt(138493976):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]The object '(null)' does not have any indexes, or you do not have permissions.*/

/* Date: 12/11/2018 Time: 14:28:26

SQLSTATE:(01000) Native Err:(50000) stmt(138493976):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Primary keys do not match*/

/* Date: 12/11/2018 Time: 14:28:26

SQLSTATE:(37000) Native Err:(102) stmt(138493976):*/

[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'ON'.*/

I have deleted the temp table in SQL, and still the issue occurs.

*This post is locked for comments

All responses (11)

Answers (2)

olegvi 799 on at

Like (1)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

Glad I could help!
Kaylasch 267 on at

Like (0)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

Hello Oleg, I ran into this same issue with another client, and your instructions worked like a charm. Thank you!
Verified answer

olegvi 799 on at

Like (2)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

Apparently, the structure of ASIEXP86 table has changed in GP 2018 version 18.2.1060. New constraint has been added to the table.

I had to back up the data:

*****************************

select *

into ASIEXP86_Backup

from ASIEXP86

*****************************

, drop the table :

********************************

drop table ASIEXP86

*********************************

and recreate it with new table structure:

*********************************************

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ASIEXP86](

       [ASI_Favorite_Dict_ID] [smallint] NOT NULL,

       [ASI_Favorite_Type] [smallint] NOT NULL,

       [ASI_Favorite_Save_Level] [smallint] NOT NULL,

       [CMPANYID] [smallint] NOT NULL,

       [USRCLASS] [char](15) NOT NULL,

       [USERID] [char](15) NOT NULL,

       [ASI_Favorite_Name] [char](81) NOT NULL,

       [ASI_Field_Sequence] [smallint] NOT NULL,

       [ASI_Include_Column] [tinyint] NOT NULL,

       [ASI_Display_Column] [tinyint] NOT NULL,

       [ASI_Field_Number_Dict_ID] [smallint] NOT NULL,

       [ASI_Field_Number] [smallint] NOT NULL,

       [ASI_Column_Display_Name] [char](81) NOT NULL,

       [ASI_Display_Column_Width] [smallint] NOT NULL,

       [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PKASIEXP86] PRIMARY KEY CLUSTERED

(

       [ASI_Favorite_Dict_ID] ASC,

       [ASI_Favorite_Type] ASC,

       [ASI_Favorite_Save_Level] ASC,

       [CMPANYID] ASC,

       [USRCLASS] ASC,

       [USERID] ASC,

       [ASI_Favorite_Name] ASC,

       [ASI_Field_Sequence] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

****************************************************

Import the data from ASIEXP86_Backup table using wizard.

And then insert additional records for new SmartList:

**********************************************************

insert into ASIEXP86

values

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',1,1,1,0,22600,22600                                                                            ,0),

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',2,1,1,0,22601,22601                                                                            ,0),

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',3,1,1,0,22606,22606                                                                            ,0),

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',4,1,1,0,22637,22637                                                                            ,0),

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',5,1,1,0,22638,22638                                                                            ,0),

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',6,1,1,0,22787,26767                                                                            ,0),

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',7,1,1,0,22681,22681                                                                            ,0),

(0,6,1,0,'               ','               ','Deposits on Unposted Sales Transactions*                                         ',8,1,1,0,22683,22683                                                                            ,0)

************************************************

I hope this helps.
Kaylasch 267 on at

Like (0)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

If the steps above (basically the same as the blog post) doesn't resolve, and you are getting the error "The object '(null)' does not have any indexes, or you do not have permissions.*/" in the dex.sql log, I believe the next step was to open a case with support to help move the data into a backup table, drop and recreate the table with the correct table structure, and then move the data back in and try the upgrade again.
olegvi 799 on at

Like (0)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

I just got the same exact error while upgrading from GP2015 14.00.1230 latest SP to GP 2018 18.2.1060 latest SP.

I tried to follow the blog by removing the data from ASIEXP86 table and the upgrade failed again.

Is there a resolution to this issue?
Kaylasch 267 on at

Like (0)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

I downloaded the year end update and setup a new environment for it without issue. I will probably just remove the previous one. It was saying the object Null does not have indexes so whatever it was referencing was blank or it looks like it couldn't find what it was expecting.
Derek Albaugh on at

Like (0)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

Yeah it is odd...… the script is just creating a temp table, populating it via an .out file and then trying to put it into the existing ASIEXP86 table.

The issue we're seeing is that the .out file has data in it that already exists in the ASIEXP86 table, which causes the PRIMARY KEY error, that we normally see.

In your case, it's not giving a primary key error, but mentioning that the primary key doesn't match, the object is missing indexes, etc., which every time I've seen that type of error, had to do with the table structure being incorrect, so we drop and re-create the table at the original version and then try the upgrade again, which tends to get past the error.

Odd error if this is a new GP 2018 R2 install and you created the databases anew as well to test the upgrade. I'd say maybe try downloading new media and test it again creating new system/company databases, if you wanted to test further.

I will say it's not an error we've been seeing at all with the year-end upgrades, for any version of GP.

Thanks
Kaylasch 267 on at

Like (0)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

Hello Derek,

This was a fresh GP 2018 R2 client installed on my work computer with TWO as the company database. I wanted to check to see what the resolution is in case I encounter it with a customer. If I do, I will open a case. Thank you!
Verified answer

Derek Albaugh on at

Like (1)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

Yes, that is a different error then what we normally see with that table. Since you've removed the data and still getting the error, I'd try dropping and re-creating the table at the previous version you're coming from, as it is mentioning the Primary Key doesn't match, so that is usually indicative of the original table having incorrect structure.

If you need help doing this, I'd recommend opening a support case, as it would be easier to handle this that way then through the forums.

Thanks
Kaylasch 267 on at

Like (0)

Report

RE: GP 2018 year-end update. Delete contents of ASIEXP86 per blog. Upgrade fails with he object '(null)' does not have any indexes, or you do not have permissions

Hello Derek,

I had followed the blog post before writing the forum post. ASIEXP86 has no rows in the DYNAMICS database, and I am getting that error.