Concurrent User account on SPLA license

hairam

Hi All
The issue is as follows we are a hosting/navision company and as such we have multiple customer servers.
We use a SPLA license and the customers then rent their licenses pr. user basis.
The SPLA license always have 999 users available, so we cannot control how many they actually have logged on at any given time, in the normal way. (aside form doing it manually in sessions)
I seem to recall that I found a article about limiting number of users pr. database on the SQL. I can however not find anything about it.
Another possible method would be maybe putting something in Codeunit 1 to limit it.
Good ideas?
Sincerly
Jacob

kine

You can use the sp_$ndo$loginproc on SQL to check what you want before the user is logged in. You can e.g. count active sessions in he session view an allow/disallow the login. See viewtopic.php?t=31486&f=5 for more...

hairam

Super thanks that was exactly what I was looking for. Thank you

hairam

I ofcourse ran into a little snag which you SQL experts might assist me with.

I need to count the current quantity of users using the database but which table is it stored in.

In C/AL I would use a setrange and count up but SQL is not quite my home turf. : )
If someone have a code snippet or something that do it or similar it would be very helpful

/jacob

kine

Just use the Session view to count what you want... :-)

hairam

Sorry for the stupid questions but how do I do that via SQL code.

I am using the code from the other topic

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_$ndo$loginproc' AND type = 'P')
DROP PROCEDURE [sp_$ndo$loginproc]
GO
CREATE PROCEDURE [sp_$ndo$loginproc]
@appname VARCHAR(64) = NULL,
@appversion VARCHAR(16) = NULL
AS
BEGIN
IF (Insert special code that checks for how many users are connected to the DB < 12) THEN
RAISERROR ('Too few user sessions available', 11, 1)
END
GO
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

kine

DECLARE @SessionCount int
SELECT @SessionCount =COUNT(*) 
  FROM dbo.[Session]
  WHERE [Database Name]=DB_NAME() and [Application Name]= 'Microsoft Dynamics NAV Classic client'
IF @SessionCount < 12 THEN

hairam

For some reason I am not quite following I get a compile error.

Msg 156, Level 15, State 1, Procedure sp_$ndo$loginproc, Line 10
Incorrect syntax near the keyword 'THEN'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'sp_$ndo$loginproc', because it does not exist or you do not have permission.

I am guessing the last one is because it fails before it gets created but the first I dont quite get. Or most likely I dont have the syntax quite right. : )

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_$ndo$loginproc' AND type = 'P')
DROP PROCEDURE [sp_$ndo$loginproc]
GO
CREATE PROCEDURE [sp_$ndo$loginproc]
@appname VARCHAR(64) = NULL,
@appversion VARCHAR(16) = NULL
AS
BEGIN
  DECLARE @SessionCount int
  SELECT @SessionCount =COUNT(*) 
  FROM dbo.[Session]
  WHERE [SOFNAVI]=DB_NAME() and [Application Name]= 'Microsoft Dynamics NAV Classic client'
  IF (@SessionCount > 2) THEN RAISERROR ('Too few user sessions available', 11, 1)
END
GO
GRANT EXECUTE ON [sp_$ndo$loginproc] TO public
GO

kine

Sorry, just remove the "Then", the IF statement in SQL is not using it... ;-)

hairam

Works wonders, thank you very much.

/jacob