You receive an error message when you try to access, to enter, to save, to print, or to post transactions in the Sales Transaction Entry window in Microsoft Dynamics GP

SYMPTOMS

When you try to access, to enter, to save, to print, or to post transactions in the Sales Transaction Entry window in Microsoft Dynamics GP, you receive one of the following error messages:

A get/change operation on table <table_name> failed accessing SQL data
A get/change operation on table ‘SOP_HDR_WORK’ failed accessing SQL data
A get/change operation on table ‘SOP_LINE_WORK’ failed accessing SQL data
A save operation on <table_name> cannot find the table
A remove operation on <table_name> failed accessing SQL data
A get/change operation on table ‘SOP_HDR_HIST’ failed accessing SQL data

Note The <table_name> placeholder represents the actual table name.

CAUSE

This problem occurs if a Sales Order Processing table contains damaged or duplicate records.

RESOLUTION

To resolve this problem, follow these steps.

Run the Check Links procedure

  1. On the Microsoft Dynamics GP menu, point to Maintenance, and then click Check Links.
  2. In the Series list, click Sales.
  3. In the Logical Tables list, click the table that appears in the error message that you received.
  4. Click Insert, and then click OK.
  5. If you are prompted to print the Error log, select a destination, and then click OK.

    Note For more information about table names, use the Resource Descriptions window. To open the Resource Descriptions window, point to Resource Descriptions on the Tools menu, and then clickTables.

  6. Perform the action that caused the error message that you received.

If you still receive the error message, follow these steps.

Re-create the auto procedure

  1. On the Microsoft Dynamics GP menu, point to Maintenance, and then click SQL.
  2. In the Database list, click the company database, and then click the auto procedure that you want to re-create.
  3. Click to select the Drop Auto Procedure and Create Auto Procedure check boxes, and then clickProcess.
  4. Perform the action that caused the error message that you received.

If you still receive the error message, follow these steps.

Re-create the table

To re-create the table without deleting records that are in the table, use the Toolkit. The Toolkit is a tool in the Professional Services Tools Library.

For more information about the Professional Services Tools Library (PSTL), use one of the following options:

Customers:
For more information about PSTL, contact your partner of record. If you do not have a partner of record, visit the following web site to identify a partner: Microsoft Pinpoint

Partners:
For more information about PSTL, visit the following web site:

https://mbs.microsoft.com/partnersource/downloads/releases/noam_pstl.htm

To re-create the table, follow these steps:

  1. Exit Microsoft Dynamics GP.
  2. Back up the Microsoft Dynamics GP code folder.

    Notes

    • By default, the Microsoft Dynamics GP code folder is in the following location:
      C:\Program Files\Microsoft Dynamics\GP
  3. Install the Professional Services Tools Library.
  4.  Start Microsoft Dynamics GP.
  5. When you are prompted to include new code, click Yes.
  6. Add the Professional Services Tools Library to the Shortcuts area. To do this, follow these steps:
    1. In the Shortcuts area, click Add, and then click Other Window.
    2. Expand Technical Service Tools, expand Project, and then click Professional Services Tools Library.
    3. Click Add, and then click Done.
  7. In the Shortcuts area, click Professional Services Tools Library.
  8. When you are prompted to enter registration keys, click Cancel.
  9. Click Toolkit, and then click Next.
  10. Click Recreate SQL Objects, and then click Next.
  11. In the Series list, click Sales.
  12. In the Table list, click the table that appears in the error message that you received.
  13. Click Recreate Selected Table, click to select the Recreate data for selected table(s) check box, and then click Perform Selected Maintenance.

    Note If the table has a custom trigger, you must manually re-create the trigger.

  14. Perform the action that caused the error message that you received.
MORE INFORMATION

If you still receive one of the error messages that is described in the “Resolution” section, try these troubleshooting suggestions.

  • Create a Dexsql.log file, and then locate the error message that you received in the Dexsql.log file. The data that comes before the error message may help you determine the cause of the error message.

    For more information about how to create a Dexsql.log file, click the following article number to view the article in the Microsoft Knowledge Base:

    850996 How to create a Dexsql.log file for Microsoft Dynamics GP
  • Determine whether the error message that you received is caused by a particular report. For example, if you receive the error message only when you print a particular order or invoice report, the problem may be caused by this report. In this situation, restore the Reports.dic file from a backup copy.

    Notes

    • By default, the Microsoft Dynamics GP Reports.dic file is in the following location:
      C:\Program Files\Microsoft Dynamics\GP
  • Determine whether the error message that you received is caused by a modified report. To do this, print the original report. If you do not receive the error message when you print the original report, re-create the Reports.dic file.

    For more information about how to re-create the Reports.dic file, click the following article number to view the article in the Microsoft Knowledge Base:

    850465 How to re-create the Reports.dic file in Microsoft Dynamics GP
  • Determine whether the error message that you received is caused by a particular user ID or workstation. To do this, start Microsoft Dynamics GP on a different workstation. Log on by using the user ID that was used when you received the error message. If you do not receive the error message, reinstall Microsoft Dynamics GP on the workstation on which you received the error message. If you do receive the error message on another workstation, the problem may be caused by the user ID. In this situation, create a new user ID.
  • Determine whether the error message that you received is caused by a particular document number. If you receive the error message only when you use a particular document number, delete this document number. Then, reenter it.
  • If you imported the data, manually enter a document in the Sales Transaction Entry window. If you do not receive the error message, the problem may be caused by the import process.
  • Determine whether the error message that you received is caused by a customization or by a third-party product. To do this, disable the customization or the third-party products.
  • Verify that the Open Database Connectivity (ODBC) data source on the workstation is set up correctly.
  • Determine whether the table contains damaged information. To do this, run the following statement in SQL Query Analyzer. Then, verify the records that are returned.
    Select  * from <table_name>

    Note The <table_name> placeholder represents the actual table name.

For information about technical support for the products that are discussed in this article, visit the following Microsoft Web site:

Posted in Uncategorized | Leave a comment

Great Plains Error – A get/change first operation on table ‘coProcess’ failed

1. Log in to Microsoft Dynamics GP as sa user.
2. Click the Tools menu, click Setup, and then click User.
3. Click the user who is receiving the error message. Make sure that you can successfully access this user.
4. On the Tools menu, click Setup, and then click User Access.
5. Select the user who is receiving the error message. Then, click to clear the User Access check box for the new company.
6. On the Tools menu, click Setup, and then click User Access.
7. Click to select the User Access check box. Give access to the new company to the user. Do this for all users who are receiving the error message.
8. Run the Grant.sql script against the company that is experiencing the problem. Also, run the script against the DYNAMICS database.

Notes” In Microsoft SQL Server 2000, run the script in SQL Query Analyzer. In Microsoft SQL Server 2005, run the script in SQL Server Management Studio.
” The Grant.sql script is located in the following path:
%Program Files%\Microsoft Dynamics GP\SQL\Util

Posted in Uncategorized | Leave a comment

Error message when you try to delete a user record that is stuck in the User Activity window: “Get Change Operation on SY_Current Activity Record Was Already Locked”

SYMPTOMS

When you try to delete a user record that is stuck in the User Activity window in Microsoft Dynamics GP or in Microsoft Business Solutions – Great Plains, you receive the following error message:

Get Change Operation on SY Current Activity Record Was Already Locked
RESOLUTION

To resolve this problem, delete the record that is associated with the stuck user from the ACTIVITY table by using SQL Server Management Studio or SQL Query Analyzer.

To delete the record from the ACTIVITY table, follow these steps:

  1. Determine the userid of the user who is stuck by viewing the User Activity window. To do this, follow the steps for your version of the program.

    Microsoft Dynamics GP 2010 or Microsoft Dynamics GP 10.0

    On the Microsoft Dynamics GP menu, point to Tools, point to Utilities, point to System, and then clickUser Activity.

    Microsoft Dynamics GP 9.0 or earlier versions

    On the Tools menu, point to Utilities, point to System, and then click User Activity.

  2. On the computer that is running Microsoft SQL Server, start SQL Server Management Studio or SQL Query Analyzer. To do this, follow the steps for the program that you use.

    SQL Server Management Studio 2008 or SQL Server Management Studio 2005

    1. Click Start.
    2. Click Programs.
    3. Point to Microsoft SQL Server 2005 or to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
      Note The Connect to Server window opens.
    4. In the Server name box, type the name of the instance of SQL Server.
    5. In the Authentication list, click SQL Authentication.
    6. In the User name box, type sa.
    7. In the Password box, type the password for the sa user.
    8. Click Connect.

    SQL Server 2000

    1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
    2. In the Server name box, type the name of the instance of SQL Server.
    3. In the Authentication list, click SQL Authentication.
    4. In the User name box, type sa.
    5. In the Password box, type the password for the sa user, and then click Connect.
  3. Make a SQL backup copy of the ACTIVITY table by running the following script.
    SELECT * into DYNAMICS..ACTIVITY_BAK from DYNAMICS..ACTIVITY
  4. Run the following script against the DYNAMICS database.
    DELETE DYNAMICS..ACTIVITY WHERE USERID = '<XXX>'
    

    Note Replace <XXX> with the userid that you want to remove. For example, run the following script:

    DELETE DYNAMICS..ACTIVITY WHERE USERID = ‘John’
  5. Start Microsoft Dynamics GP.
  6. After you verify that the user was removed successfully from the ACTIVITY table, and you can log in successfully, run the following script to drop the backup table that you created in step 3.
    DROP table DYNAMICS..ACTIVITY_BAK
    
    
    
    Reference by Microsoft Dynamics
