Dynamics GP - Custom Activity Tracking Reports - Auto-Enable Activity Tracking

Activity Tracking Log Table is

select * from dynamics.dbo.sy05000

Scheduled SQL Job to enable activity tracking for all users

update Dynamics.dbo.SY60100
set TRKUSER = 1 
where TRKUSER = 0

print 'Changed : ' + str ( @@ROWCOUNT )

Original Post here

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/11/07/quick-tip-making-sure-activity-tracking-is-enabled-for-all-users.aspx

View to only show the last 2 years of activity log

---------------------------------------------

/****** Object:  View [dbo].[BI_ActivityLog]    Script Date: 3/8/2018 10:27:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ActivityLog]
AS
SELECT        INDEX1, PRODNAME, CMPNYNAM, USERID, INQYTYPE, DATE1, TIME1, SECDESC, DEX_ROW_ID, (YEAR(GETDATE()) * 12 + MONTH(GETDATE())) - (YEAR(DATE1) * 12 + MONTH(DATE1)) AS MthsOld
FROM            dbo.SY05000
WHERE        ((YEAR(GETDATE()) * 12 + MONTH(GETDATE())) - (YEAR(DATE1) * 12 + MONTH(DATE1)) <= 24)

GO
-------------------------------------------------------------

Codes

InqTypes
1=user failed login
2=user logging in
3=user accessed form
4=user denied login
5=user accessed report
6=user denied report
7=user added master record
8=user modified master record
9=user deleted master record
10=user used process server
11=user added utility record
12=user accessed file maintenance
13=user denied file
14=user accessed routines
15=user logged out
16=user went into modifier
17=user went into report writer
18=user added transaction record
19=user deleted transaction record
20=user modified transaction record
21=user added setup record
22=user deleted setup record
23=user modified setup record

Index Series Numbers:
2=Financial 
3=Sales 
4=Purchasing 
5=Inventory 
6=Payroll 
7=Project

Dynamics GP - SQL - Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim

To identify deadlocks, run this script.
However, you may have to run this before you get the error message, as the deadlock will have already been removed by the time you see the error message.
Attempt to replicate the lock, and run the script to see what's being locked.

--------------------------------------------------------------------------------------------------

select

 db_name(sp.dbid) as database_name, d.text as sql_text, spid, blocked, cmd, sp.waittime, hostname, program_name,loginame, *

 from master.sys.sysprocesses sp OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

 where sp.blocked <> 0

 union all

 select db_name(sp.dbid) as database_name, d.text as sql_text, spid, blocked, cmd, sp.waittime, hostname, program_name,sp.loginame, *

 from master.sys.sysprocesses sp OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

 where blocked = 0 and spid in (select blocked from master.sys.sysprocesses where blocked <> 0)

 order by sp.waittime desc

-------------------------------------------------------------------------------------------------------------------

Original post

http://social.msdn.microsoft.com/Forums/en-US/sqldatamining/thread/4141a761-e7c7-467b-bf24-c1ae85023964

Dynamics GP Manufacturing - Data Collection - System settings have not been defined

• Go to Manufacturing>Setup>System Defaults>WIP
• Tick at least one option
• Click OK

SQL - How to add Thousands Separator on numbers within SQL

select replace(convert(varchar,convert(Money, @MyValue),1),'.00','')


original post here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/49e7d563-5056-478a-921d-ec56ae3162c8

Dynamics GP - Incorrect Functional Currency Index - Incorrect Currency Indexes

Whenever you combine companies that have had separate installs and separate dynamics databases, chances are, the currency indexes across the companies are different. Even if the currency name is the same, GP uses the indexes for everything.
You will realize this problem if you are trying to install Management Reporter and you get the error

• You must set up a functional currency for the following companies before the integration can continue

Check indexes in the following tables to ensure the currency indexes in the company match the currency indexes in the dynamics db.

• MC40000 in company
• MC40200 in Dynamics

Update the MC40000 index to match the MC40200 as long as the currency id's are the same

Dynamics GP eOne Extender Standard - Microsoft Dynamics GP Extender will be disabled until it is initialized by the system administrator

1. Install the latest version of extender standard, this issue has been resolved 
2. You may need to rebuild your views manually within extender

How to Open a chm help files - Navigation to the webpage was cancelled

1. Right click chm file
2. Go to properties
3. click Unblock

Original post here
http://social.msdn.microsoft.com/Forums/en-US/devdocs/thread/ef2b95e0-4198-4e1a-b876-314a23348b5e