web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Check out the Community Spotlight Video Series
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / GP 2018 year-end updat...
Microsoft Dynamics GP (Archived)

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) ShareShare
ReportReport
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

I have the same question (0)
  • Derek Albaugh Profile Picture
    Derek Albaugh Microsoft Employee on at

    You can try the following, which is pretty much the blog steps:

    1. Restore the DYNAMICS/system database back to the prior version before year-end, run the following script against your DYNAMICS/system database:

          Select * into ASIEXP86BAK from ASIEXP86

    2. Next, remove all records from the ASIEXP86 table, using this script:

          Delete ASIEXP86

    3. Launch the Dynamics GP 2018 year end upgrade which should upgrade the ASIEXP86 table without any errors.

    4. Once the upgrade of the DYNAMICS/system database is completed, you can then move the records from the copied ASIEXP86BAK table back into the ASIEXP86 table, then run the following INSERT scripts to add the new records to this table, which is what Utilities should be doing:

    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)

    If you still get the same errors you're mentioning seeing with ASIEXP86, we'd probably need a support case opened so we can identify why you're seeing those errors, which isn't what we've been seeing, even with this table issue.

    Thanks

  • Kaylasch Profile Picture
    Kaylasch 267 on at

    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.

  • Verified answer
    Derek Albaugh Profile Picture
    Derek Albaugh Microsoft Employee on at

    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 Profile Picture
    Kaylasch 267 on at

    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!

  • Derek Albaugh Profile Picture
    Derek Albaugh Microsoft Employee on at

    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 Profile Picture
    Kaylasch 267 on at

    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.

  • olegvi Profile Picture
    olegvi 831 on at

    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 Profile Picture
    Kaylasch 267 on at

    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.

  • Verified answer
    olegvi Profile Picture
    olegvi 831 on at

    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 Profile Picture
    Kaylasch 267 on at

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the Community Spotlight Video Series

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans