Creating a Job

I am new to SQL Server and have a few questions:

1) We have given our clients the option to create a scheduled job for a future date. When this occurs, the jobID, the new action for the job, and the future date is inserted into a table aptly called 'Scheduling'. What we are hoping SQL Server can do is the following:

Query the 'Scheduling' table based on the current date to see if there are any jobs that need their actions updated. If ( 1 ) returns a recordset, update the job's action in the 'Jobs' table with the new action Delete the rows that were queried and updated from the 'Scheduling' table

I am assuming I can do this using the schedule Jobs (excuse the irony) in SQL Server Management. Is this true?

2) I having been playing with TSQL to do the previous mentioned. My other question is how can I query the database using the current date? For example, the date in the database is entered as "mm/dd/yyyy" and I can query it using the following: SELECT * FROM Scheduling WHERE Date='6/30/2006', which will return the recordsets that I desire. If I can schedule SQL Server to do this, then how will I query it based on today's date? SELECT * FROM Scheduling WHERE Date="today's date". I tried the function GETDATE(), but that didn't seem to work. Any ideas?

Thanks

I have been trying to answer my second question on my own, but so far have been unable. Like I said earlier, I have a field in my table "Scheduling" called "Date". This is a timestamp of when my clients want their schedule for their job updated. I have been trying to query the database for today's date, but I don't know how. Here is how the table looks right now:

JobID Date DC1235 2006-05-31 00:00:00.0

I can query it by the following and it works fine:

SELECT * FROM Scheduling WHERE Date='May 31, 2006'

SELECT * FROM Scheduling WHERE Date='5/31/2006'

SELECT * FROM Scheduling WHERE Date='2006-05-31'

How can I query it using today's timestamp? The following doesn't work:

SELECT * FROM Scheduling WHERE Date=getdate() //This returns nothing

Thanks,

Scott

|||

If you use datetime, you are not using the timestamp data type which is completly different to datetime and has nothing to do with date/time. It is used for row versioning (thats different to the ANSI standard).

Using datetime means that you are have always the time stored within the data, so comparing this to GETDATE() (which returns a datetime which on its own holds a time part) will return false (except if you query at midnight :-) ). You can either convert the date to a non-timecontaining format or use the datediff function to query thise records:

SELECT * FROM Scheduling WHERE DATEDIFF(dd,Date,GETDATE()) = 0

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hi again,

1) is possible, but would that make sense, updating the row in the table and afterwards right deleting it ?

But in common, you can do schedule recurring jobs in SQL Server Agent, thats for sure true.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens! I also tried this:

SELECT * FROM Scheduling WHERE Date=convert(varchar,getdate(),101)

which seemed to work as well. Is this what you where talking about when you said, "...convert the date to a non-time containing format..."? Do you prefer one way over the other?

Also, is it possible to do what I want using a SQL Server Agent Job to perform what I was talking about in my first question on my first post?

Thanks!

|||

I guess we must have posted at the same time :)

As far as question 1 is concerned, 2 tables are affected. When a client schedules a new 'job' action for a future date, it is inserted into the 'Scheduling' table. When that date rolls around, the 'Job' table is updated with the new action and it gets deleted from the 'Scheduling' table as it is no longer needed. Hopefully I explained it better.

Thanks

|||

Also... Is there any good documentation or tutorials on how to get started with Transact-SQL or creating SQL Server Agent Jobs?

Thanks

|||

Hi,

"SELECT * FROM Scheduling WHERE Date=convert(varchar,getdate(),101) which seemed to work as well."

Sure you should always provide a length within VARCHAR otherwise it will be truncated to the length 1. I would rather prefer using 112 which is the ISO format.

"Do you prefer one way over the other?" I would prefer DATEDIFF, because it can take use of indexes.

HTH, jens Suessmeyer.

http://www.sqlserver2005-de

|||

Hi,

sorry I don′t know any good ressource, beside the e-learning classes of microsoft for adminstration, most of them are free and you can use a virtual sql server to test and train you knowledge. Beside this, as of my opinion it is always good to have a pocket book for administration of you are right starting with the sql things like this one here:

SQL 2000
OR
SQL2005

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for all your help Jens! I will look into purchasing the pocket book for SQL 2000. I am sure it can probably answer a lot of questions!

Thanks again,

Scott