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

Creating a Backup Server software

Hi everyone,
A number of my clients keep talking about creating a backup server so that
they can quickly switch to the backup server in case the live database
server failed.
I was dabbling with the following idea.
If I could create a software that continuosly trapped SQL commands being
fired [or SPs being fired] on the live database [just like the Profiler] ,
filter out only the UPDATE syntaxes and fire them against a backup server,
couldn't I create a one-way replication programmatically?
Any ideas or suggestions from you guys?
Thanx
AbhayWhat about transaction log shipping? Then using applying them to the backup
database. In the event of failure, the backup system can be bought online
in a matter of minutes.
"Abhay Sobti" <stansoft@.vsnl.com> wrote in message
news:u$%23TZTyRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi everyone,
> A number of my clients keep talking about creating a backup server so that
> they can quickly switch to the backup server in case the live database
> server failed.
> I was dabbling with the following idea.
> If I could create a software that continuosly trapped SQL commands being
> fired [or SPs being fired] on the live database [just like the Profiler] ,
> filter out only the UPDATE syntaxes and fire them against a backup server,
> couldn't I create a one-way replication programmatically?
> Any ideas or suggestions from you guys?
> Thanx
> Abhay
>|||Bad idea. Why not use regular replication if that is what you want?
You'll have problems with things like GETDATE(), IDENTITY and GUID
columns because they will/may produce different results when the second
update is ran.

CREATE_ENDPOINT / HTTPRequest

I am trying to figure out how to communicate to some web clients when a
SQL Server table has changed. I am connecting to the database through
ADO embedded on the clients in web pages. I want to be able to refresh
data on the client only when needed. I have researched all sorts of
methodologies and it seams like the CREATE_ENDPOINT will allow for me
to create a connection where I can call some functions like;
getDataStatus and return to the client what date time the data last
changed and then allow my client to requery the database or, something
like, setAccessTime where the Client sends its last date time stamp
when It last queried for data and allow the server to send back the
requested dataset to the client.Synchronize clock, sync up the clocks
of the clients to server.
Any help would be greatly appreciated. DanHello Dan,
I don't think there's a great way to do this since Web Services is essential
ly
a pull rather than push approach. If you were working with traditional ASMX
functions, I'd suggest looking at SqlCacheDependency since that could "subsc
ribe"
to changes on a query without having to invest a lot in the infrastructure
of the solution. Your ASMX would cache the dataset (saving SQL Server genera
ting
it fresh or from cache) and would, more or less, automatically get updated
data whenever your query changed. You could then add your bits for tracking
when the last change was received.
There really shouldn't need to be a have the client do more periodically
"call home" and ask "have you got a new version of this data?" Use some seri
al
number approach instead of time so that you don't have to worry about timezo
ne
changes between the client and the server.
This probably works "better" in that it minimizes the load on SQL Server
and gives you a better programability model than just a native web service
does today. Not saying that these services aren't useful, but they are harde
r
to use concepts like SqlDepedency on.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks, I think I understand what you are getting at. There are no real
provisions to push to clients any data without request from the client
unless you take advantage of new .net technology which will allow for
caching of data on querys, tables etc... and automatically update the
data every ' as set up within the SQL or is it web config. Anyway. I
think I may have my clients poll for a serial number from an END_POINT
and then get the data when the serial number is different from the
clients. Do you think this will work. I wasn't sure if you were saying
it would or would not work? Thanks.|||Hello Dan,

> Thanks, I think I understand what you are getting at. There are no
> real provisions to push to clients any data without request from the
> client unless you take advantage of new .net technology which will
> allow for caching of data on querys, tables etc... and automatically
> update the data every '
There a new set of features in SQL Server 2005, .NET 2.0 and ASP.NET 2.0
allow a client to subscriber to notifications of changes on the data underly
ing
a query. In .NET, its common to use SqlDependency for this. In ASP.NET 2.0,
its common to use SqlCacheDependency. These subscribe to server-side events
and allow processing of them on the client side. However, they currently
require an active TDS connection to SQL Server.
When you get the notification, you can then decide what you want to do. A
common choice is to recall your loading code and restart the dependency.

> as set up within the SQL or is it web config.
Some of both. Google on SqlCacheDependency for more information.

> Anyway. I think I may have my clients poll for a serial number from an
> END_POINT and then get the data when the serial number is different
> from the clients. Do you think this will work. I wasn't sure if you
> were saying it would or would not work? Thanks.
No, because the endpoint doesn't have a good way to keep track of a serial
number. I'm basically suggesting that if you want an optimized solution (in
this case), don't use endpoints, use ASMX instead.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thank You, Ill look into it. Dan