Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Dynamics GP Add in “Cannot load file or assembly Microsoft.Lync.Utilities”

GP Add in raised exception

After applying GP2010 SP3 my GP add in started raising an exception on windows forms that used Entity Framework (EF). The error reported was

System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.Lync.Utilities, Version=4.0.0.0…"

…but I don’t use Lync…

But I know Dynamics GP does have integration with Lync. It had me puzzled for a while as I wondered why my form was causing this assembly to be required and attempting to load it. Eventually the penny dropped after studying the stack trace. The stack trace was mentioning about System.Reflection and System.Data.Metadata.Edm.DefaultAssemblyResolver.GetAllDiscoverableAssemblies().  I guessed there was some dynamic loading going on and got mislead reading up on MEF, thinking perhaps GP implemented it under the covers. I then hit lucky on Google with Craig Suntz’s blog that explained my issue. It is down to Entity Framework connection strings.

Entity Framework Connection Strings

In my EF connection string I was building my connection string like this from the SQL connection string.

Dim entityBuilder As New EntityClient.EntityConnectionStringBuilder()
entityBuilder.Provider = "System.Data.SqlClient"
entityBuilder.ProviderConnectionString = sqlBuilder.ToString
entityBuilder.Metadata = _
"res://*/Inventory.DAL.InventoryModel.csdl|res://*/Inventory.DAL.InventoryModel.ssdl|res://*/Inventory.DAL.InventoryModel.msl"
Return entityBuilder.ToString()

Notice the “res://*” bit, the Metadata reference. The files are embedded into the assembly as resources and this Metadata property is the pointer to them. Specifying * instructs a reflective search through all assemblies (even if not yet loaded)  that are referenced by the application, looking for the resources.

I guess that as Lync SDK was referenced by GP 2010 my attempt to create the connection was causing the Lync assembly to be loaded in order to check it for the meta file resource. The consequence of this is that we got an application exception as that assembly does not exist on the machines in our organisation.

Solution

The solution is to explicitly specify the assembly name that holds the resource as shown in the next code snippet. Making this exact reference is more efficient by it preventing unnecessary loading of assemblies and thus preventing the error . This is interesting as I’d experienced a performance hit on these windows vs ones not using EF. I assume some of that was down to this attempt to load all the assemblies.

Dim entityBuilder As New EntityClient.EntityConnectionStringBuilder()
entityBuilder.Provider = "System.Data.SqlClient"
entityBuilder.ProviderConnectionString = sqlBuilder.ToString
entityBuilder.Metadata = "res://GP AddIn GUI Library/Inventory.DAL.InventoryModel.csdl|res://GP AddIn GUI Library/Inventory.DAL.InventoryModel.ssdl|res://GP AddIn GUI Library/Inventory.DAL.InventoryModel.msl"
Return entityBuilder.ToString()

Solved

After recompiling and then deploying to the GP add in folder, my problem went away. I suspect if I spent some more time on this that the Lync assemblies are stuffed into the Global Assembly Cache by the SP3 installer, as we do copy deployments to the client machines we will be missing those assemblies. Hence not everyone may suffer this issue when using add ins if you run the full installer. However this guesswork needs researching and I’ve not got time right now to investigate.

About MEF (Microsoft Extensibility Framework)

In my Google search of the issue, it looks like you can get issues around MEF with dynamically loading assemblies, so for those of you finding this post through Google and not using Dynamics GP, then that might be a term to try on Google for a solution in your scenario.

Below is the original error

Raised by by Add-in for those still with an interest…

clip_image002

Solution to Excel Automation VB.NET/C# error running as a service 0x800A03EC - Microsoft.Office.Interop.Excel.Workbooks.Open

Overview of issue

When automating excel using a non-interactive session as a service or windows task scheduler it failed with an exception System.Runtime.InteropServices.COMException (0x800A03EC).

This did not happen when the same task was ran from the command prompt as the same user. The stack trace showed it was when we tried to open the excel sheet Microsoft.Office.Interop.Excel.Workbooks.Open

Automating Excel like this is strictly warned against by Microsoft as it is a pain. There are so many possible issues you have to work through.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behaviour and/or deadlock when Office is run in this environment.

Microsoft strongly recommends that developers find alternatives to Automation of Office if they need to develop server-side solutions. Because of the limitations to Office's design, changes to Office configuration are not enough to resolve all issues. Microsoft strongly recommends a number of alternatives that do not require Office to be installed server-side, and that can perform most common tasks more efficiently and more quickly than Automation. Before you involve Office as a server-side component in your project, consider alternatives.
Considerations for server-side Automation of Office

However you may manage despite all these strong words to not do it. In our case we had some complex excel documents with complicated formatting that was populated through a datasource that was latterly removed before saving. I didn’t fancy trying that in OpenXML, also we were required to produce old xls type files for output.

More...

Inactivity Auto Logout for Dynamics GP

Updated 18th May 2014 – Amended to improve stability

Concurrent licences

Dynamics GP is licenced on a concurrent user model. Hitting max user count limit, yet having some of the user sessions idle for hours at the same time as the user is AFK (Away From Keyboard) is a problem for many installations. Asking politely for users to logout is not enough as users cannot always anticipate when they will be unexpectedly pulled away from their work, sometimes for extended times leaving GP logged in hogging a valuable licence.

image

A colleague thought one approach to address the issues may be to allow the users to click a button, see on the above screen his idea, in order to see the users that have been idle too long. Thus the users themselves get to do the leg work of phoning up those licence hogs to get them to logout. I have written this version, by hooking into the form that pops up for maximum user count. A better way to tackle this in my view is with an inactivity auto logout approach.

User licences are so expensive, so having them laying idle is not acceptable so I tried looking for an auto logout function in GP. Alas it is not there. Rockton Software have a GP Toolbox, which has a feature rich auto logout, go check it out. This looked to be part of a larger toolkit that I didn’t require.  Thus I rolled up my sleeves and fired up Visual Studio and Google.

image

Using SQL

I was really only targeting the occasional users, not so much core users and didn’t want to force people out during dangerous activities like posting or editing records. I toyed with using a SQL script and SQL Job to kill off users from the activity table. I checked first to see if they had any locks on resources that would concern us though the locks tables, thus not affecting users editing or posting. Although this was OK, I wasn’t happy enough with the way it worked. I could end up clearing up the data corruption if it didn’t work! I then thought I could try to use a Visual Studio Addin to log these users out, rather than rudely killing them off behind the scenes. This could be done based on the same script to decide when it should happen.

See idle times from the second SQL script on this page:http://dynamicsgpblogster.blogspot.co.uk/2009/04/retrieving-dynamics-gp-user-idle-time.html

Using Visual Studio Addin

I set up a system.Timer and fired it regularly to check the SQL script and then used a method I found to log the users out. http://mohdaoud.blogspot.co.uk/2008/12/programmatically-closing-for-dynamics_3512.html

I had found a way to do the logout and it worked like a charm. It works by simulating the user going to file>>exit. It does this by writing out a GP macro to a temp file that is subsequently ran. It meant that GP retained control in exactly the same manner as if a user had attempted to exit, thus would say “do you want to save changes?” and prevent logout, should the user be in the middle of anything risky. This soft logout is quite adequate for what I was trying to achieve, shaving a few more users out the system with low risk of causing any issues.

The problem was then that when I tried testing I found that normal users can’t access the system tables I needed (SYSPROCESSES) without granting extra permissions (VIEW SERVER STATE). I didn’t want to grant this. So it had been fine running as a SQL job under an elevated permission set, but not as end users.

Hooking into Global Procedures

Thus I started looking at how I could detect a user actively doing it in GP. I went looking at putting an event handler in the client GP application. I hit upon the global security procedure that is checked when you do most anything in GP. By using this procedure to update a last touched date I could pretty much check for user activity in the GP client though the add-in, much better as self contained functionality.

Googling that security global procedure, I then stumbled on a post by David Musgrave about how they had hooked onto that and some other procedures too when making the Rockton tool for inactivity monitoring. http://msgroups.net/microsoft.public.greatplains/auto-logout-of-dynamics-gp/590960 If only I’d seen this at the start of my hunt. I shamelessly added those extra procedures on top of the security procedure and it improved the behaviour, now virtually never get a auto logout when it shouldn’t have happened.

I prompt the user for fifty seconds with a countdown windows form providing an opportunity to cancel the auto logout. I also call out to a SQL stored procedure before initiating the logout (not shown here for simplicity). That stored procedure checks if we are within x% of the max user limit, out of hours or one of a group of users who are excluded from the functionality due to the nature of the work they do (think checklinks maintenance user etc). Then decides if the auto logout should be initiated. There is no point logging people out unless we are running out of user licences, and everyone should logout at the end of day.

Source Code Snippets

I have been running this rough version now on my copy of GP for testing. It just needs the rough edges smoothing, extra error checking and naming variables better. Also need to pull the time out settings from a SQL table where they can be maintained.

Following code shows the registration of the global procedure events we are interested in (see the dexterity programmers guide for more explanation on what they do). We then have a class that simply holds an instance of the timer inactivity class and prods it when we get activity to update the time of last activity. In that timer inactivity class we have a system timer that checks regularly to see if the last time the class was prodded for activity was over the threshold number of mins. If so it then first off the countdown form. This form allows users to cancel the auto logout.

 

Public Class UserInactivityForm
    Dim SecondsToLogout As Short = 50
    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        lblCount.Text = SecondsToLogout.ToString
        SecondsToLogout = SecondsToLogout - CShort(1)
        If SecondsToLogout = 0 Then Me.DialogResult = Windows.Forms.DialogResult.OK

    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        DialogResult = Windows.Forms.DialogResult.Cancel
    End Sub

    Private Sub UserInactivityForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Timer1.Interval = 1000
        Timer1.Enabled = True
        FlashWindow.Flash(Me)
    End Sub
End Class

 

Imports System.Windows.Forms

Public Class InactivityTimer

    Public WithEvents oApplicaitonExitTimer As Timers.Timer

    Private m_LastActivityTime As DateTime
    Public Property LastActivityTime() As DateTime
        Get
            Return m_LastActivityTime
        End Get
        Set(ByVal value As DateTime)
            m_LastActivityTime = value
        End Set
    End Property

    Private m_TimeOutTimeMins As Short = 1
    Public Property TimeOutTimeMins() As Short
        Get
            Return m_TimeOutTimeMins
        End Get
        Set(ByVal value As Short)
            m_TimeOutTimeMins = value
        End Set
    End Property

    Private m_Enabled As Boolean
    Public Property Enabled() As Boolean
        Get
            Return m_Enabled
        End Get
        Set(ByVal value As Boolean)
            m_Enabled = value
            If Not IsNothing(oApplicaitonExitTimer) Then
                oApplicaitonExitTimer.Enabled = value
                oApplicaitonExitTimer.Start()
            End If
        End Set
    End Property


    Public Sub Tick()
        Me.LastActivityTime = Now()
    End Sub

    Private o As UserInactivityForm


    Private Sub oApplicaitonExitTimer_Elapsed(sender As Object, e As System.Timers.ElapsedEventArgs) Handles oApplicaitonExitTimer.Elapsed
        'Checks to see if the current user has been idle too long and logs them out if so
        CheckUserTimeOut()
        'After first timeout check now check every 5mins
        'If oApplicaitonExitTimer.Interval > 300000 Then
        '    oApplicaitonExitTimer.Interval = 300000
        'End If
        'CurrentUser.CheckUserTimeOut()
    End Sub



    Public Sub TryToLogout()
        Try
            If IsNothing(o) Then o = New UserInactivityForm
            If o.ShowDialog() = Windows.Forms.DialogResult.OK Then
                'Creates a temp macro and executes it
                Dim CompilerApp As New Dynamics.Application
                Dim CompilerMessage As String = Nothing
                Dim CompilerError As Integer
                Dim Commands As String
                Commands = ""
                Commands = Commands & "local integer l_file_id; " & vbCrLf
                Commands = Commands & "local string pathname; " & vbCrLf
                Commands = Commands & "pathname = Path_GetForApp(1) + ""TEMP_LOGOUT.MAC""; " &
                vbCrLf
                Commands = Commands & "l_file_id = TextFile_Open(pathname, 0, 0); " &
                vbCrLf
                Commands = Commands & "TextFile_WriteLine(l_file_id, ""CommandExec form BuiLtin command cmdQuitApplication""); " & vbCrLf
                Commands = Commands & "TextFile_Close(l_file_id); " & vbCrLf
                Commands = Commands & "if File_Probe(pathname) then " & vbCrLf
                Commands = Commands & "  run macro pathname; " & vbCrLf
                Commands = Commands & "end if; " & vbCrLf
                ' Execute SanScript 
                CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
                If CompilerError <> 0 Then
                    MsgBox(CompilerMessage)
                End If
            Else
                o.Dispose()
                o = Nothing
            End If
        Catch ex As Exception
            Windows.Forms.MessageBox.Show(ex.ToString)
        End Try

    End Sub

    Private Sub CheckUserTimeOut()
        'If the last activity was longer than the timeout then try logout
        If DateDiff(DateInterval.Minute, Me.LastActivityTime, Now()) > TimeOutTimeMins Then
            TryToLogout()
        End If
    End Sub


    Public Sub New()
        oApplicaitonExitTimer = New Timers.Timer(60000)
        oApplicaitonExitTimer.AutoReset = True
        Me.oApplicaitonExitTimer.Enabled = False
    End Sub

End Class
'Handlers looking for activity for the inactivity monitor
AddHandler DynamicsGP.Procedures.AddSuccessfulLoginRecord.InvokeAfterOriginal, AddressOf oGlobalProcedures.AfterSucessfulLogin
AddHandler DynamicsGP.Procedures.Pathname.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresPathnameInvokeAferOriginal
AddHandler DynamicsGP.Procedures.SqlScriptPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlScriptPathInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.SqlPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlPathInvokeAfterOriginal

AddHandler DynamicsGP.Procedures.Security.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSecurityInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForNoteInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForRecordNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal
Imports System.Threading
Imports System.Security
Imports Microsoft.Dexterity.Bridge
Imports Microsoft.Dexterity.Applications
Imports System.Windows.Forms


Public Class GlobalProcedures
    Private oInactivityTimer As New InactivityTimer

    Public Sub DynamicsProceduresPathnameInvokeAferOriginal(sender As Object, e As DynamicsDictionary.PathnameProcedure.InvokeEventArgs)
        oInactivityTimer.Tick()
    End Sub

    Public Sub DynamicsProceduresSqlScriptPathInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.SqlScriptPathProcedure.InvokeEventArgs)
        oInactivityTimer.Tick()
    End Sub

    Public Sub DynamicsProceduresSqlPathInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.SqlPathProcedure.InvokeEventArgs)
        oInactivityTimer.Tick()
    End Sub

    Public Sub DynamicsProceduresSecurityInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.SecurityProcedure.InvokeEventArgs)
        oInactivityTimer.Tick()
    End Sub

    Public Sub DynamicsProceduresCheckForNoteInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.CheckForNoteProcedure.InvokeEventArgs)
        oInactivityTimer.Tick()
    End Sub

    Public Sub DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.CheckForRecordNoteProcedure.InvokeEventArgs)
        oInactivityTimer.Tick()
    End Sub

    Public Sub AfterSucessfulLogin(ByVal sender As Object, ByVal e As EventArgs)
        'Only allow auto logout after login...
        Try
            oInactivityTimer.Enabled = True

        Catch ex As Exception
            Windows.Forms.MessageBox.Show("Error on after open" & vbCrLf & ex.ToString, "Toolbar AfterSucessfulLogin", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

    End Sub

End Class

Enhanced Version

I got around to adding a SQL check that the user count is high and excludes and key users such as sa or DYNSA. The following stored procedure checks for “permission” to log the user out. You could group users in this SQL into groups each with their own thresholds for example.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tim Wappat
-- Create date: 3rd Sept 2013
-- Description: When a user session inactivity times out
-- determines if the user should be logged off
-- =============================================
CREATE PROCEDURE gpmods.SY_UserInactivityTimeOutCheck
@UserID CHAR (15)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserCountThreshold AS INT;
SET @UserCountThreshold = 72;
IF ((SELECT COUNT(*)
FROM dynamics..ACTIVITY) > @UserCountThreshold
AND (@USERID NOT IN ('sa', 'DYNSA')))
BEGIN
RETURN 1;
END
ELSE
BEGIN
RETURN 0;
END
END
GO
GRANT EXECUTE ON gpmods.SY_UserInactivityTimeOutCheck TO DYNGRP
GO
 
Global Procedures Class handles GP events and contains the inactivity timer
Imports System.Threading
Imports System.Security
Imports Microsoft.Dexterity.Bridge
Imports Microsoft.Dexterity.Applications
Imports System.Windows.Forms


Public Class GlobalProcedures
Private oInactivityTimer As New InactivityTimer(60000)
'60000 1 min

Public Sub DynamicsProceduresPathnameInvokeAferOriginal(sender As Object, _
e As DynamicsDictionary.PathnameProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try

End Sub

Public Sub DynamicsProceduresSqlScriptPathInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.SqlScriptPathProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub

Public Sub DynamicsProceduresSqlPathInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.SqlPathProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub

Public Sub DynamicsProceduresSecurityInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.SecurityProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub

Public Sub DynamicsProceduresCheckForNoteInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.CheckForNoteProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub

Public Sub DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.CheckForRecordNoteProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub

Public Sub New()
oInactivityTimer.Enabled = True
End Sub
End Class

InactivityTimer class fires (60 mins in this example) to check for inactivity and logs the user out if no activity since last check and after checking with the sql stored proc that we actually need to free some users up.

Imports System.Windows.Forms

Public Class InactivityTimer

Public WithEvents oApplicaitonExitTimer As Timers.Timer

Private m_LastActivityTime As DateTime
Public Property LastActivityTime() As DateTime
Get
SyncLock (oLockLastActivityTime)
Return m_LastActivityTime
End SyncLock

End Get
Set(ByVal value As DateTime)
SyncLock (oLockLastActivityTime)
m_LastActivityTime = value
End SyncLock
End Set
End Property

Private m_TimeOutTimeMins As Short = 60
Public Property TimeOutTimeMins() As Short
Get
Return m_TimeOutTimeMins
End Get
Set(ByVal value As Short)
m_TimeOutTimeMins = value
End Set
End Property

Private m_Enabled As Boolean
Public Property Enabled() As Boolean
Get
Return m_Enabled
End Get
Set(ByVal value As Boolean)
m_Enabled = value
If Not IsNothing(oApplicaitonExitTimer) Then
oApplicaitonExitTimer.Enabled = value
oApplicaitonExitTimer.Start()
End If
End Set
End Property

Private oLockLastActivityTime As New Object
Private oLockLogout As New Object

Public Sub Tick()
Me.LastActivityTime = Now()
End Sub

Private oUserInactivityForm As UserInactivityForm


Private Sub oApplicaitonExitTimer_Elapsed(sender As Object, _
e As System.Timers.ElapsedEventArgs) _
Handles oApplicaitonExitTimer.Elapsed
'Checks to see if the current user has been idle too long and logs them out if so
CheckUserTimeOut()
End Sub


Private isLoggingOut As Boolean = False

Public Sub TryToLogout()
Try
'Test and set pattern to ensure another thread is not updating this flag
If Not isLoggingOut Then
SyncLock (oLockLogout)
If Not isLoggingOut Then
isLoggingOut = True
End If
End SyncLock
'Whatever the case, this is now a clean read
If isLoggingOut Then
If IsNothing(oUserInactivityForm) Then oUserInactivityForm = _
New UserInactivityForm
If oUserInactivityForm.ShowDialog() = Windows.Forms.DialogResult.OK Then
'Creates a temp macro and executes it
Dim CompilerApp As New Dynamics.Application
Dim CompilerMessage As String = Nothing
Dim CompilerError As Integer
Dim Commands As String
Commands = ""
Commands = Commands & "local integer l_file_id; " & vbCrLf
Commands = Commands & "local string pathname; " & vbCrLf
Commands = Commands & "pathname = Path_GetForApp(1) + ""TEMP_LOGOUT.MAC""; " &
vbCrLf
Commands = Commands & "l_file_id = TextFile_Open(pathname, 0, 0); " &
vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, ""CommandExec form BuiLtin command cmdQuitApplication""); " & vbCrLf
Commands = Commands & "TextFile_Close(l_file_id); " & vbCrLf
Commands = Commands & "if File_Probe(pathname) then " & vbCrLf
Commands = Commands & " run macro pathname; " & vbCrLf
Commands = Commands & "end if; " & vbCrLf
' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox(CompilerMessage)
End If
oUserInactivityForm.Dispose()
oUserInactivityForm = Nothing
Else
Tick()
oUserInactivityForm.Dispose()
oUserInactivityForm = Nothing
SyncLock (oLockLogout)
isLoggingOut = False
End SyncLock
End If
End If
End If
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try

End Sub

Private Sub CheckUserTimeOut()
'If the last activity was longer than the timeout then try logout
If DateDiff(DateInterval.Minute, Me.LastActivityTime, Now()) > TimeOutTimeMins And Not isLoggingOut Then
If CheckDBForPermissionToLogout() Then
TryToLogout()
End If
End If
End Sub

Private Function CheckDBForPermissionToLogout() As Boolean
'Allows a stored to procedure to look at the activity table and decide if the user needs to be logged out,
' i.e. only log user out when activity table is nearing the user limit
' also prevents sa and some other users from getting logged out
' if connection is down or something to the database, error means we return true and the client logs out
Try

Using sql As New SqlClient.SqlConnection(CurrentUser.ConnectionStringSql)
sql.Open()
Using cmd As New SqlClient.SqlCommand("gpmods.SY_UserInactivityTimeOutCheck", sql)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@USERID", CurrentUser.UserID)
Dim paramReturnValue As New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(paramReturnValue)
cmd.ExecuteNonQuery()
Dim myReturnValue As Integer = CInt(paramReturnValue.Value)
If myReturnValue = 0 Then
Return False
Else
Return True
End If
End Using
End Using
Catch ex1 As SqlClient.SqlException
Return True
Catch ex As Exception
Throw
End Try
End Function

Public Sub New(TimeOutCheckFrequencySeconds As Integer)
oApplicaitonExitTimer = New Timers.Timer(TimeOutCheckFrequencySeconds)
'60000 1 min
oApplicaitonExitTimer.AutoReset = True
Me.oApplicaitonExitTimer.Enabled = False
End Sub

End Class

The add-in when initialised creates an instance of the GlobalsProcedures Class and registers the events of interest to keep the application alive when activity takes place.

Imports System.Windows.Forms
Imports DynamicsGP = Microsoft.Dexterity.Applications.Dynamics
Imports Microsoft.Dexterity.Applications
Imports Microsoft.Dexterity.Applications.MenusForVisualStudioToolsDictionary
Imports Microsoft.Dexterity.Bridge

Public Class GPAddIn
Implements IDexterityAddIn
Private HasInitalisedEventHandlers As Boolean


'GlobalProcedures essentially handles the inactivity time out functionality
Public oGlobalProcedures As New GlobalProcedures

Sub Initialize() Implements IDexterityAddIn.Initialize
Try
If Not HasInitalisedEventHandlers Then
RegisterEventHandlers()
HasInitalisedEventHandlers = True
End If
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub


Public Sub RegisterEventHandlers()

'=========================================================================================================================================
'----------------------- Hooks used for inactivity monitoring -----------------------------
'=========================================================================================================================================
AddHandler DynamicsGP.Procedures.Pathname.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresPathnameInvokeAferOriginal
AddHandler DynamicsGP.Procedures.SqlScriptPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlScriptPathInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.SqlPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlPathInvokeAfterOriginal

AddHandler DynamicsGP.Procedures.Security.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSecurityInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForNoteInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForRecordNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal
End Sub
End Class

 

Note

 
18th May 2014: We had some problems in production with instability in GP with the first version of this code. When the timer fires it creates a new thread, so a little attention to detail is required to handle multi-threaded variable access, hence in the more recent version it uses sync locks. Now those problems should be eradicated.
 

Credits

Thanks to Mohammad R. Daou and David Musgrave for their contributions to this project via forums and blogs.

Task Scheduler and COM automation

Note to self:

The solution for this appalling BUG in Microsoft IIS & Excel is terrific:

  1. Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
  2. Set Full control permissions for directory Desktop (for example in Win7 & IIS 7 & DefaultAppPool set permissions for user
    "IIS AppPool\DefaultAppPool")

 

http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/4d6c383a-94eb-4898-9d22-aa4bb69be25b