How to create job? - 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

General SQL Server Forums

New to SQL Server Programming

How to create job?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-23 : 13:02:12

Hi,

I am using SQL Server 2008 R2 and I wonder if there is a way to create a job on a table if there is NO insert,update for 20 mins?

Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 13:05:43

job to do what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-23 : 13:07:34

sorry , A job for delete all the data inside this table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 14:11:15

quote:
Originally posted by raysefo

sorry , A job for delete all the data inside this table.

yep...you can add a t-sql delete script and call it from sql agent job step

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-23 : 14:14:47

Hi visakh16,

How can I write the condition (no insert,update for 20 mins) with T-SQL?
Best Regards.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-23 : 14:24:52

Do you have a column on that table or in an audit table that tracks the Insert and/or Update date/time?

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-23 : 14:43:30

I have columns on this table.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-23 : 14:45:10

Here is the table:

CREATE TABLE [dbo].[Test](
[DrawNumber] [varchar](20) NULL,
[OddsRevision] [varchar](20) NULL,
[EventID] [varchar](20) NULL,
[EventDesc] [nvarchar](100) NULL,
[EventStatus] [nvarchar](30) NULL,
[OutComeID] [varchar](20) NULL,
[OutComeDesc] [nvarchar](100) NULL,
[OutComeGame] [nvarchar](100) NULL,
[OutComeGameCode] [varchar](50) NULL,
[OutComeOdd] [varchar](10) NULL,
[RecordID] [int] NULL
) ON [PRIMARY]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-23 : 16:19:05

I assumed your table had columns. However, it doesn't appear there is any sort of date column. So, how would can you tell which rows where inserted or updated when?

Is your actual question something like: I have a table that doesn't have any columns that indicate when a row was inserted or updated. I would like to know if it is possible to obtain that information in another place?

If so, the answer is probably no, unless there is some sort of audit in palce. You *might* be able to get lucky and try to use the DMVs, but that is not going to be 100%. Here is some code to show what I mean:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DatabsaeName')
AND OBJECT_ID=OBJECT_ID('TableName')

You need to remember we do not have any insight into your database. So, you need to provide more detail that what you started with. Here are some links that might be helpful about how to ask a database question.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-24 : 04:25:00

Hi,

I ran your query and I got this result;

DatabaseName last_user_update database_id object_id index_id user_seeks user_scans user_lookups user_updates last_user_seek last_user_scan last_user_lookup last_user_update system_seeks system_scans system_lookups system_updates last_system_seek last_system_scan last_system_lookup last_system_update
LiveOdds 2012-08-24 03:19:54.667 13 2121058592 0 0 4122 0 3706 NULL 2012-08-24 03:24:18.267 NULL 2012-08-24 03:19:54.667 0 0 0 0 NULL NULL NULL NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-24 : 12:26:02

Awesome!

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-25 : 03:20:39

Hi Lamprey,

I am not familiar with T-SQL. Can you please help me to implement the logic?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 13:16:07

quote:
Originally posted by raysefo

Hi Lamprey,

I am not familiar with T-SQL. Can you please help me to implement the logic?

sorry we thought you got it sorted
whats the issue now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-25 : 13:32:51

Hi,

Now I would like to write a T-SQL which checks this table (table:LiveOdds,Last Update Time:2012-08-24 03:19:54.667) and if there is NO insert/update for 20 minutes, I would like to delete all of the data in this table.

Best Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 13:34:40

quote:
Originally posted by raysefo

Hi,

Now I would like to write a T-SQL which checks this table (table:LiveOdds,Last Update Time:2012-08-24 03:19:54.667) and if there is NO insert/update for 20 minutes, I would like to delete all of the data in this table.

Best Regards

does job have to be invoked every 20 minutes? or is it like whenever it runs it checks for last 20 min window?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-08-26 : 01:29:26

Hi visakh16,

whenever the job runs, it should check for last 20 mins.

Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 12:43:17

quote:
Originally posted by raysefo

Hi visakh16,

whenever the job runs, it should check for last 20 mins.

Best Regards.

then write a logic based on date field


...
where datefield > dateadd(minute,-20,GETDATE())
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources