delete from DYNAMICS..ACTIVITY
where USERID not in
(select loginame from master..sysprocesses)
delete from tempdb..DEX_SESSION
where session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK
where session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800
where USERID not in
(select USERID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801
where USERID not in
(select USERID from DYNAMICS..ACTIVITY)
1. In SQL Management Studio, run this script to remove the existing password.
UPDATE DYNAMICS.SY02400
SET PASSWORD = 0x02020202020202020202020202020202. In GP, reset the system password.
Latest Service Pack
At this writing it is very important when upgrading to Dynamics GP 2015 that you are on the current service pack/hotfix release. There are known issues that were resolved.
Prior Version data in Tables
Use the reference below to untangle the problems of prior versions in the tables
select CMPANYID,CMPNYNAM,INTERID from Dynamics..SY01500
select name, compatibility_level , version_name =
CASE compatibility_level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END
from sys.databases
To enable CLR on SQL Server run this script:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
SELECT
INTERID 'Database',
CMPNYNAM 'Company_Name'
FROM DYNAMICS..SY01500
If you find that you click any item in MAX and you get no response as in adding a part or if you try to update a part and receive an DB Error 4. This could be cause by SQL Server CLR not being enabled. Check your SQL Server or ask your DB admin if CLR has been disabled. In secure locations often DB Admin will opt to not allow CLR due to the fact that CLR can gain access to the network / servers and other critical resources which open the area to attack.
/* Code to List all Recovery Models */
select [name], DATABASEPROPERTYEX([name],'recovery')
from sysdatabases
where name not in ('master','model','tempdb','msdb')
1) Make sure that you have your anti-virus turned off.
2) Check your User Account control settings and make sure they are off
3) Make sure that .Net 3.51 is installed.
4) A new fresh install can also solve any issue that might be present due to other apps installed after MAX has been installed.
5) When doing batch updates be sure to stop data collection or other 3rd party apps that might be running or that might be using MAX files in the client folders.
6) Make sue there are not any restrictive permissions settings that might be preventing MAX from running. Set Modify for Users.
7) Install as local Admin or admin on workstation.
8) Group policies that might be interfering with installation or the lay-in update of files for MAX.
This is not necessarily a complete list but should be helpful in debugging most issues.
Good link to review the possible paths:
http://waqasb.blogspot.com/2014/12/dynamics-gp-2015-upgrade-process-paths.html
First Date of last month
=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))
=Today.AddDays(1- Today.Day).AddMonths(-1)
Last date of last month
=DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))
=Today.AddDays(-1.0 * Today.Day)
First date of current month
=DateSerial(Year(Now()), Month(Now()), 1)
=Today.AddDays(1 - Today.Day)
Last date of current month
=DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Now()), Month(Now()), 1))))
=Today.AddDays(-1 * Today.Day).AddMonths(1)
Yesterday's date
=DateAdd("d", -1, Today)
=Today.AddDays(-1)
Tomorrow's date
=DateAdd("d", 1, Today)
=Today.AddDays(1)
* Monday of current week
=DateAdd("d", 2 - DatePart("w",Today) , Today)
=Today.AddDays(1- Today.DayOfWeek)
* Friday current week
=DateAdd("d", 6 - DatePart("w",Today) , Today)
=Today.AddDays(5- Today.DayOfWeek)
First day of current year
=DateSerial(Year(Now()), 1, 1)
=Today.AddDays(1- Today.DayOfYear)
Last day of current year
=DateSerial(Year(Now()), 12, 31)
=Today.AddDays(-1 * Today.DayOfYear).AddYears(1)
First day of current quarter
=DateSerial(Year(Now()), (3*DatePart("q",Now()))-2, 1)
=Today.AddDays(1- Today.DayOfYear).AddMonths((3 * (((Today.Month-1) \ 3) + 1) ) -3)
Last day of current quarter
=DateAdd("d",-1,DateAdd("q",1,DateSerial(Year(Now()), (3*DatePart("q",Now()))-2, 1)))
=Today.AddDays(1- Today.DayOfYear).AddMonths((3 * (((Today.Month-1) \ 3) + 1) )).AddDays(-1)
/* Handy Date Stuff */
----Today
SELECT GETDATE() 'Today'
--Full Day
SELECT CAST(GETDATE() as date) AS DT
----Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
---- Previous Day
DATEADD(day, DATEDIFF(day, 0, yourDate), 0)
----First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
----Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
----First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
----Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
----First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
----Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
----Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
----First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
----Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
----First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
----Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'
