Blog

Our latest posts and rants

Dynamics GP logo

Tracking Dynamics GP License Use

You might need to know how many licenses of Microsoft Dynamics GP are in use. Maybe you’d like to purchase more licenses while you still have a couple extra, or you suspect you can give some back, or whatever.

A happy chef

You can quickly set up a repeating job to take snapshots of your license use, with whatever detail you wish. In our case we just wanted to know how close we were to hitting our license limit.

Make a table for the usage history. Since it applies across companies, I inserted it into the DYNAMICS database. I began the table name with SSC_, our company initials, to group it with the rest of our custom tables.

CREATE TABLE dbo.SSC_LicenseHistory (
	WhenMeasured datetime NOT NULL,
	LicensesInUse int NOT NULL,
 CONSTRAINT PK_SSC_LicenseHistory PRIMARY KEY CLUSTERED 
(
	WhenMeasured ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY;

Then I made a SQL Server Agent job to periodically save a snapshot of the license usage.

USE msdb;
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories 
  WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
  @type=N'LOCAL', @name=N'[Uncategorized (Local)]';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

END;

DECLARE @jobId BINARY(16);
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect GP License Trends', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Occasionally count how many GP licenses are in use for later analysis of historical trends', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'SSCORP\rkohler', @job_id = @jobId OUTPUT;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Save license count', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'insert into SSC_LicenseHistory (WhenMeasured, LicensesInUse)
select current_timestamp,count(*)
from Activity;', 
		@database_name=N'DYNAMICS', 
		@flags=0;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Save License Count Schedule', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=62, 
		@freq_subday_type=8, 
		@freq_subday_interval=1, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20131123, 
		@active_end_date=99991231, 
		@active_start_time=61500, 
		@active_end_time=183000, 
		@schedule_uid=N'9dde8acd-762b-4369-98bb-d3406c96f23f';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
EndSave:
;
GO

So we have a table that looks like this:

Two columns, When Measured and Licenses in Use.

You can add to this. For example, you can save the number of people using multiple licenses.

Then we report on the license usage. For our internal use we just made a simple query that returns the min, mean , and max license count from the past 30 calendar days. Your needs may differ.

create view dbo.SSC_rpt_GpLicenseUse as
select top 2000000000 -- SELECT TOP allows me to use an ORDER BY clause in the view
  cast(WhenMeasured as date) as Date,
  min(LicensesInUse) as MinLicenseCount,
  avg(LicensesInUse) as MeanLicenseCount,
  max(LicensesInUse) as MaxLicenseCount
from Dynamics.dbo.SSC_LicenseHistory
where WhenMeasured >= dateadd(day,-30,cast(current_timestamp as date))
and LicensesInUse > 0
group by cast(WhenMeasured as date)
order by cast(WhenMeasured as date) desc;

That’s it! For a few minutes of work, you can start to see your license trends. The job keeps watch even when you are on vacation.

Bored Guy

Hope this helps!

Paint.Net logo

Building App Icons with Windows Paint.Net

I frequently use Paint.Net to make icons for cell phone apps that I make for my own purposes (i.e. not official Visual Construction Media apps). Paint.Net is a great little program, worthy of articles all by itself. Today I wanted to pass along a couple quick tips that might be obvious to most but could be helpful to a few others like me.

When I need an icon, I usually start with Image, Canvas Size to set the size of the icon.

Icons frequently need to be on a transparent background. A very quick way to do that is Edit, Select All followed by Edit, Erase Selection.

I sometimes want to make a symmetric blob of color centered on the transparent background. A very quick way to accomplish that is to draw something in one quarter of the canvas on top of a transparent background. Then I use:

  • Edit, Select All
  • Edit, Copy
  • Image, Flip Horizontal and/or Vertical
  • Edit, Paste in to New Layer
  • Layers, Merge Layer Down

Or instead of flipping horizontally and/or vertically, you can get rotational symmetry by using Image, Rotate. And you can apply effects to the copies, etc.

A happy chef

Well, I did promise these tips would be quick. You can obviously spend a lot more time and money, and sometimes that is justified, but these tips have helped me generate simple icons quickly and cheaply when the occasion called for it. Maybe they’ll help you also.

WPLogo

Windows App Developer Incentives

In my endless quest for free stuff, I’ve noticed that Microsoft and Nokia have some incentive programs running at the moment. Maybe they’re of interest to you also.

App Builder rewards

Microsoft App Builder Rewards lets you earn and spend reward points.

DVLUP logo

The Nokia DVLUP Program is very similar. You earn points by developing applications, and spend points on reward items ranging from clothing to computer equipment.

Marmalade logo

The Marmalade Windows Phone Incentive could get you a 3-month Marmalade license, a year in the Windows App Store, and a Windows Phone 8 device.

Unity logo

The Unity 3D Game Engine is now free for Windows 8, Windows Phone 8, and XBox One developers. The free edition now allows you to publish for those three platforms.

BigBagOfCash

These are just a few more indications of what an exciting time it is to be a developer right now. Hope this compilation helps!

Dynamics GP logo

Applying New Standard Costs in GP

In yesterday’s post about calculating a standard cost for new assemblies based on component costs, I didn’t finish the story by showing what we did to apply those calculations in GP.

A worried person

Oh no!

But not to worry, I’ll remedy that oversight today.

In our case we wanted to put the calculated costs onto the GP item card as a standard cost. So I used this expression involving the SSC_BOMCost function from yesterday’s article:

with recursiveCosts as (
select c.*
from dbo.bm00101 b
cross apply dbo.SSC_BomCost(b.itemnmbr) c
where b.ITEMNMBR like '6-%'
and b.ITEMNMBR in (
  select ITEMNMBR
  from dbo.iv00101
  where ITEMTYPE=1
  and CURRCOST=0
  and STNDCOST=0)
)

update x
set stndcost=z.Cost
from iv00101 x
join recursiveCosts z
on x.itemnmbr=z.OriginalItemNmbr
where itemnmbr in (
select OriginalItemNmbr
from recursiveCosts)
and z.Cost is not null
;

Hope this helps!