Posted in Uncategorized | Leave a comment

Steps for Installing eConnect using the Microsoft Installation Media

This week’s Tech Tuesday is from our support guru, David Youngquist, who has included steps for installing eConnect using the Microsoft Dynamics GP 2013 installer instead of installing it from the SmartConnect media below:

1. If you prefer to install eConnect using the Microsoft Dynamics GP installation media, the eConnect installer can be found in the following folder(s)

If you are on a 32 bit machine, the installer is in the AdProdeConnectx86 folder.
If you are on a 64 bit machine, the installer is in the AdProdeConnectx64 folder.

The file you want to run is named econnect.exe

2. When installing from the GP installation media, the only required component is the eConnect Runtime, the other options are not required for SmartConnect. Make sure eConnect Runtime Services is selected click the Next button.

3. This screen is asking for a Windows login account. This is not a SQL account, this needs to be a domain user that has been added to the DYNGRP for all the Dynamics databases in SQL. In my screenshot my Domain is EONEDEMO, my username is eConnect.

4. On this screen you need to type in the Dynamics GP SQL server nameinstance. On eConnect 2013 it will ask for your Dynamics database name. On 2010 and prior it assumes it is named DYNAMICS. You should type in the sa username and password on this screen. It will connect to SQL and add your eConnect user to the DYNGRP. In this screenshot, my GP server is named EONE-2014, and my SQL instance is named EONE. My Dynamics database name for this particular installation is DYN2013.

5. On this final screen hit the Install button.

6. Once eConnect finishes installing, go to Start – Run and type in “services.msc”. In the services window, look for the service named “eConnect for Microsoft Dynamics GP 20XX Integration Service”. If you see the service there and the status says Running, then eConnect has been successfully installed.

Reference by …Danielle Erickson

Posted in Uncategorized | Leave a comment

GP2015 R2 Upgrade – “ASI_MSTR_Explorer_Favorites: Number of results columns doesn’t match table definition”

After upgrading to GP2015 R2, while doing a routine testing, following error message was thrown while trying to open Smartlist:

Screen Shot 2015-07-31 at 6.53.32 PM

Full message as follows:

Unhandled database exception:
A get/change operation on table ‘ASI_MSTR_Explorer_Favorites’ failed accessing SQL data.

Number of results columns doesnt match table definition.

Number of results columns doesnt match table definition.

EXCEPTION_CLASS_DB
DB_ERR_SQL_DATA_ACCESS_ERR

Debugging this error took me to the table Smartlist Favorites Master (ASIEXP81). Strange, however, that number of columns indeed matched.

I remembered this issue happening in GP 10 and GP 2010. Suggested solutions were to run GRANT.SQL on all databases andchanging ODBC provider to SQL Native Client. However, in my case, both were already done and confirmed.

To avoid any further delay or issue(s), I decided to backup the records from ASIEXP81, recreate the table using GP SQL Maintenance and push the records back from the backup table.

Resolution: Drop and create the table Smartlist Favorites Master (ASI_MSTR_Explorer_Favorites, ASIEXP81) using GP SQL Maintenance.

Only that, I did not have to reinsert the records. When I opened the Smartlist after recreating the table, it automatically populated all records. Strange, again. But it worked.

Word of caution: Always take backup before you do anything with the data.

Reference by VAIDY

Posted in Uncategorized | Leave a comment

“String overflow on set” message in Microsoft Dynamics GP,….Unhandled script exception: String overflow on set ‘[Not Found]’. EXCEPTION_CLASS_SCRIPT_STRING_OVERFLOW,…Symption #2 – A compupter check batch in Payables Management keeps going to Batch Recovery and you get this message: String overflow on set ‘Voucher Number – WORK’.

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

OPTION 1: In the top menubar in Microsoft Dynamics GP, click on DEBUG and then click to clear Show Debug Messages.  Then test again and the issue should be resolved. However it is recommended that the Debug menu is not enabled in a live system as there can be other side effects.  We recommend to do Option 2 as well.

OPTION 2: To resolve this problem, navigate to the code folder for Microsoft Dynamics GP. The default location is usually: C:\Program Files\Microsoft Dynamics GP\GP\Data. Right click on the Dexx.ini file and open it with Notepad. Review the file for the below lines and set them to FALSE or as shown below:           (reference by Microsoft Dynamics )

ShowDebugMessage=FALSE
ScriptDebugger=FALSE
ScriptDebuggerProduct=0


Posted in Uncategorized | Leave a comment

Updating Inventory Decimal Places through SQL

UPDATE IV00105 SET DECPLCUR = 3
WHERE ITEMNMBR = ‘XXX’
GO
UPDATE IV00101 SET DECPLCUR = 3
WHERE ITEMNMBR = ‘XXX’

Posted in Uncategorized | Leave a comment