Got Stuck using SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Got Stuck using SELECT

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-18 : 13:13:15

Hi All,
I have 2 tables where i want to make decisions from.
The first table is user table

AppUser
---------
Userid var...
Logged bit ...
username....

the second table
userinfo
.........
userid varch...
ast_page_visit_time datetime...

Logged will be set to 1 when the user logs successfully
and for every page the user browses, the datetime of visit will
be updated into userinfo.

so i have two tables, so i want my query to read thru appuser take d userid check it in userinfo and check d date if its above 20 minutes, if above 20 minutes, check logged to 0. then for next, until end record. The basis of the query will if logged = 1 in appuser table.

This is my sample query, but i know it wont work

CREATE PROCEDURE UpdateCRI
AS
update appusers set Logged = 0
where UserID in (select userid from userinfo)
and (datediff(mi,(Select last_page_visit_time from userinfo),getdate())) > 20

Thanks.

I sign for fame not for shame but all the same, I sign my name.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-18 : 13:28:33

[code]
update appusers
set
Logged = 0
from
appusers
join
userinfo
on appusers.userid = userinfo.userid
where
appusers.Logged = 1 and
userinfo.last_page_visit_time < dateadd(mi,-20,getdate())[/code]

CODO ERGO SUM

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-18 : 13:55:45

Hi All,
DBA Colonel thanks for the post.
I have a problem now, i tried o make the query a scheduled tasks in sql server. I have created a job using the query above, the problem am having now sql server is saying "SQLServerAgent is not currently running so it cannot be notified of this action". Please, how can make d tasks work?

--Lekan aka abacusdotcom

I sign for fame not for shame but all the same, I sign my name.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-18 : 14:07:27

Start the SQLServerAgent.

If you don't know how to do this, read about SQLServerAgent in SQL Server Bookd Online.

CODO ERGO SUM

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-18 : 14:08:12

Thanks A Lot

I sign for fame not for shame but all the same, I sign my name.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2005-04-18 : 14:29:33

Hi,
Can I set the scheduled time to 20 min. SQL server kept making the interval an hour. why?

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources