It can be helpful to know how many users licenses are in use within Sage 500 ERP. This can be helpful in determining if the user limit is being reached on a regular basis and it may even be helpful to know when this is occurring.
In order to determine the number of licenses available in your Sage 500 ERP installation you can review your system information in the System Manager (SM) Maintain Site task. In this task you will see two types of users, Business Insights Users and Concurrent Users.
Business Insights User License Limits
Concurrent User License Limits
Figure 1: Sample Folder Structure of a Sage 500 Module
Figure 2: The Maintain Site task shows how many user licenses you currently own.
In order to determine how many user licenses are currently in use, you can run the attached query to provide immediate system status regarding licenses. This will provide you immediate feedback about the licenses in use on the system.
/*
GetUserCounts.SQL
*/DECLARE @_DBName VARCHAR(255),
@_AppUserCount INT,
@_BIUserCount INTselect @_DBName = 'sage500_app'
-- Get Concurrent user license counts
SELECT @_AppUserCount = COUNT(1)
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE dbid = db_id(@_DBName)
AND (program_name LIKE 'Sage MAS 500/App%' -- Version 7.2 and 7.3 Check
OR program_name LIKE 'Sage ERP MAS 500/App%' -- Version 7.4 Check
OR program_name LIKE 'Sage 500 ERP/App%') -- Version 7.6 (aka 2014)-- Get Business Insights License Count
SELECT @_BIUserCount = COUNT(1)
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE dbid = db_id(@_DBName)
AND (program_name LIKE 'Sage MAS 500/Business Insights%' -- Version 7.3 Check
OR program_name LIKE 'Sage MAS 500/BI%' -- Version 7.2 Check
OR program_name LIKE 'Sage ERP MAS 500/Business Insights%' -- Version 7.4 Check
OR program_name LIKE 'Sage 500 ERP/Business Insight%') -- Version 7.6 Check (aka 2014)select @_AppUserCount as 'ConcurrentUserCount', @_BIUserCount 'BIUserCount'
If you would like to get information about the number of licenses in use throughout the day, you can utilize the following logic to log information to a table in order to allow you to review it at a later point in time.
First, create a table to capture the user count data:
/*
Create Table
tsmUserCount_rkl.sql
*/IF OBJECT_ID('dbo.tsmLicenseCount_rkl', 'U') IS NOT NULL
DROP TABLE dbo.tsmLicenseCount_rkl
GOCREATE TABLE dbo.tsmLicenseCount_rkl
(
RowKey INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CreateDate DATETIME NULL,
AppUserCount SMALLINT NULL DEFAULT (0),
BIUserCount SMALLINT NULL DEFAULT (0),
DBName VARCHAR(255) NULL,
)
GO
GRANT REFERENCES, SELECT, INSERT, UPDATE, DELETE ON [dbo].[tsmLicenseCount_rkl] TO [ApplicationDBRole]
GO
Next, create a stored procedure that will capture the user count information and log it to the table along with a timestamp.
/*
Stored Procedure
spsmGetCurrentLicenseCount_rkl.sql
*/IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'spsmGetCurrentLicenseCount_rkl')
DROP PROCEDURE dbo.spsmGetCurrentLicenseCount_rkl
GOCREATE PROCEDURE dbo.spsmGetCurrentLicenseCount_rkl
@_iDBName VARCHAR(255),
@_oAppUserCount SMALLINT OUTPUT,
@_oBIUserCount SMALLINT OUTPUT,
@_oRetVal SMALLINT OUTPUT
AS-- Count the number of users currently logged into the system.
-- Parameters:
-- Input : @_iDBName - Database Name to check
-- Output: @_oAppUserCount - Count of Sage 500 Application Licenses in use
-- @_oBIUserCount - Count of Sage 500 BI Licenses in use
-- @_oRetVal
-- 1 = Successful Completion
-- -1 = Unexpected Error-- default to unknown error
SELECT @_oRetVal = -1-- Get license counts (these are full Sage 500 ERP users)
SELECT @_oAppUserCount = COUNT(1)
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE dbid = db_id(@_iDBName)
AND (program_name LIKE 'Sage MAS 500/App%' -- Version 7.2 and 7.3 Check
OR program_name LIKE 'Sage ERP MAS 500/App%' -- Version 7.4 Check
OR program_name LIKE 'Sage 500 ERP/App%') -- Version 7.6 (aka 2014) (need 2013 still!)-- Get Business Insights License Count
SELECT @_oBIUserCount = COUNT(1)
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE dbid = db_id(@_iDBName)
AND (program_name LIKE 'Sage MAS 500/Business Insights%' -- Version 7.3 Check
OR program_name LIKE 'Sage MAS 500/BI%' -- Version 7.2 Check
OR program_name LIKE 'Sage ERP MAS 500/Business Insights%' -- Version 7.4 Check
OR program_name LIKE 'Sage 500 ERP/Business Insight%') -- Version 2014 Check (need 2013 still!)INSERT INTO tsmLicenseCount_rkl
(CreateDate, AppUserCount, BIUserCount, DBName)
VALUES (GetDate(), @_oAppUserCount, @_oBIUserCount, @_iDBName)DELETE a
FROM tsmLicenseCount_rkl a with (NOLOCK)
WHERE a.Createdate < DATEADD (d, -30, GETDATE())--Return successful completion of procedure
SELECT @_oRetVal = 1
GO
GRANT EXECUTE ON [dbo].[spsmGetCurrentLicenseCount_rkl] TO [ApplicationDBRole]
GO
Last, setup a SQL Job that will execute the above procedure on a regular basis (possibly 3 times a day, hourly, or on whatever schedule makes sense for your environment). This job should include a script similar to the following.
/*
Call the stored procedure
You may need to change the first parameter to match you Sage 500 database name
*/DECLARE @_oAppUserCount SMALLINT,
@_oBIUserCount SMALLINT,
@_oRetVal SMALLINTexec spsmGetCurrentLicenseCount_rkl 'mas500_app', @_oAppUserCount output, @_oBIUserCount output, @_oRetVal output
select @_oAppUserCount, @_oBIUserCount, @_oRetVal
After configuring the above, you can query the log table to see your company's license usage over time.
Question: Does a user have to be assigned to a specific license type?
Answer: No, licenses are consumed based on the types of task a user utilizes within Sage 500 ERP. For example, when a user starts up the Sage 500 Business Desktop and launches an Business Insights Explorer task they will be utilizing a Business Insights User License. As soon as the user launches an Application task their user will be immediately be converted to utilize a Concurrent User license.
Question: A user closed all Application tasks other than Business Insights Explorer views and they are still consuming a full concurrent user license. Why is this?
Answer: Once a user starts any Sage 500 ERP application task they will consume a full concurrent user license. Closing the application task does not release this license from use. Only completely closing out of the Sage 500 ERP application on the workstation will release the license. This allows this user to close a task and then launch another without having a concern that another user will "steal" their license while they are in between tasks.
Question: Will all tasks under the Insights folder only utilize a Business Insights User?
Answer: While most tasks under the "Insights" folder within Sage 500 ERP will only consume a Business Insights User license, there are some that launch application tasks that will consume full user licenses. Examples of these are: