making a continous loop
-
i want to write a sproc that will be called by a job. the sproc will continually check if a certain login is on the system. if it is not, it will do nothing, but if it is, it has other actions to perform. all i can think of right now is:
while (1 = 1)
begin
--check with if statements here for the login
end
this seems very archaic to me. is there a better/tidier/more efficient way to do this?
-
Why not simply call the job every 5 seconds and run only once per call?
-
Sounds like a job for a logon trigger... maybe?
-
a login trigger! brilliant !!! is there a logout trigger also?
-
crap. just realised the database i question is still on sql server 2000.
so far my solution is this.
I created a job called checkForLogin.
This job runs each minute.
its code is this:
if ((select count(*) from sys.dm_exec_sessions with (nolock)where login_name like '%login')>=1)
begin
exec sp_stop_job @job_name = 'checkForLogin'
exec sp_update_job @job_name = 'checkForLogin'
,@enabled=0
exec sp_update_job @job_name = '2nd job',@enabled=1
exec sp_start_job @job_name = 'checkForLogin'
end
The 2nd job simply keeps checking while the login is on the system, again each minute. when the login leaves our syste, 2nd job does the opposite of the first job. it disables itself, starts and enables job one , and stops itself.
The problem here is when either of my jobs run, it can disconnect users from the server and disallow them access again. i get the error:
"a connection was successfully established with teh server but then an error occoured during the pre-login handshake. when connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server does not allow remote connections(provider: Named Pipes Provider, error: 0 -No process is on the other end of the pipe.)(Microsoft sql server ,error:233)
anyone have any idea what is going on?
-
found out the issue. i had the server limited to 4 remote connections. youd never know from the above error!!! i would expect the error to say something like " there are no more availiable connections availiable. If you require more connections please configure your server to allow more remote connections". i guess im just weird though!!!
-
You expect SQL to make sense in error messages?
No such thing. Although 2005 seems a bit better in this regard.I do not think there is a logout trigger.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply