Delete or Remove Range Errors on ‘SY_Current_Activity’ table after GP 2015 R2


David Meego - Click for blog homepageMicrosoft Dynamics GP 2015 R2 added lots of new features and enhancements and fixed a number of bugs too. However, it also introduced a change that is now causing errors in some ISV’s (Independent Software Vendor) products.

I first came across this issue when preparing to release GP Power Tools build 22 and was testing the Database Validation feature on my Microsoft Dynamics GP 2016 install.

The change that was made to Microsoft Dynamics GP 2015 R2 was the addition of the CODocViewerQD (CO00201) table to the company databases. This table is used by the Document Viewer Query Designer which is part of the All-In-One Viewers for Items, Customers and Vendors.

Adding this table itself was not the cause of the issues, but along with this table a trigger was added to the SY_Current_Activity (ACTIVITY) table. This syVendorAnalysisDeleteTrigger code is designed to clean up any orphaned records in the CO00201 table when an ACTIVITY record for a company is removed.

CREATE TRIGGER [dbo].[syVendorAnalysisDeleteTrigger] ON [dbo].[ACTIVITY]
FOR DELETE
AS
DECLARE @CMPNYNAM VARCHAR(100)
	,@UserID VARCHAR(100)
	,@INTERID VARCHAR(5)
	,@SqlStr VARCHAR(255)

SELECT @CMPNYNAM = deleted.CMPNYNAM
	,@UserID = replace(deleted.USERID, '''', '''''')
FROM deleted

SELECT @INTERID = INTERID
FROM SY01500
WHERE CMPNYNAM = @CMPNYNAM

SET @SqlStr = 'DELETE FROM ' + @INTERID + '..CO00201 WHERE USERID = ''' + RTRIM(@UserID) + ''' '

EXEC (@SqlStr)

The problem occurs if the record in the ACTIVITY table that is being removed refers to a company database that the current user does not have access to, or the company database does not exist or is not online.

Then we get errors like the example below:

error1
A remove range operation on table ‘SY_Current_Activity’ failed accessing SQL data.

When you click on More Info, you get:

error2
The server principal ‘User Name’ is not able to access the database ‘DBName’ under the current security context.

It was this second situation where Database Validation had issues cleaning up references to a non-existent company. Removing a record in the ACTIVITY table referring to the missing company database fired the trigger and generated an error. The code was changed to temporarily disable the trigger to avoid further issues.

Any ISV product that removes records from the ACTIVITY table will now need to check company access and database existence and online status and/or temporarily disable the trigger to avoid errors.

I tried to reproduce this error in core Dynamics GP by trying to use the User Activity window to delete a user in another company that the current user did not have access to and the code was smart enough to prevent this from occurring.

image-3
Please excuse the lime green, but this is my test company.

In my opinion, a cross database trigger like this is dangerous and asking for problems with permissions. In this case, it means that code that used to work safely across the entire system is now broken depending on which companies the current user has access to.

A better solution would be to place the CODocViewerQD (CO00201) table into the System Database after adding a Company ID field into the table and the indexes. The trigger should be removed and the clean up handled in the GP code along with all the other clean up that occurs when User Activity is removed.

[Edit] My friend and fellow MVP, Beat Bucher, has notified me that there is another trigger added to the ACTIVITY table when the Manufacturing module is installed that can cause the same sorts of issues. It is called mfg_delete_orphaned_MO_locks and would need to be disabled as well to avoid errors with access permissions or missing databases.

Hope you find this useful.

David

This article was originally posted on http://www.winthropdc.com/blog.

Advertisements

12 thoughts on “Delete or Remove Range Errors on ‘SY_Current_Activity’ table after GP 2015 R2

  1. Hello David! Great to see you at GPUG! I just ran into this issue today, and it seems that the table is actually missing. We get the error when we try to remove sa from a company database in Activity. When we go to the company referenced, that CO table doesn’t exist. They are upgrading to GP 2016R2. So I’m not sure why the upgrade didn’t create the table. It could be an oddity between versions, we went from 2010 to 2015R2 then to 2016R2, so who knows. I’ve seen the upgrade miss some objects or tables before even with no errors, but I’ve never been able to explain why in one customer’s system it got created, and another it got missed. You mention 3rd parties in your article, the only ones they have are Extender and Smartlist builder.

    Like

  2. Hi David, it’s me again! A little more troubleshooting with this client has determined that IT went rogue and restored a SQL database from their current production 2010 GP to the new GP 2016R2 test box that I had setup for them. So, this error message can also happen in that case as well, which explains why the table was missing. 🙂

    Like

      • Well, we do have a backup from after I did the test upgrade for them, but it’s BEFORE i did all the post-upgrade steps like entering reg keys and upgrading Extender, so it’s going to be some re-work.. Not much they can do at this point except re-do the work since their DBA did not turn on backups on this server yet, they usually only do backups on production servers.

        Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s