You’re offline. This is a read only version of the page.
75,730
Index 0 of loca array is out of range in script
When I run
SELECT distinct A.ASI_Favorite_Dict_ID, A.ASI_Favorite_Type FROM ASIEXP81 A WHERE ASI_Favorite_Type between 498 and 22000 and NOT EXISTS (SELECT ASI_Favorite_Dict_ID, ASI_Favorite_Type FROM ADH00100 WHERE ASI_Favorite_Dict_ID = A.ASI_Favorite_Dict_ID and ASI_Favorite_Type = A.ASI_Favorite_Type )
I get
ASI_Favorite_Dict_ID ASI_Favorite_Type
0 498
0 499
0 500
0 501
0 502
0 503
I am following the tips in this blog but now I am stuck with these extra product IDs.
Is there anyway to get Smartlist just completely reset? Resetting just for the company database does not work. It appears the DYNAMICS database needs to be reset as well.
Derek, how can I identify this custom Smartlist? What tables will tell me that?
Thanks Derek. I am going to try to export and import this custom Smartlist. On the surface it looks like it is built on a SQL view as it has one column for each month of the year and a totals column. Something a normal Smartlist would not have.
If the custom report was created via SmartList Builder, it most likely wouldn't get re-created after re-initializing the SmartList (1493) feature, as this process would remove all custom SmartList reports, favorites and any modifications.
Custom reports created in SmartList Builder would be held in both SmartList and SmartList Builder tables, in the system database, so if this customer does have SmartList Builder installed for Dynamics GP, they may need to go into SLB, pull up this one custom SmartList and re-save it, so it gets added back into all the tables it should and then begins working again.
I also want to say, though I can't guarantee as I'm not completely familiar with eOne's version of SLB, that there was / is an option to export and import reports for SmartList Builder, so that may also be an option, exporting this custom report from SLB on the live environment and importing it onto this test environment post-upgrade to verify whether that works. If it does, then you'd have an option in case this happens in the live upgrade environment.
GP Utilities won't touch SmartList Builder at all at the 18.2.1060 version of Dynamics GP, as SLB is a third-party product, but again, the re-initialization scripts for SmartList could definitely impact SLB reports, I can't say for sure.
Thanks
Derek, finally success. Do you think I will be able to export that custom Smartlist out of GP 2015 and into GP 18.2.1060?
The ADH00101 has 107 records in it for products 497, 498 and 499.
Derek, I am going to try this right now but here is an additional clue for you. Right now we have done a test upgrade for them so we are totally in test mode. I just had them show me Smartlist in their production system. There is a custom Smartlist in there. I believe was is happening is that someone build them a Smartlist back when Smartlist builder was part of GP. Either that or someone built them a custom Smartlist. Either way this is a very important Smartlist so I need to come up with a way to get this Smartlist upgraded and into their test upgrade and eventually production upgrade.
Along with the 0/497, 0/498 and 0/499 records in the ADH00100 table, does the ADH00101 table also have records for those three default reports?
On the get/change error, is there a 2nd part to the error, or is that it?
If the second part of the error message is this: "Number of results columns doesn't match table definition.", try the following:
1. Have all users log out of Dynamics GP and make sure we have recent, valid backups of the GP databases.
2. In the Dex.ini, change SYNCHRONIZE= line to be TRUE and save changes, then close this file.
3. Launch Dynamics GP Utilities and synch the dictionary files with the account framework setup. Once that completes, look in the Dex.ini again to verify the SYNCHRONIZE= line now shows as FALSE. If so, close the Dex.ini and GP Utilities.
4. Login to Dynamics GP as 'sa' and browse to the SQL Maintenance window (Microsoft Dynamics GP > Maintenance > SQL)
5. In the SQL Maintenance window, choose the DYNAMICS/system database, then choose 'SmartList' for the Product.
6. Find and highlight the 'User SmartList Master' table in the list that populates, if not all tables.
7. Mark only the 'Drop Auto Procedure' and then 'Create Auto Procedure' options and click Process
8. Once the dex procedures for ADH00100 and/or other SmartList tables are re-created, verify with 'sa' that you can now access the SmartList window and access the reports and see data, without getting this table error message.
The ADH00100 and ADH00101 tables are part of the 'SmartList' feature, so it's odd that we'd get such an error after re-initializing that feature to re-install anew.
Let me know how this goes.
Thanks
Derek, using your scripts to reset SmartList did not help. I was prompted to launch GP Utilities and it did reinstall SmartList but under SmartList is nothing, even for 'sa'. What else can I try. Even 'sa' gets the message "get/change on table 'ADH_UIserList_MSTR failed accessing SQL data'. The ADH00100 table is there in the DYNAMICS database with a record for 497, 498 and 499.
Thank you for this thorough explanation Derek. It is a pleasure seeing and your team picking these up. I will add this to my bag of tricks.
I usually run these scripts as they are clearer:
Select * from ASIEXP81 where ASI_Favorite_Type > 499 and ASI_Favorite_Type < 2000 order by ASI_Favorite_Dict_ID, ASI_Favorite_Type
Select * from ADH00100 order by ASI_Favorite_Dict_ID, ASI_Favorite_Type
Select * from ADH00101 order by ASI_Favorite_Dict_ID, ASI_Favorite_Type
Using what you mentioned above, the ASIEXP81 won't have the default 0/498, 0/499 records in it, but the ADH00100 should have one record each and the ADH00101 will have multiple records for these two default reports, as ADH00100 is more the header table and ADH00101 is the lines/columns table.
Then, for 0/500, 0/501, 0/502 and 0/503 custom reports listed in ASIEXP81, we should again have at least one record in ADH00100 for these four custom reports and records in the ADH00101 for these four reports as well.
To re-initialize the SmartList feature, which will remove all custom reports and favorites, you can use these scripts:
A. Make sure you have recent, valid backups of your Dynamics GP databases, system and company.
B. Against the DYNAMICS system database, run the following scripts:
1. Delete DYNAMICS..DB_Upgrade where PRODID = 1493
2. Delete DYNAMICS..DU000020 where PRODID = 1493
3. Delete DYNAMICS..DU000030 where PRODID = 1493
4. Delete DYNAMICS..SY90200 where PRODID = 1493 (this table may not exist, but we need to make sure)
C. Against each and every company database mentioned in the DYNAMICS..SY01500 table, run these scripts:
1. Drop Table ASILOC90
D. Now, when you launch Dynamics GP Utilities, since SmartList is a core module, it will treat it like it's installing it for the first time, meaning it will drop and re-create all objects for this module, to include tables. In doing so, you should only have the default SmartList reports for this module and none of the custom SmartList reports or favorites.
**NOTE: If the custom reports were created via SmartList Builder, the steps and scripts above may not fully remove all of those custom reports and you may need to contact eOne who now owns SmartList Builder for Dynamics GP after GP 2013 R2.
Thanks,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (