Scheduling Job sending email if datefield = today

Hi there,
I am wondering if the following is possible.

I would like to have a scheduled job that would check every day a date field of every record (on a specific table). If that date correspond to today then I would like to send an email with the information of that record.

Is this possible?

Btw if I mentionned Job Scheduling it's because I don't know if there is other ways available for such purpose, so if you think of an other way please let me know.The way I'd do it would be to have a SQL Agent job that is scheduled to run nightly. That agent would run a stored procedure to select today's records, like this:

DECLARE @.currentdate char(8)
DECLARE @.startdatetime varchar(21)
DECLARE @.enddatetime varchar(21)
SELECT @.currentdate = CONVERT(char(8),GETDATE(),112)
SELECT @.startdatetime = @.currentdate + ' 00:00:00'
SELECT @.enddatetime = @.currentdate + ' 23:59:59.999'
SELECT
 *
FROM
 myTable
WHERE
 myDate BETWEEN @.startdatetime AND @.enddatetime

Terri|||

Is there a way to do this with out SQLAgent because tiscali's SQL Agent is not running and thy don't want to run it.
I need to call a webmethod in a webservice every 10 min. any help would be appreciated

|||Sorry, I have no idea. It's not possible through SQL withoutusing the SQL Agent AFAIK. It looks like you've asked thisquestionelsewhere so hopefully you'll get the answer you are looking for.
|||

Thanx

If you had to write a website withreminders so that the users can say I want a reminder on this day and thistime. What would you do to see if a reminder is due and send it?

I desperately have to find a way to do this.

|||Sorry, I'd use SQL Agent. :-( Where I work we host both thewebsite and SQL Server so I have all possible methods available to me.
FWIW There are several threads going on in the Getting Started forum right now related to this topic

http://forums.asp.net/944623/ShowPost.aspx
http://forums.asp.net/494136/ShowPost.aspx
http://forums.asp.net/945255/ShowPost.aspx (sort of)

Also, check out these articles:

Combine Web and Windows Services to Run Your ASP.NET Code at Scheduled Intervals
The Code Project - A New Task Scheduler Class Library for .NET

They both rely on a Windows Service to work, and it sounds like in your situation you do not have that option.


|||

Thank you for your help. I think I have toconvince my company to go to another hosting company where I can use a windowsservice.

Thank you

|||I agree with you. I chatted with a pal about this and another suggestion he had wasPaul Wilson's Keep-Alive hack.Keep in mind this is indeed a hack and is prone to failure but it couldbe better than nothing as you try to implement a more robust solution.
|||

Thank you,you are a life saver

|||All the credit goes toSomeNewKid; he is one smart cookie. I'm glad this suggestion got you over the hump.

Scheduling Info.

Hi

I am using RS 2000. Currently I am scheduling the job by manually giving the date, time. Is there any possibility to make it as data driven so that I can configure each user with different timings.

Regards,

Venkataraman M

You can create multiple data driven subscriptions to run at different schedules for different users or user groups. You can filter users for which the subscription is run in data driven subscription query.

Scheduling A Stored Procedure?

Hi,
We are using SQL Server 2000. Is it possible to somehow schedule a stored
procedure to run at a specified date and time? I know that its possible
with DTS packages, but this is just a stored proc that performs a
calculation. We want it to run at a certain time every day.
JDSQL Agent - add a job
in EM it's under <server> - Management - SQL Agent - Jobs.
Joe Delphi wrote:
> Hi,
> We are using SQL Server 2000. Is it possible to somehow schedule a stored
> procedure to run at a specified date and time? I know that its possible
> with DTS packages, but this is just a stored proc that performs a
> calculation. We want it to run at a certain time every day.
>
> JD
>|||On Wed, 30 Nov 2005 19:59:44 -0600, Trey Walpole
<treypole@.newsgroups.nospam> wrote:
>SQL Agent - add a job
>in EM it's under <server> - Management - SQL Agent - Jobs.
>Joe Delphi wrote:
or you can run it from inside a DTS package.
J.|||Another alternative:
You can create a bat file that will run the stored procedure using osql.exe.
Then schedule that bat file to run using windows scheduler.
Lucas
"jxstern" wrote:

> On Wed, 30 Nov 2005 19:59:44 -0600, Trey Walpole
> <treypole@.newsgroups.nospam> wrote:
> or you can run it from inside a DTS package.
> J.
>

Scheduler UTC problem

Hi all,

I'm having trouble to implement an "scheduler" in a stored procedure with UTC date.

In one table (ScheduleCfg) I have one field that indicates which days of the week my banner must be exhibited, separated by commas (e.g. 0,5,6)
In my table "Schedule", I have a datetime field that indicates which is the next time that the banner must be shown.
(To explain: I have a process which runs every hour to republicate the banners and recalculate the next exhibition hour).

My SP gets the "Last Display" datetime and sums one hour... If the day still the same, then the new value of "Next Display" is this value.
But if it changes the day, then I'll have to verify if the new day's dayofweek number matches with the tables...

Until this everything works fine... But all my problem is: UTC TIME!

My application is globalizated, so many countries can use it. All the dates stored in my database must be UTC, so here's my problem:

When I sum one hour, I'm using the UTC date. So the "turn of the day" occurs in a different moment. For example, in Brazil the TimeZone is -3 hours, so when the day changes it will be 9 o'clock in Brazil. (because it's 0h UTC, so the Brazil's local time will be 9h PM of the day before...)

Anyone can help me?
I've been thinking about it for weeks and I couldn't find a good solution...

Thanks in advance.

This should work. I've used the difference between the GetUTCDate and GetDate functions to get the difference between the local timezone and UTC. Here is my suggested approach:

Code Snippet

--This variable contains the last saved date

Declare @.LastDisplay as Datetime

--Retrieve the value from the 'Schedule' table

--Retrieve the number of hours difference between the local timezone and UTC

Declare @.HourDiff as Int

Select @.HourDiff = DateDiff(hour, GetUTCDate(), GetDate())

--Calculate the next schedule date taking into consideration the timezone difference and adding an hour

Declare @.NextDisplay as Datetime

Set @.NextDisplay = DateAdd(hour, 1 + @.HourDiff, @.LastDisplay)

Select @.NextDisplay

--Check the difference between the last date (in UTC) and the new date (local time)

If DateDiff(day, @.LastDisplay, @.NextDisplay) = 1

Begin

--The day has changed

End

Else

Begin

-- The day has not changed

End

The DateDiff at the end, compares the last date the schedule ran which is saved in UTC with the new calculated date which is in local time. If the difference is not 0 then this is a new date and you can perform the logic that you want.

I hope this answers your question.

Best regards,

Sami Samir

|||Hmmm. Good idea..
I'll try to pass a parameter with the time difference, since my SQL Server does not know which country is calling..

Thank you!!!!

Scheduled Job Not Working

I have created a scheduled job in the enterprise manager, which checks around 20 different date columns and replaces any non-dates with NULL using the following syntax: -

update policy set [exp] =null where isdate (substring([exp],4,2)+'-'+left([exp],2)+'-'+'200'+right ([exp],1 ))=0
go
update policy set [eff] =null where isdate (substring([eff],4,2)+'-'+left([eff],2)+'-'+'200'+right ([eff],1 ))=0
go
update policy set [written] =null where isdate (substring([written],4,2)+'-'+left([written],2)+'-'+'200'+right ([written],1 ))=0
go

etc......

When I start the job, if fails within 2 seconds. If I copy the syntax in to the query analyzer it works fine.

All help appreciated.

A couple of questions

1 what is the error

2 who is the owner of the Job (and does he have permissions to the DB)

3 is the correct DB seleted in the step?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

to view the errro message , right click on the job, select view job history, click show step details (top right)

Click on the stepid and the error will be displayed in the bottom part

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi there,

The error message is: The job failed. The Job was invoked by User KEELAN_WESTALL\James. The last step to run was step 1 (UpdateDates).

The user is James (me)

I have security to the server/database/tables etc.

The correct DB is selected.

|||

The error message is: -

Executed as user: sa. The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256). The step failed.

|||

Convert to varchar first, take a look at this

select substring(1952005,1,2) -- will fail

select substring(convert(varchar(8),1952005),1,2) -- correct

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

As a test I ran the job just changing one column using the following syntax: -

update policy set [exp] =null where isdate (convert (varchar(8), (substring([eff],4,2)+'/'+left([eff],2)+'/'+'200'+right ([eff],1))))=0
go

It worked perfectly.

Thanks very much Denis.