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.

Scheduled report by email with attached report fails

Can anyone offer any help with this?

Error maesage:
Status:
Failure sending mail: The Report Server has encountered a configuration error; more details in the log files

Windows 200 Server: SP4
RS 2000: SP2
SQL Server 2000, on the same server as RS.

We

get this error whenever we try to run a report with the report

attached. However, the email sends fine if the report is not attached.

Reports can also be exported into different formats without problem when viewing the report through report manager.
I received a email telling me there had been a reply to this thread, but I see nothing.

Can anyone help with this?|||Did you check the log files to see what the error is?|||Bringing this topic back from the dead a little I know, but I've finally had chance to get back to trying to use this since being switched onto other projects.

Which log would likely contain the information needed.
The logs located at: C:\Program Files\Microsoft SQL Server\MSSQL\LOG
called: SQLAGENT.OUT ?|||I am having the same problem. The report service log file only re-iterates the message, and does not provide any further details.
I checked the Report Server configuration file and it looks okay, nothing unusual.
Does anyone have any idea what may be causing the problem?|||

The RS log files are located here:

%Program Files%\Microsft SQL Server\MSSQL.2\Reporting Services\LogFiles

The Windows Service log file (which processes subscription events) should have a name like this:

ReportServerService__03_19_2007_00_01_09.log

|||

No entry is made in that folder when it fails the job.

However, we have noticed that if my colleague creates the subscription then it works fine without the error. So I suspect it might be related to permissions in sql server or on the win 2000 server box. So far I've been unable to determine which permission is causing it. Despite ensuring I am in the same permission groups as he is, my subscriptions still fail.

Scheduled report by email with attached report fails

Can anyone offer any help with this?

Error maesage:
Status:
Failure sending mail: The Report Server has encountered a configuration error; more details in the log files

Windows 200 Server: SP4
RS 2000: SP2
SQL Server 2000, on the same server as RS.

We

get this error whenever we try to run a report with the report

attached. However, the email sends fine if the report is not attached.

Reports can also be exported into different formats without problem when viewing the report through report manager.
I received a email telling me there had been a reply to this thread, but I see nothing.

Can anyone help with this?|||Did you check the log files to see what the error is?|||Bringing this topic back from the dead a little I know, but I've finally had chance to get back to trying to use this since being switched onto other projects.

Which log would likely contain the information needed.
The logs located at: C:\Program Files\Microsoft SQL Server\MSSQL\LOG
called: SQLAGENT.OUT ?|||I am having the same problem. The report service log file only re-iterates the message, and does not provide any further details.
I checked the Report Server configuration file and it looks okay, nothing unusual.
Does anyone have any idea what may be causing the problem?|||

The RS log files are located here:

%Program Files%\Microsft SQL Server\MSSQL.2\Reporting Services\LogFiles

The Windows Service log file (which processes subscription events) should have a name like this:

ReportServerService__03_19_2007_00_01_09.log

|||

No entry is made in that folder when it fails the job.

However, we have noticed that if my colleague creates the subscription then it works fine without the error. So I suspect it might be related to permissions in sql server or on the win 2000 server box. So far I've been unable to determine which permission is causing it. Despite ensuring I am in the same permission groups as he is, my subscriptions still fail.

Scheduled report by email with attached report fails

Can anyone offer any help with this?

Error maesage:
Status:
Failure sending mail: The Report Server has encountered a configuration error; more details in the log files

Windows 200 Server: SP4
RS 2000: SP2
SQL Server 2000, on the same server as RS.

We get this error whenever we try to run a report with the report attached. However, the email sends fine if the report is not attached.

Reports can also be exported into different formats without problem when viewing the report through report manager.
I received a email telling me there had been a reply to this thread, but I see nothing.

Can anyone help with this?
|||Did you check the log files to see what the error is?|||Bringing this topic back from the dead a little I know, but I've finally had chance to get back to trying to use this since being switched onto other projects.

Which log would likely contain the information needed.
The logs located at: C:\Program Files\Microsoft SQL Server\MSSQL\LOG
called: SQLAGENT.OUT ?
|||I am having the same problem. The report service log file only re-iterates the message, and does not provide any further details.
I checked the Report Server configuration file and it looks okay, nothing unusual.
Does anyone have any idea what may be causing the problem?|||

The RS log files are located here:

%Program Files%\Microsft SQL Server\MSSQL.2\Reporting Services\LogFiles

The Windows Service log file (which processes subscription events) should have a name like this:

ReportServerService__03_19_2007_00_01_09.log

|||

No entry is made in that folder when it fails the job.

However, we have noticed that if my colleague creates the subscription then it works fine without the error. So I suspect it might be related to permissions in sql server or on the win 2000 server box. So far I've been unable to determine which permission is causing it. Despite ensuring I am in the same permission groups as he is, my subscriptions still fail.

schedule job to set database to single user mode

Task Summary:
I have a remote user to sending records every 5 minutes by DTS package.
I think the best way to handle this is to create a job that will up the
database
into single user mode, set the remote user to the single user.
allow the job to run 10 to 15 minutes then take it out of single user mode.
Task Break down:
1:
Put the database into single user mode.
assign myself as the user.
(How do I define which user has access?)
2:
Backup the database
unassign myself as the single user
assign remote user as the single user
3:
Scheduled the job to execute for 15 minutes
4:
take database out of single user mode
5:
Schedule job to execute evey 6 hours.
Hi
"mj" wrote:

> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
You can not assign another user to be the single user in single user mode!
If you are not connected to the database when it is set in single user mode
then potentially anyone could get in and be that single user.
I am not sure why the remote user is send things every 5 minutes and you
will only want him to send something for a 15 minute period every 6 hours.
What may be a better scenario is that every 6 hours you fetch the information
from the remote location and then processes it in one batch, then the process
is controlled by the server. A different approach you be to have a second
database that the user updates (local or remote) and then use replication to
merge the differences. With SQL 2005 service broker may possibly be useful.
John
|||What is the justification for this attempt to "lock the database into
single-user mode for a specific user"?
TheSQLGuru
President
Indicium Resources, Inc.
"mj" <mj@.discussions.microsoft.com> wrote in message
news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user
> mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
>
|||Thanks John and SQLGuRu for the interest;
More Background Details:
The remote user has developed an extensive DTS package.
When the transformations have been completed, then the transformed
data is sent to a central server.
For this reason I can not just go get the data.
To ensure the migration is completed without interuption, I need to put the
server into single user mode.
To preserve the data prior to the migration, I need to perform a backup.
My current solution looks like this:
Procedure MySolution AS
Alter database Set Single_User
Perform back up of database
perform backup of logs
alter database Set Multi_User
End Procedure
Create Job
Call Procedure Mysolution
Schedule Job 6hrs
"TheSQLGuru" wrote:

> What is the justification for this attempt to "lock the database into
> single-user mode for a specific user"?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
>
>
|||Hi
"mj" wrote:
[vbcol=seagreen]
> Thanks John and SQLGuRu for the interest;
>
> More Background Details:
> The remote user has developed an extensive DTS package.
> When the transformations have been completed, then the transformed
> data is sent to a central server.
> For this reason I can not just go get the data.
> To ensure the migration is completed without interuption, I need to put the
> server into single user mode.
> To preserve the data prior to the migration, I need to perform a backup.
> My current solution looks like this:
> ----
> Procedure MySolution AS
> Alter database Set Single_User
> Perform back up of database
> perform backup of logs
> alter database Set Multi_User
> End Procedure
> Create Job
> Call Procedure Mysolution
> Schedule Job 6hrs
> ----
>
> "TheSQLGuru" wrote:
I can only assume that this is not a system with users, as a period of
unavailability every 6 hours is not the sort of service level that would be
classed as acceptable!!
Who do you need to load this every 6 hours?
Have you considered replication?
If you have a proper sequence of log backups you may not need to do the full
backup if you accept that you will need to restore the last full backup and
roll forward to return to the state prior to your process running.
Alternatively a differential backup would remove the need to restore some of
the log backups.
If you set the database into single user mode, then you can not guarantee
getting access to the database (for that single user) in subsequent job steps.
What do you do with the files that the process has transferred?
John

schedule job to set database to single user mode

