Prevent SQL job running when another job is running

 
CREATE VIEW myschema.Admin_RunningSQLJobs
AS
SELECT job.NAME
,job.job_id
,job.originating_server
,activity.run_requested_date
,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
JOIN (
SELECT MAX(agent_start_date) AS max_agent_start_date
FROM msdb.dbo.syssessions
) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL
AND stop_execution_date IS NULL


Using this view we can check for a running job before running our job…
In this example we don’t want the nightly pricing build to run if a monthly build is in progress, note, nightly is scheduled later than monthly. So wrap the call to the stored procedure in the job step like this…
 
IF NOT EXISTS(SELECT * FROM myschema.Admin_RunningSQLJobs WHERE name='Pricing - Monthly Rebuild') 
BEGIN
EXEC myschema.Nightly Pricing build
END

Note about backups:
We restore the live company into the test company of our ERP system, once a week, to keep the test company data fresh. It is worth noting that it is wise to keep the above SQL view in a database that is not involved in a restore, otherwise errors will occur trying to access the view as that database is in restore mode and cannot be accessed by the SQL job.
For this reason we keep this view in an “admin” database that is not part of the automated restore process, using the view in SQL jobs used against the test database prevents them erroring when the restore process in in progress.
 

Protocol handler debugging in Dynamics GP (drill down) problems

In previous blog posts we have talked about the way that other applications can drill down into parts of GP by using an direct approach via a pluggable protocol handler that is installed when GP is installed. Sometimes this does not work, to debug if the protocol handler has an end point to talk to follow these instructions.

 

Get Handle.exe

Download Handle.exe which is part of the SysInternals tools that Microsoft acquired some time back, written by Mark Russinovich,

https://docs.microsoft.com/en-us/sysinternals/downloads/handle

Extract the Zip file, if you have 64 bit system use the 64 bit version, otherwise use the plain version.

Handle viewer archive extracted

Use PowerShell to pipe out and decode handle.exe output

Open a PowerShell editor on your machine (e.g. start menu, search powershell, launch Windows PowerShell ISE)

In my case I extracted the handles.exe into the C:\Users\tw\Documents\ folder, you will need to change the path for where you extracted it to…

Execute the following PowerShell Command:

C:\Users\tw\Documents\handle64.exe net.pipe -accepteula

This will accept the user agreement and pipe the output of the handle viewer to powershell for decoding.

This gives the following output.

2018-10-31_14-55-08

You can see that visual studio and Dynamics.exe have named pipes running.

If I close Dynamics GP, then run it again…

2018-10-31_15-03-22

You see that Dynamics .exe is not listed as having named pipes available anymore as it is closed.

2018-10-31_15-15-42

[System.Text.Encoding]::UTF8.GetString([Convert]::FromBase64String("bmV0LnBpcGU6Ly8rLw=="))

net.pipe://+/

 

Using powershell to list all

[System.IO.Directory]::GetFiles("\\.\\pipe\\")

Diagnosing PowerShell New-GPSystemDatabase error when creating the Dynamics GP system database

The PowerShell modules for GP allow a number of DevOps activities. For example you may install the Dynamics GP Lesson (sample) company TWO and install the system database via these PowerShell modules. I take full advantage of the PowerShell modules when running and building Docker images for Dynamics GP. 

Today whilst doing this I encountered the following error when moving to build the image for a different version of Dynamics GP:

Dynamics GP - Macro failed to complete successfully

Macro failed to complete successfully.

The PowerShell script works by creating a macro file in the GP\data folder, the PowerShell script then runs DexUtils passing the macro file as a command parameter. The macro file tells utilities what it needs to do (in this case create the system database), you can see this macro in the folder below, captured after this error occurred in the Docker container. CreateSysetmDatabase.mac is the macro file that PowerShell is using to create the system database. 

CreateSystemDatabase.mac

We have no idea at this point what the error actually is, as the error description of “Macro failed to complete successfully” is not very helpful. However you will notice also in that folder the “DexMacro.log” file. Lets look in that file by reading it with the command “type DexMacro.log”.

2018-10-25_21-54-55

So below is the output of that command, the contents of the log.

GP SQL server version issue

There we can see the issue, I’m using SQL Server 2017, I already know this is only compatible with GP2018 onwards. However you will notice from the folder name I’m trying to use it with GP2016. The error is badly worded, it says you need to upgrade to server 11 from 14. It actually means you need to use an older version of SQL for this version of Dynamics GP. Something I already knew but my focus wasn’t in this area at the time I was developing the Docker container.


Please comment if you found this helpful – motivates me to blog more!

Problem with extensions search in visual studio

If you are using Dynamics GP with Visual Studio 2017 –>  , you will find that the project templates for Dynamics GP do not show up in the new project or new item menus.

I wrote a solution to this in the form of a Visual Studio extension, but it seems that the extension is not showing up when it is searched for in the extension gallery, something brought to my attention via the Microsoft forums.

 

However if you page through the results then go back to page one of the results, then all of a sudden it appears in the list!

 

I’ll be bringing this to the attention of the VS team, in the meantime the video shows this work around.