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

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

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