Task Summary:
I have a remote user to sending records every 5 minutes by DTS package.
I think the best way to handle this is to create a job that will up the
database
into single user mode, set the remote user to the single user.
allow the job to run 10 to 15 minutes then take it out of single user mode.
Task Break down:
1:
Put the database into single user mode.
assign myself as the user.
(How do I define which user has access?)
2:
Backup the database
unassign myself as the single user
assign remote user as the single user
3:
Scheduled the job to execute for 15 minutes
4:
take database out of single user mode
5:
Schedule job to execute evey 6 hours.Hi
"mj" wrote:

> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user mode
.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
You can not assign another user to be the single user in single user mode!
If you are not connected to the database when it is set in single user mode
then potentially anyone could get in and be that single user.
I am not sure why the remote user is send things every 5 minutes and you
will only want him to send something for a 15 minute period every 6 hours.
What may be a better scenario is that every 6 hours you fetch the informatio
n
from the remote location and then processes it in one batch, then the proces
s
is controlled by the server. A different approach you be to have a second
database that the user updates (local or remote) and then use replication to
merge the differences. With SQL 2005 service broker may possibly be useful.
John|||What is the justification for this attempt to "lock the database into
single-user mode for a specific user"'
TheSQLGuru
President
Indicium Resources, Inc.
"mj" <mj@.discussions.microsoft.com> wrote in message
news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user
> mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
>|||Thanks John and SQLGuRu for the interest;
More Background Details:
The remote user has developed an extensive DTS package.
When the transformations have been completed, then the transformed
data is sent to a central server.
For this reason I can not just go get the data.
To ensure the migration is completed without interuption, I need to put the
server into single user mode.
To preserve the data prior to the migration, I need to perform a backup.
My current solution looks like this:
----
--
Procedure MySolution AS
Alter database Set Single_User
Perform back up of database
perform backup of logs
alter database Set Multi_User
End Procedure
Create Job
Call Procedure Mysolution
Schedule Job 6hrs
----
--
"TheSQLGuru" wrote:

> What is the justification for this attempt to "lock the database into
> single-user mode for a specific user"'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
>
>|||Hi
"mj" wrote:
[vbcol=seagreen]
> Thanks John and SQLGuRu for the interest;
>
> More Background Details:
> The remote user has developed an extensive DTS package.
> When the transformations have been completed, then the transformed
> data is sent to a central server.
> For this reason I can not just go get the data.
> To ensure the migration is completed without interuption, I need to put th
e
> server into single user mode.
> To preserve the data prior to the migration, I need to perform a backup.
> My current solution looks like this:
> ----
--
> Procedure MySolution AS
> Alter database Set Single_User
> Perform back up of database
> perform backup of logs
> alter database Set Multi_User
> End Procedure
> Create Job
> Call Procedure Mysolution
> Schedule Job 6hrs
> ----
--
>
> "TheSQLGuru" wrote:
>
I can only assume that this is not a system with users, as a period of
unavailability every 6 hours is not the sort of service level that would be
classed as acceptable!!
Who do you need to load this every 6 hours?
Have you considered replication?
If you have a proper sequence of log backups you may not need to do the full
backup if you accept that you will need to restore the last full backup and
roll forward to return to the state prior to your process running.
Alternatively a differential backup would remove the need to restore some of
the log backups.
If you set the database into single user mode, then you can not guarantee
getting access to the database (for that single user) in subsequent job step
s.
What do you do with the files that the process has transferred?
John

schedule job to set database to single user mode

Task Summary:
I have a remote user to sending records every 5 minutes by DTS package.
I think the best way to handle this is to create a job that will up the
database
into single user mode, set the remote user to the single user.
allow the job to run 10 to 15 minutes then take it out of single user mode.
Task Break down:
1:
Put the database into single user mode.
assign myself as the user.
(How do I define which user has access?)
2:
Backup the database
unassign myself as the single user
assign remote user as the single user
3:
Scheduled the job to execute for 15 minutes
4:
take database out of single user mode
5:
Schedule job to execute evey 6 hours.Hi
"mj" wrote:
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
You can not assign another user to be the single user in single user mode!
If you are not connected to the database when it is set in single user mode
then potentially anyone could get in and be that single user.
I am not sure why the remote user is send things every 5 minutes and you
will only want him to send something for a 15 minute period every 6 hours.
What may be a better scenario is that every 6 hours you fetch the information
from the remote location and then processes it in one batch, then the process
is controlled by the server. A different approach you be to have a second
database that the user updates (local or remote) and then use replication to
merge the differences. With SQL 2005 service broker may possibly be useful.
John|||What is the justification for this attempt to "lock the database into
single-user mode for a specific user"'
--
TheSQLGuru
President
Indicium Resources, Inc.
"mj" <mj@.discussions.microsoft.com> wrote in message
news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user
> mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
>|||Thanks John and SQLGuRu for the interest;
More Background Details:
The remote user has developed an extensive DTS package.
When the transformations have been completed, then the transformed
data is sent to a central server.
For this reason I can not just go get the data.
To ensure the migration is completed without interuption, I need to put the
server into single user mode.
To preserve the data prior to the migration, I need to perform a backup.
My current solution looks like this:
----
Procedure MySolution AS
Alter database Set Single_User
Perform back up of database
perform backup of logs
alter database Set Multi_User
End Procedure
Create Job
Call Procedure Mysolution
Schedule Job 6hrs
----
"TheSQLGuru" wrote:
> What is the justification for this attempt to "lock the database into
> single-user mode for a specific user"'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> >
> > Task Summary:
> > I have a remote user to sending records every 5 minutes by DTS package.
> > I think the best way to handle this is to create a job that will up the
> > database
> > into single user mode, set the remote user to the single user.
> > allow the job to run 10 to 15 minutes then take it out of single user
> > mode.
> >
> >
> > Task Break down:
> >
> > 1:
> > Put the database into single user mode.
> > assign myself as the user.
> > (How do I define which user has access?)
> >
> > 2:
> > Backup the database
> >
> > unassign myself as the single user
> >
> > assign remote user as the single user
> >
> > 3:
> >
> > Scheduled the job to execute for 15 minutes
> >
> > 4:
> > take database out of single user mode
> >
> > 5:
> > Schedule job to execute evey 6 hours.
> >
> >
> >
> >
>
>|||Hi
"mj" wrote:
> Thanks John and SQLGuRu for the interest;
>
> More Background Details:
> The remote user has developed an extensive DTS package.
> When the transformations have been completed, then the transformed
> data is sent to a central server.
> For this reason I can not just go get the data.
> To ensure the migration is completed without interuption, I need to put the
> server into single user mode.
> To preserve the data prior to the migration, I need to perform a backup.
> My current solution looks like this:
> ----
> Procedure MySolution AS
> Alter database Set Single_User
> Perform back up of database
> perform backup of logs
> alter database Set Multi_User
> End Procedure
> Create Job
> Call Procedure Mysolution
> Schedule Job 6hrs
> ----
>
> "TheSQLGuru" wrote:
> > What is the justification for this attempt to "lock the database into
> > single-user mode for a specific user"'
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "mj" <mj@.discussions.microsoft.com> wrote in message
> > news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> > >
> > > Task Summary:
> > > I have a remote user to sending records every 5 minutes by DTS package.
> > > I think the best way to handle this is to create a job that will up the
> > > database
> > > into single user mode, set the remote user to the single user.
> > > allow the job to run 10 to 15 minutes then take it out of single user
> > > mode.
> > >
> > >
> > > Task Break down:
> > >
> > > 1:
> > > Put the database into single user mode.
> > > assign myself as the user.
> > > (How do I define which user has access?)
> > >
> > > 2:
> > > Backup the database
> > >
> > > unassign myself as the single user
> > >
> > > assign remote user as the single user
> > >
> > > 3:
> > >
> > > Scheduled the job to execute for 15 minutes
> > >
> > > 4:
> > > take database out of single user mode
> > >
> > > 5:
> > > Schedule job to execute evey 6 hours.
I can only assume that this is not a system with users, as a period of
unavailability every 6 hours is not the sort of service level that would be
classed as acceptable!!
Who do you need to load this every 6 hours?
Have you considered replication?
If you have a proper sequence of log backups you may not need to do the full
backup if you accept that you will need to restore the last full backup and
roll forward to return to the state prior to your process running.
Alternatively a differential backup would remove the need to restore some of
the log backups.
If you set the database into single user mode, then you can not guarantee
getting access to the database (for that single user) in subsequent job steps.
What do you do with the files that the process has transferred?
John