November 5, 2003 at 7:37 am
As Usual We have bought a piece of software from a tin pot company.....
The said piece of software keeps a log of the active licenses in a table, this table contains the PC number and a '1' to say that the pc is logged in.
Whenever the PC falls over or the sql server is stopped for the backup (Bad idea I know but it was not my decision!!!!) the entry in the table is not cleared and the PC cannot gain access...
is their any easy way to either script a proc to clear the entry on login or to check that the user is not connected and clear the entry???
Any Ideas appreciated.. I know that they should realy fix their software
ANDOI
November 5, 2003 at 9:19 am
Unless you can trigger something to run, then it would be hard. You could setup a process (job) to run every minute and look for entries in sysprocesses and compare these to the table. If there isn't an active connection, then clear the row in teh table.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 6, 2003 at 9:13 am
This won't help if the pc disconnects, but you could write a stored proc to clear the entries, and set it up as a start up proc. This would clear all entries any time SQL is started on your server.
November 6, 2003 at 9:19 am
Problem solved....
I have creates a stored procedure to check the sysprocesses against the Workstation ID table and scheduled this to run every 10 mins (a bit excessive but it stops them complaining)
CREATE PROCEDURE [dbo].[usrClear_WS_Loggedin] AS
/*
###########################################
# Venuemaster.dbo.usrClear_WS_Loggedin #
###########################################
# #
# Stored procedure to Clear the Logged in #
# Flag in the Venuamaster Table WSID #
# This Procedure should be run Approx #
# Every 5 Minutes via a scheduled task #
# to clear out the WSID Logged_IN flag #
# for a PC that is no longer connected #
###########################################
# Version Information #
###########################################
# #
# 0.1 06/11/2003 Andrew Mitchell Initial Concept #
###########################################
*/
set nocount on
declare @PC varchar(50) -- Variable to hold the PC Name
Declare VMaster cursor -- Create a Cursor to hold the PC Names
for
-- Select All Logged in PC Names From the Venuemaster WSID Table
select NTName from Venuemaster.dbo.WSID where WS_Loggedin = 1
open VMaster -- Open the Cursor
fetch next from VMaster -- Read the next value
into @PC-- Into the Variable
while @@Fetch_status = 0-- While there are recordsin the cursor
begin
-- If there os no matching process id in the SQL Server sysprocesses table
if not exists (select * from master.dbo.sysprocesses where dbid = (select dbid from master.dbo.sysdatabases where name='Venuemaster') and hostname = @PC)
begin
-- Remove the Logged in flag
Update Venuemaster.dbo.wsid set WS_Loggedin = 0 where NTName = @PC
PRINT @PC + ' Cleared'
end
-- Read the next value
fetch next from VMaster
into @PC
End
-- CLose the Cursor
Close VMaster
-- Tidy Up
deallocate VMaster
GO
Thanks for the help
November 6, 2003 at 9:25 am
You're welcome and glad it's working.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply