With SmartPost, one of the requirements is that you have an active Microsoft Dynamics GP session. You may have the need to ensure that GP is open at when you have SmartPost scheduled to post your transactions.

While the ACTIVITY table in the DYNAMICS database will tell you if a user is logged into Dynamics GP, you may find that the connection to SQL is lost. For this, we have to look at the Sysprocesses table in SQL.

A SQL select statement like the following will return the count of records for a specific user. If it is 0, then the user doesn’t have a valid session.

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 = ‘UserId’)

You can create a SQL Job and use Database Mail to notify you if the script returns 0 meaning it didn’t find a valid active session for GP with the login you are running SmartPost under.

Here is a sample of a script you can use with a SQL Job and Database Mail to do this.  You will need to review the fields that need to be changed based on the comments in the script.  This script is courtesy of David Joosten from Premier FMCG (Pty) Ltd.

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;

As always, it is recommended that you make sure you review any scripts provided here and ensure they will be what you need before setting them up in your live environment.