Open Forum

Expand all | Collapse all

Email alert once my GP get disconnected

  • 1.  Email alert once my GP get disconnected

    TOP CONTRIBUTOR
    Posted 10 days ago
    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.
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Email alert once my GP get disconnected

    GPUG ALL STAR
    Posted 9 days ago
    Hi @Cecile Dinh

    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Email alert once my GP get disconnected

    GOLD CONTRIBUTOR
    Posted 9 days ago
    Edited by David Joosten 9 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Email alert once my GP get disconnected

    SILVER CONTRIBUTOR
    Posted 9 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role