A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...
Hi
This is one of the things connection pooling would help with, but then you
will not be re-writting the package! I guess you could have a job that checks
the last_batch time in sysprocesses.
John
"clairvoyant316" wrote:

> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
|||clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for your prompt response.
I ran that script and crashed the MS SQL 2000 with timeout as well as
corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
It didn't kick users out of the Great Plains at all.
Is there any condition or special configuration that I am not aware of to
run that script?
Thank You
"Tracy McKibben" wrote:

> Tried to post this yesterday, apparently it didn't "go". Here's a
> script (and table) that I've used to handle this very problem in GP.
|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.

> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:

> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
>
|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||

Quote:

Originally Posted by John Bell

Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Interesting code, Tracy, but I am curious - what would become of a document or transaction that a user left open? I would guess that any unsaved changes would be lost, but the document itself is still retained. is that correct?
I do a fair amount of work in GP Modifier & VBA, plus SQL for my company. This is something we've considered doing in the past, to reduce the number of occupied licenses for clients.
Thanks! BTW, you have a neat site...I've bookmarked it.