This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
This Quick Tip comes to you courtesy of an idea from my friend, Robert Cavill.
On a Microsoft Dynamics GP system where Activity Tracking is being used, it needs to be activated for each User ID/Company ID that you want it enabled for. In most cases, you would want the Activity Tracking enabled for all valid User ID and Company ID combinations.
What we have seen many times is that when new users or new companies are created in the system or user company access settings are changed, the settings in Activity Tracking Setup are overlooked and not updated to activate the tracking. Meaning that Activity Tracking is not capturing all the desired events for all users in all companies.
If you want to ensure Activity Tracking is enabled for all Users and Companies automatically, you can create a SQL Agent job to run the following code on a regular basis:
update SY60100 set TRKUSER = 1 where TRKUSER = 0 print 'Changed : ' + str ( @@ROWCOUNT )
You could also have a trigger on DYNAMICS.dbo.SY60100 table which will update the Activity Tracking flag automatically when the record in the table is created or updated. The code for the trigger is below and also attached to this post.
SQL Trigger Script
-- Trigger to make sure Activity Tracking is enabled USE DYNAMICS GO IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SY60100_INSERTUPDATE_ActivityTracking]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1 ) DROP TRIGGER [dbo].[SY60100_INSERTUPDATE_ActivityTracking] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE TRIGGER [SY60100_INSERTUPDATE_ActivityTracking] ON [dbo].[SY60100] FOR INSERT ,UPDATE NOT FOR REPLICATION AS DECLARE @RowCount INT SELECT @RowCount = count(*) FROM inserted WHERE EXISTS ( SELECT * FROM SY60100 A WHERE A.USERID = inserted.USERID AND A.CMPANYID = inserted.CMPANYID AND A.TRKUSER <> 1 ) SET NOCOUNT ON BEGIN UPDATE A SET A.TRKUSER = 1 FROM SY60100 A JOIN inserted I ON A.USERID = I.USERID AND A.CMPANYID = I.CMPANYID WHERE I.TRKUSER <> 1 DECLARE @logmsg VARCHAR(500) IF @@ROwCount <> @RowCount BEGIN SELECT TOP 1 @logmsg = USERID + '(' + STR(CMPANYID) + ')' FROM inserted SELECT @logmsg = 'Trigger ' + db_name() + '.SY60100_INSERTUPDATE_ActivityTracking (' + SYSTEM_USER + '): ' + @logmsg + ' may not have updated SY60100 table correctly' EXEC master.dbo.xp_logevent 60011 ,@logmsg ,Error END ELSE IF @RowCount > 0 BEGIN SELECT TOP 1 @logmsg = USERID + '(' + STR(CMPANYID) + ')' FROM inserted SELECT @logmsg = 'Trigger ' + db_name() + '.SY60100_INSERTUPDATE_ActivityTracking (' + SYSTEM_USER + '): ' + @logmsg + ' updated Activity Tracking' EXEC master.dbo.xp_logevent 60012 ,@logmsg ,Informational END END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Hope this is handy.
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.