Review SQL Server Instance Configuration

SELECT  *
FROM    sys.configurations
ORDER BY name ;
GO

How to Clear Disconnected Users from Dynamics GP

 

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)

Reset Dynamics GP System Password

 

 

1.  In SQL Management Studio, run this script to remove the existing password.

UPDATE DYNAMICS.SY02400
SET PASSWORD = 0x0202020202020202020202020202020

2.  In GP, reset the system password.

Dynamics GP 2015 Important Notes

 

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

https://community.dynamics.com/gp/f/32/t/134557

Script to Obtain a List of Dynamics Company IDs

 

select CMPANYID,CMPNYNAM,INTERID from Dynamics..SY01500

Script to List the Compatibility Levels of Databases

 

 

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

Enable CLR on SQL Server

 

To enable CLR on SQL Server run this script:

 

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Dynamics GP Get List of DB Names and Companies

 

 

SELECT 

INTERID 'Database',

CMPNYNAM 'Company_Name'

FROM DYNAMICS..SY01500

Clicking “Add” in Part Master No Update

 

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.

List the Recovery Model of All DB on SQL Server

/* Code to List all Recovery Models */

select [name], DATABASEPROPERTYEX([name],'recovery')
from sysdatabases
where name not in ('master','model','tempdb','msdb')

Errors on New Install of MAX things to check

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.

Quick SQL Server Version List

 

 

http://sqlserverbuilds.blogspot.com/

Dynamics GP Upgrade Paths Concise Reference

 

Good link to review the possible paths:

http://waqasb.blogspot.com/2014/12/dynamics-gp-2015-upgrade-process-paths.html

Additional Date Functions

 

 

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 Calculations

 

/* 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'

Management Report Setup Important

After CU 11 you need to:
Go to Microsoft Dynamics GP > Tools > Setup > Company > Company.
Click on “Options” to open the “Company Setup Options” window There is a separate section for management reporter integration with Dynamics GP when the data mart provider is used. Make sure these are checked for that the data mart imports the company.

Note that you need to enable Change Tracking for all DBs and the Dynamics DB as well. 

Resetting the QuickBooks FIM Integration for Exact MAX

1) First, I would remove the integrated applications in QB (Edit--Preferences--Integrated applications--Company tab). Highlight the two and remove.
2) Next, I would delete the ExactRM user in QB
3) Then remove the QBFC 11 from Control Panel--Uninstall Program
4) Rename the ExactMAXQB.xml.
5) Make sure all users are out of MAX and QB.
6) Go to Finance--Financial Integration--Options--Integration Setup. Click on config editor button
7) Make a print screen of the current integration setup and then remove it.
8) Add a new integration. Make sure you select the correct version of QB.
9) Follow the wizard to complete the integration setup.

Clearing up Posting in Exact MAX

1. To clear up the Invoice Edit list - Set INVCE_27 to “Y” or …

a. Stop shipping

b. Have them post all sales orders that they want to actually go through

c. Turn off the integration with Great Plains

d. Post all remaining orders

e. Turn on the integration with Great Plains

f. Resume shipping

2. To remove all unposted PO receipts - Done

a. Using SQL, change the value of the MARK_55 field on all PO Receipt records with a transaction date prior to (date) and a value of ‘N’ to ‘P’

3. To close Completed Shop orders

a. Close all the shop orders

b. Stop all Max transactions

c. Run the From/To report selecting their standard options

d. Using SQL, go into the order master and change all shop orders with a status of ‘4’ to ‘5’

e. Rerun the From/To report selecting their standard options again

f. Allow Max transactions to continue

Upgrades form SQL 2005 to Later Versions for Exact MAX

When upgrading MAX databases from SQL 2005 be sure to change the legacy setting of TORN_PAGE to CHECKSUM in the PAGE_VERIFY setting of your MAX database. 

Use of Parameterization on SQL Database for Exact MAX

 

In some cases setting the database Parameterization setting may increase performance of MRP Explosion and other sizable batch processes. Testing this in your test environment is definitely recommended. Change this setting under options in your SQL database and benchmark the change in processing time.