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.

Multi-Column Report for Serial Numbers

I recently had to create a report that needed adjacent columns of serial numbers much like columns in a Word document. I pored over several advices but in the end discretely defining the number of serial numbers per column worked out best.
I used the code below to get a row number and list them and then created separate data sets in the sub-report which was inserted in the body of the main report.

SQL Code for Data Sets
select s1.Num as R1, s1.SERIAL_71 as S1
from
(
SELECT top 100 percent SERIAL_71, ordnum_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @Order)
order by SERIAL_71
) AS S1
where s1.Num <= 10
select s1.Num as R2, s1.SERIAL_71 as S2
from
(
SELECT top 100 percent SERIAL_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @Order)
order by SERIAL_71
) AS S1
where s1.Num >= 11 and s1.Num <= 20
select s1.Num as R3, s1.SERIAL_71 as S3
from
(
SELECT top 100 percent SERIAL_71,
ROW_NUMBER () OVER (order by serial_71) as Num
FROM            Serial_Master
WHERE        (ORDNUM_71 = @OrdNum)
order by SERIAL_71
) AS S1
where s1.Num >= 21 and s1.Num <= 30

Linking the Order Master to the Sales Master Table in ExactMAX for AutoGenerated Master Scheduled Orders

 

SELECT Order_Master.ORDNUM_10, CM.NAME_23, CM.COMNT1_23, CM.COMNT2_23, CM.UDFREF_23
FROM
Order_Master
INNER JOIN
SO_Master SM
on LEFT(CUSORD_10,8) = SM.ORDNUM_27
inner join Customer_Master CM on SM.CUSTID_27 = CM.CUSTID_23
WHERE – Add anything here that you need to select the proper record set.

Command Line Script to Open Ports for SQL Server

 

 

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022 
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo =========  Misc Applications  ==============

@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE
@echo Enabling port for MAX License Service
netsh firewall set portopening TCP 8080 "ExactRMService"

Dynamics GP 2015 Upgrade System Requirements

If you are upgrading to Dynamics GP 2015 make sure that you are at least on SQL Server 2008 R2 and running Windows Server 2012.
Vince Stefanetti

How to Clear Stuck Users in ExactMAX 5

 

Always use caution when editing live tables in MAX.

On occasion a hard closure of MAX or a dropped connection can cause MAX to retain the user in the ERMUD table which is in the EXACTMAX master database. You can query the ERMUD table and review the details to see which users are stuck.

MachineName    ExactRMUser CompanyName   AppID
STEF277301-02 MANAGER     MAX Sample DB 8
STEF277301-02 MANAGER     MAX Sample DB 512

MAX Also has an associated App ID.

You can query the table :

SELECT * FROM EXACTMAX..ERMUD

You can delete stuck users by executing:

DELETE FROM ERMUD WHERE ExactRMUser = ‘Type the Login Name’ and CompanyName = ‘Type the Company Name’

 

 

As well you can use this handy Script to check to see who is in what module:

-- Get the Logged in Users and the Core Modules they are in

/* 10/2/2014 - Authored: Vincent Stefanetti */

/* Notes: System Manager = 512, Advanced Shipping = None, SOP = 1, Warranty = 2048, BOM = 4, ECO = None, Feature Opton = None, Advanced Shipping = None,

INV = 8, MPS = 128, MRP = 16, PUR = 2, pim = 8192, labor tracking = 16384, SFC = 64, Costing = 256, FIM = 32, EDI = 32768, TnxLoad = 1024

Archive = None, User Designed Fields = None

*/

USE ExactMAX -- OR enter your DB name here

select SQ.hostname as sql_hostname, MX.MachineName, MX.ExactRMUser, MX.CompanyName, MX.Module

from [master].[sys].[sysprocesses] AS sq

LEFT OUTER JOIN

(SELECT MachineName, ExactRMUser, CompanyName,

CASE

WHEN AppID = '512' THEN 'System Manager'

WHEN AppID = '1' THEN 'Sales Order Processing'

WHEN AppID = '2' THEN 'Purchasing'

WHEN AppID = '4' THEN 'Bills of Materials'

WHEN AppID = '8' THEN 'Inventory Control'

WHEN AppID = '16' THEN 'MRP'

WHEN AppID = '32' THEN 'Financial Integration'

WHEN AppID = '64' THEN 'Shop Floor Control'

WHEN AppID = '128' THEN 'Master Scheduling'

WHEN AppID = '256' THEN 'Costing'

WHEN AppID = '1024' THEN 'Transaction Load'

WHEN AppID = '2048' THEN 'Warranty Tracking'

WHEN AppID = '8192' THEN 'Physical Inventory'

WHEN AppID = '16384' THEN 'Labor Tracking'

WHEN AppID = '32768' THEN 'Physical Inventory'

else ''

END AS 'Module'

FROM ERMUD) AS MX

ON sq.hostname = mx.MachineName

where hostname <> ''

group by SQ.hostname, MX.MachineName, MX.ExactRMUser, MX.CompanyName, MX.Module

Vince Stefanetti

Senior Consultant

ID:12248195

Exact Software North America - Exact MAX

Setting Up SQL Database Email with a Google Mail Account

 

If you need to use a Google Account for setting up email notifications on SQLServer make sure you use the 2 step security method.

1. Login to the Google Account
2. Go to Account Settings for the Account – Upper Right when you log in.
3. Select Sign-in and Security

 

4. Then – 2 step

5. Then perform the actions needed to do the verification by phone or text.

6. Next, Add a name for the app “SQL Database Email” etc.

7. Grab the password

8. Configure the DB email account and place the password there for the account authentication (see below)

Hope this helps!

Collation Sequence

 

This defines how SQL Server will sort Case and Accents ( glyphs to determine pronunciation) and Kana (Japanese syllabic writing). Capitalization and the Diacritical ( Dia - through and krinein “to separate”) marks are included in sort considerations by SQL Server. Selecting the collation sequence on setup provide the Instance Collation however the collation can also be changed at the DB and Column level as well.

Key Words:

Collate > https://www.google.com/?gws_rd=ssl#q=define+collate and the derivation meaning “brought together”.

Code Page > https://en.wikipedia.org/wiki/Code_page

Vince Stefanetti

What does the Double Period Mean in T-SQL Script?

When a double period is used, as in:

 

ExactMAXSampleDB..Part_Master

 

This means the schema is inferred to be the default schema of the current login.

 

In most cases this will be .dbo. So instead of having to provide it one could use the shortcut of the “..” or more correctly the default for that user which is more right due to the fact that the author of the script may not know which default is in use but may know the table and databases name that may well be standard as determined by the software app deployed.

 

Vince Stefanetti

Senior Consultant

                                              ID:12248195