Which Codeunit Calls when Open a Database

lakshman

Hi Experts,

My Requirement is To restrict to open a database if the database is already open with same userid. so that i want to know which codeunit calls when open a database.
(not a open company. when open company codeunit 1 called).please suggest me.



Thanks & Regards
lakshman

greensmile

lakshman wrote:

Hi Experts,
My Requirement is To restrict to open a database if the database is already open with same userid. so that i want to know which codeunit calls when open a database.
(not a open company. when open company codeunit 1 called).please suggest me.
Thanks & Regards
lakshman

It is old catch no trigger exists on database open (I tested with debugger, first trigger is CompanyOpen in CU1 after company choice). IF SQL server option used, you can run following stored procedure trigged by SQL server agent.

CREATE PROCEDURE KillGluttonUsers AS
DECLARE @Victim INTEGER
DECLARE @SQLCommand VARCHAR(30)
DECLARE _ToKill CURSOR LOCAL FAST_FORWARD FOR
  SELECT [Connection ID]
  FROM Session
  INNER JOIN (
    SELECT
      s.*, 
      (SELECT TOP 1 [Connection ID] FROM Session WHERE s.[User ID] = Session.[User ID] ORDER BY [Login Time]) AS FirstConnectionID
    FROM
    (
      SELECT COUNT(*) AS NoOfConnections, [User ID]
      FROM Session
      WHERE [Application Name] = 'Microsoft Business Solutions-Navision client' AND [Database Name] = db_name()
      GROUP BY [User ID]
      HAVING COUNT(*) > 1
     ) s
  ) k ON Session.[User ID] = k.[User ID] AND Session.[Connection ID] <> k.FirstConnectionID
  WHERE [Application Name] = 'Microsoft Business Solutions-Navision client' AND [Database Name] = db_name()

OPEN _ToKill
FETCH NEXT FROM _ToKill INTO @Victim
WHILE @@FETCH_STATUS = 0 BEGIN
  SET @SQLCommand = 'KILL ' + CAST(@Victim AS VARCHAR)
  EXEC (@SQLCommand)
  FETCH NEXT FROM _ToKill INTO @Victim
END
CLOSE _ToKill
DEALLOCATE _ToKIll

garak

search also the forum for this.There are also examples if u are under native (CU1) or sql (Procedure to kill the session directly on sql server engin)

Regards

dmccrae

Another approach on SQL is to create the stored procedure [$ndo$loginproc] in the database.

You can put any logic you want in here (it should not return result sets though) and it should have execute permissions granted to public.

NAV will execute this proc on database open (if it exists) and will disallow the connection if the proc raises an error message with RAISEERROR.

For example, you can check if there is a connection already using the current user ID as you mentioned, similar to the code shown in the previous post, and if so raise an error disallowing the new connection. This proc is documented I think in the Application Designers guide, although I cannot check that right now.

[You can also use this proc to have a message sent to NAV, which is displayed for information only in a dialog box, but allows the connection to continue. E.g. "System down for maintencance at 09:00"].

garak

@dmccrae: Are there some other $ndo$ Procedures / Tables which are checked by NAV ( [not] documented features) ?

Here a link for all for $ndo$loginproc http://msdn.microsoft.com/en-us/library/dd338930.aspx
it's also explained in the Application Designer's Guide

Regards

dmccrae

The examples in your MSDN link are those I had originally prepared and outlined in the ADG, to demonstrate the use of the PRINT and RAISEERROR scenarios. There are no other built-in procs like this; the only other similar concept is the [$ndo$dbconfig] table, which is pretty well known now for influencing index hint behaviour and so on.

If you look at a SQL Profiler trace you will see the (attempted) execution of the login proc when connecting.

Important: If you use this proc and grant execute permission to public, you are of course allowing all users to run it. That's not an issue if its only purpose is disallowing a connection, or showing a warning in the client - but any activity on the database should be avoided, or treated with caution.