Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Cecile Dinh]

Hello!

We have been using eOne SmartPost to automatically post transactions at night and it requires active Dynamics GP session.

I need to find which file in Dynamics GP would tell me that Dynamics GP is disconnected so we can setup an email alert.
If Dynamics GP is completely close, Dynamics.exe is no longer there … but if the message “FP: Can’t close Table!” appears… the Dynamics.exe is still running in Task Manager.

Have you done this email alert before? What did you do?

Thank you!

——————————
Cecile Dinh
GP Admin at ARC (Airlines Reporting Corp.)
GPUG Chapter Leader – Washington, D. C.
——————————

[David Musgrave]

Hi

I don’t think there is any way to capture this.

It might be possible to look at the ACTIVITY table to get the spid for the user which is normally logged in and seeing if that spid is still in use at the SQL level.

That might identify that the connection has been lost.

To send an email, you would need a SQL job running to query this situation and send an email if it occurs.

Hope this helps

David?

——————————
David Musgrave MVP, GPUG All-Star

Managing Director
Winthrop Development Consultants

Perth, Western Australia

http://www.winthropdc.com
——————————
——————————————-

[David Joosten]

Hi Cecile,
We also use Smart Post. We have a slightly more complex solution, but there is a fairly simple job that can validate the the session for “most” cases.

A valid GP session must have an activity record and this needs to link to a valid SQL session ID. There will still be scenarios where this will not work, but at least it catches some of the problems.

You need to create a SQL job that runs as frequently as is required and executes a script similar to this: –

BEGIN

DECLARE @textUserID VARCHAR(50);

DECLARE @textMailList VARCHAR(255);

DECLARE @textMailMessage VARCHAR(MAX);

DECLARE @intRecCnt INT;

–Set the name of the user you want to monitor

SET @textUserID = ‘GPUserID’;

–This would be the mail recipient/s that you would like to send the mail to. Remember to separate recipients with a ;

SET @textMailList = ‘someone@somedomain.com’

–This is the body of the message you would like to send.

SET @textMailMessage = ‘The DYNAMICS session for ‘ + @textUserID + ‘ is no longer valid. Please log the user out and back in to restore the session.’;

–This query will check if there is a complete chain from the activity record through to the SQL sessions to ensure that the session is still valid. If the record count comes back as a zero, then there is no longer a valid session for this user id.

IF (

SELECT

COUNT(*)

FROM DYNAMICS..ACTIVITY AS ACT

INNER JOIN tempdb..DEX_SESSION AS SES

ON (ACT.SQLSESID = SES.session_id)

INNER JOIN sys.sysprocesses AS PRC

ON (SES.sqlsvr_spid = PRC.spid)

WHERE

(ACT.USERID = @textUserID)

) <= 0

BEGIN

–Send a mail to warn that the session is disconnected

–This needs to be setup on the SQL server for sending mail. The profile name needs to be changed to the correct profile

EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = ‘SQL_profile_Name’

,@recipients = @textMailList

,@body = @textMailMessage

,@body_format = ‘text’

,@subject = ‘SQL Automated Message’;

END

END;

Review the fields that need to be changed. You may need to chat with the DBA around the SQL mail.

If there is anything extra, you can reach out.

Regards

——————————
David Joosten
Technical Manager – CTO
Premier FMCG (Pty) Ltd
Midrand
——————————
——————————————-

[John Kirsch]

Hi Cecile – I’ve done something similar with SmartConnect (but not SmartPost).  For SmartConnect I setup a SQL job to check either the logging or else the record tables and if I don’t see activity within a certain period of time I send an email alert.  For example, if I’ve seen no SmartConnect activity within the past 30 minutes I start sending email alerts so that I can check the server or service for any issues.  If SmartPost has similar logging, that may work for you.

——————————
John Kirsch
Director of IT
Global Optics Inc.
Green Bay WI
——————————
——————————————-