Scheduling simple cdosys email task does not work

I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?

'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")

with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update

.Subject = "Notification"
.From = "donotreply@.test.com"
.TextBody = "TEST"
.Bcc = "someone@.test.com"
.Send
end with
'-

Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.

Any help would be appreciated.

Almost certainly permissions. Here's the KB article on this subject:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

Is your SMTP server configured to allow anonymous sending, or to allow the account being used by Agent?

-Doug

|||

Thanks for the article.
We did try method 1 (SQL Agent proxy account). We also set method 2 for serverstorage protection level. We don't have any sensitive information so we skipped method 3 (Encrypt Sensitive with password). As for method 4, we do have package configuration files that have been set as well.

Also, our SMTP server allows anonymous connections.

However, since we are trying to execute activex script tasks using CDOSYS, we're assuming perhaps there is some security permissions issue we overlooked with "component services" perhaps? We also tried adding our SQL agent account (as well as proxy account) into our local admins group, but without any luck. It still runs only manually, but fails for CDOSYS when scheduled.

Scheduling simple cdosys email task does not work

I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?

'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")

with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update

.Subject = "Notification"
.From = "donotreply@.test.com"
.TextBody = "TEST"
.Bcc = "someone@.test.com"
.Send
end with
'-

Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.

Any help would be appreciated.

Almost certainly permissions. Here's the KB article on this subject:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

Is your SMTP server configured to allow anonymous sending, or to allow the account being used by Agent?

-Doug

|||

Thanks for the article.
We did try method 1 (SQL Agent proxy account). We also set method 2 for serverstorage protection level. We don't have any sensitive information so we skipped method 3 (Encrypt Sensitive with password). As for method 4, we do have package configuration files that have been set as well.

Also, our SMTP server allows anonymous connections.

However, since we are trying to execute activex script tasks using CDOSYS, we're assuming perhaps there is some security permissions issue we overlooked with "component services" perhaps? We also tried adding our SQL agent account (as well as proxy account) into our local admins group, but without any luck. It still runs only manually, but fails for CDOSYS when scheduled.

Scheduling Reports

How are reporting services reports scheduled to run and email? I don't see
any links in the SQL Server Business Intelligence Development Studio. Are
there plug ins or something else from microsoft that could be missing? I have
SQL Server 2005 SP1 running on my PC (windows 2003). Any step by step
documentation?Reports are scheduled through Report Manager... Go to
http://<yourserver>/reports
Then select a report.
You should see a subscriptions tab, where you can schedule.
To schedule a report, the data source MUST use Credentials Stored Securely
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"dbach" wrote:
> How are reporting services reports scheduled to run and email? I don't see
> any links in the SQL Server Business Intelligence Development Studio. Are
> there plug ins or something else from microsoft that could be missing? I have
> SQL Server 2005 SP1 running on my PC (windows 2003). Any step by step
> documentation?
>

Scheduling Montly Report

Hi all,

I currently have a report which needs to be scheduled to run on every 1st of the month to get records of last month. For example, on Feb. 01 the report will get all records by the date range of Jan. 01 to Jan. 31. My current report does not have any defined report parameter. It gets all the date range it finds from the table.

Please advise how can I start with this.

Any help is much appreciated! Thanks!

Hi,

Couldnt get it completely.

you want to run a report on monthly basis, then you need to have two datetime parameters i.e. from_time and to_time, then you want default value to be last months dates, use expression for the defualt value....

your expr. for "from time" should be like

=switch(month(now())=1,"01/01/2007",

month(now())=2,"02/01/2007, and in same way you can have it for to time frame....

HTH

Priyank

|||

Hi

As i understand the requirement over here is to have a report which shows the data for previous month

say the report is executed on mar 1st then the report should display data till feb28th correct?

For this a report has to be created with 2 parameters Startdate and EndDate.

However subscription can be created in Report Manager.

Regards

Smitha

|||

You don't need a param for this, although to give you an perfectly appropriate example it would be good to know if you are calling a stored procedure or writing the query directly in the report, or what.

Let's say you are writing the query directly in the report, not a stored procedure.

You already know that you can schedule it to run on the first of the month, right? Here is a query that will do what you want, given that you know you are running it every month on the first:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,GETDATE()-1,<YOUR DATE COLUMN>) = 0

' the default behavior of the date subtraction is by days, see?

' and you're running the subscription on the first of the month,

' so this works.

Let's make it better. Here is a query that will do what you want, no matter what date of the month it is run on, which is : provide the results for the month *before* the month in which it is run:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,GETDATE()),

<YOUR DATE COLUMN>) = 0

Now, if you prefer, you can add a parameter and give your parameter the default value of Today (you may have to do some CASTing in here, not checking this):

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,@.YourParam),

<YOUR DATE COLUMN>) = 0

... hope this helps,

>L<

|||

Thank you all for the helpful solutions.

Yes basically it is just to get a report of previous month when it is scheduled to run.

|||

Lisa that's a cool little function!

Only question I have it whether it would work if you had data for more than one year in the table? i.e If you had 2 rows, one with July 2006 and one with July 2007 wouldn't the DateDiff(month, etc) return 0 for that even though the year is different?

As my own $0.02 I usually use something like

Beginning of last month

DATEADD(month,DATEDIFF(month,0,getdate)-1,0)

Beginning of this month

DATEADD(month,DATEDIFF(month,0,getdate),0)

Beginning of next month

DATEADD(month,DATEDIFF(month,0,getdate)+1,0)

which is sort of based off similar logic. Found it on a forum, and loved it. Can also do days/weeks by just switching the period name.

|||

>> whether it would work if you had data for more than one year in the table

It will actually work fine, D, check it out:

select datediff(month, '1/1/2007','1/1/2004')

will return -36 even though both dates are (obviously) in January. 3 * 12.

It's a *difference of months*. Not a difference of *month numbers* <s>.

>L<

|||Right you are - I should have run it through Query Analyzer first

Scheduling Montly Report

Hi all,

I currently have a report which needs to be scheduled to run on every 1st of the month to get records of last month. For example, on Feb. 01 the report will get all records by the date range of Jan. 01 to Jan. 31. My current report does not have any defined report parameter. It gets all the date range it finds from the table.

Please advise how can I start with this.

Any help is much appreciated! Thanks!

Hi,

Couldnt get it completely.

you want to run a report on monthly basis, then you need to have two datetime parameters i.e. from_time and to_time, then you want default value to be last months dates, use expression for the defualt value....

your expr. for "from time" should be like

=switch(month(now())=1,"01/01/2007",

month(now())=2,"02/01/2007, and in same way you can have it for to time frame....

HTH

Priyank

|||

Hi

As i understand the requirement over here is to have a report which shows the data for previous month

say the report is executed on mar 1st then the report should display data till feb28th correct?

For this a report has to be created with 2 parameters Startdate and EndDate.

However subscription can be created in Report Manager.

Regards

Smitha

|||

You don't need a param for this, although to give you an perfectly appropriate example it would be good to know if you are calling a stored procedure or writing the query directly in the report, or what.

Let's say you are writing the query directly in the report, not a stored procedure.

You already know that you can schedule it to run on the first of the month, right? Here is a query that will do what you want, given that you know you are running it every month on the first:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,GETDATE()-1,<YOUR DATE COLUMN>) = 0

' the default behavior of the date subtraction is by days, see?

' and you're running the subscription on the first of the month,

' so this works.

Let's make it better. Here is a query that will do what you want, no matter what date of the month it is run on, which is : provide the results for the month *before* the month in which it is run:

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,GETDATE()),

<YOUR DATE COLUMN>) = 0

Now, if you prefer, you can add a parameter and give your parameter the default value of Today (you may have to do some CASTing in here, not checking this):

Code Snippet

select <WHATEVER> from <YOUR TABLE> where

DATEDIFF(month,DATEADD(month,-1,@.YourParam),

<YOUR DATE COLUMN>) = 0

... hope this helps,

>L<

|||

Thank you all for the helpful solutions.

Yes basically it is just to get a report of previous month when it is scheduled to run.

|||

Lisa that's a cool little function!

Only question I have it whether it would work if you had data for more than one year in the table? i.e If you had 2 rows, one with July 2006 and one with July 2007 wouldn't the DateDiff(month, etc) return 0 for that even though the year is different?

As my own $0.02 I usually use something like

Beginning of last month

DATEADD(month,DATEDIFF(month,0,getdate)-1,0)

Beginning of this month

DATEADD(month,DATEDIFF(month,0,getdate),0)

Beginning of next month

DATEADD(month,DATEDIFF(month,0,getdate)+1,0)

which is sort of based off similar logic. Found it on a forum, and loved it. Can also do days/weeks by just switching the period name.

|||

>> whether it would work if you had data for more than one year in the table

It will actually work fine, D, check it out:

select datediff(month, '1/1/2007','1/1/2004')

will return -36 even though both dates are (obviously) in January. 3 * 12.

It's a *difference of months*. Not a difference of *month numbers* <s>.

>L<

|||Right you are - I should have run it through Query Analyzer first

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 job in sql server 2005

Hi,

I created a scheduled job in sql server 2005 which takes a daily backup of database.

My problem is this job does not run automatically.but when i execute it manually it runs on that instance.

do i need to activate any servrice to have scheduling of jobs running perfectly on my machine ?

Please guide me on this.

Thanks,

Prashant

The SQL Agent Server must be running (Check in Services) and have OS permission to the backup file location.

The Job must be 'Enabled'.

The Schedule must be 'Enabled'

Scheduling enable tick box not working

Hi all,
I have a simple question, as follows.
I have set up a couple of jobs which I have scheduled to
run around the same time each night.
One of the jobs works fine, but the other does not run, I
have lloked into the problem and found that the tick box
which enables scheduling, unchecks it's self each night.
Is this possible and how can I stop it.
Both jobs were set-up in exactly the same manner.
Cheers.Hi,
Is that job scheduled to run "One Time".
Check that by looking in to the "schedule" property of that job. If it is
the case make the job "Recurring"
Thanks
Hari
MCDBA
"Gazzer J" <anonymous@.discussions.microsoft.com> wrote in message
news:f99901c43e39$fc06ab50$a001280a@.phx.gbl...
> Hi all,
> I have a simple question, as follows.
> I have set up a couple of jobs which I have scheduled to
> run around the same time each night.
> One of the jobs works fine, but the other does not run, I
> have lloked into the problem and found that the tick box
> which enables scheduling, unchecks it's self each night.
> Is this possible and how can I stop it.
> Both jobs were set-up in exactly the same manner.
> Cheers.|||There is a bug that can cause something like this. Do you
have any warnings in the SQL Agent log along the lines of
Possible date calculation spin for Schedule nn
If you have these warnings make sure you are on the latest
service pack. You may also want to try adding a minute or
two to the scheduled time as this can often correct the
problems with the data calculation "confusion" that SQL
Agent sometimes experiences.
-Sue
On Thu, 20 May 2004 00:13:43 -0700, "Gazzer J"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have a simple question, as follows.
>I have set up a couple of jobs which I have scheduled to
>run around the same time each night.
>One of the jobs works fine, but the other does not run, I
>have lloked into the problem and found that the tick box
>which enables scheduling, unchecks it's self each night.
>Is this possible and how can I stop it.
>Both jobs were set-up in exactly the same manner.
>Cheers.

Scheduling enable tick box not working

Hi all,
I have a simple question, as follows.
I have set up a couple of jobs which I have scheduled to
run around the same time each night.
One of the jobs works fine, but the other does not run, I
have lloked into the problem and found that the tick box
which enables scheduling, unchecks it's self each night.
Is this possible and how can I stop it.
Both jobs were set-up in exactly the same manner.
Cheers.Hi,
Is that job scheduled to run "One Time".
Check that by looking in to the "schedule" property of that job. If it is
the case make the job "Recurring"
Thanks
Hari
MCDBA
"Gazzer J" <anonymous@.discussions.microsoft.com> wrote in message
news:f99901c43e39$fc06ab50$a001280a@.phx.gbl...
> Hi all,
> I have a simple question, as follows.
> I have set up a couple of jobs which I have scheduled to
> run around the same time each night.
> One of the jobs works fine, but the other does not run, I
> have lloked into the problem and found that the tick box
> which enables scheduling, unchecks it's self each night.
> Is this possible and how can I stop it.
> Both jobs were set-up in exactly the same manner.
> Cheers.|||There is a bug that can cause something like this. Do you
have any warnings in the SQL Agent log along the lines of
Possible date calculation spin for Schedule nn
If you have these warnings make sure you are on the latest
service pack. You may also want to try adding a minute or
two to the scheduled time as this can often correct the
problems with the data calculation "confusion" that SQL
Agent sometimes experiences.
-Sue
On Thu, 20 May 2004 00:13:43 -0700, "Gazzer J"
<anonymous@.discussions.microsoft.com> wrote:
>Hi all,
>I have a simple question, as follows.
>I have set up a couple of jobs which I have scheduled to
>run around the same time each night.
>One of the jobs works fine, but the other does not run, I
>have lloked into the problem and found that the tick box
>which enables scheduling, unchecks it's self each night.
>Is this possible and how can I stop it.
>Both jobs were set-up in exactly the same manner.
>Cheers.

Scheduling enable tick box not working

Hi all,
I have a simple question, as follows.
I have set up a couple of jobs which I have scheduled to
run around the same time each night.
One of the jobs works fine, but the other does not run, I
have lloked into the problem and found that the tick box
which enables scheduling, unchecks it's self each night.
Is this possible and how can I stop it.
Both jobs were set-up in exactly the same manner.
Cheers.
Hi,
Is that job scheduled to run "One Time".
Check that by looking in to the "schedule" property of that job. If it is
the case make the job "Recurring"
Thanks
Hari
MCDBA
"Gazzer J" <anonymous@.discussions.microsoft.com> wrote in message
news:f99901c43e39$fc06ab50$a001280a@.phx.gbl...
> Hi all,
> I have a simple question, as follows.
> I have set up a couple of jobs which I have scheduled to
> run around the same time each night.
> One of the jobs works fine, but the other does not run, I
> have lloked into the problem and found that the tick box
> which enables scheduling, unchecks it's self each night.
> Is this possible and how can I stop it.
> Both jobs were set-up in exactly the same manner.
> Cheers.
|||There is a bug that can cause something like this. Do you
have any warnings in the SQL Agent log along the lines of
Possible date calculation spin for Schedule nn
If you have these warnings make sure you are on the latest
service pack. You may also want to try adding a minute or
two to the scheduled time as this can often correct the
problems with the data calculation "confusion" that SQL
Agent sometimes experiences.
-Sue
On Thu, 20 May 2004 00:13:43 -0700, "Gazzer J"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have a simple question, as follows.
>I have set up a couple of jobs which I have scheduled to
>run around the same time each night.
>One of the jobs works fine, but the other does not run, I
>have lloked into the problem and found that the tick box
>which enables scheduling, unchecks it's self each night.
>Is this possible and how can I stop it.
>Both jobs were set-up in exactly the same manner.
>Cheers.

Scheduling DTS Package using a stored procedure problem

The DTS package runs fine through Enterprise manager successfully.
However, when scheduled through a job that runs the dts through the
following code:

DECLARE @.findfile int
Exec @.findfile = master.dbo.xp_cmdShell 'dir
\\ServerName\folder\filename.xls', no_output
IF (@.findfile=0)
BEGIN
Exec master.dbo.xp_cmdshell 'dtsrun -E -Server\Instance
-N"DataImport"'
END

The servername specified in the above statement in a different server
than the server that the package resides on.

This is the error that I get when I try to run the same code using
query analyzer:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147024893
(80070003)
Error string: The system cannot find the path specified.

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100

Error Detail Records:

Error: -2147024893 (80070003); Provider Error: 0 (0)
Error string: The system cannot find the path specified.

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100

Error: -2147024893 (80070003); Provider Error: 0 (0)
Error string: Cannot open a log file of specified name. The system
cannot find the path specified.

Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4700

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

Error: -2147220440 (80040428); Provider Error: 0 (0)
Error string: Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700

NULL

The job is owned by the SQLService account(Windows account) that has
System Admin rights and also is part of the Domain Admin User group.
The Domain Admin User group has full rights on the file that the DTS is
trying to access.

Any help in trying to figure out why the schedule job cannot find the
file path would be appreciated.

Thanks

KRI found the problem was the path in the log file : was using a mapped
drive vs a UNC path.

Scheduling DTS package that executes other DTS packages

Hey guys...

We have a particular process in place that uses many different DTS packages that have to be scheduled. We can successfully run a DTS package from EM. Furthermore, we can successfully schedule a DTS package and run it successfully from SQLServerAgent. And finally... I can create a DTS package that executes other DTS packages and execute it successfully from EM.

Our problem is when I attempt to schedule a DTS package that executes other packages I get an error that says:

Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713. Process Exit Code 1. The step failed.

I've seen all over the place issues regarding the inability to schedule a DTS package... but we are able to do this. This is just limited to those scheduled packages that have an "Execute Package Task" in them.

Thanks in advance...I dont know wich version of server you got..this ansver goes for 7.0 (might also work on 2000).

You can add an "Execute process task" in the package that should execute other packages (one for each package).
It should run DTSrun.exe, the parameters you need is /S ServerName /U Login /P Password /N PackageName

Works for me!|||I am having the same issue as we speak. It seems that I have the SQL 2000 client tools installed on my WS, but the server is running SQL 7 with SQL 7 client tools. There are some new tasks shipped with SQL 2000. The one I am having the issue with is dynamic properties task. When I open the package on SQL 7, I get an error message that says "Invalid Class String", this is caused becauase the server does not have the same tasks or clients tools installed.

Microsofts workaround is to install the SQL 2000 client tools on the server.

I am looking for a workaround without installing SQL 2000 tools in production.

Any Thoughts?

SHICKS

Scheduling DBCC DBReindex

How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
I've got backups scheduled nightly, but don't know how to schedule something
like this. I've got 7 tables that need to be reindexed and defragged every
Saturday night.
Tips/Help/etc appreciated.
Thanks!
YsandreJust create a TSQL job step which includes your DBCC DBREINDEX or DBCC INDEX
DEFRAG commands. The commands are
documented in Books Online. You also want to read below excellent article ab
ut defragmentation. :-)
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <ysandre@.hotmail.com> wrote in message news:317B96C9-15E6-46A7-972E-7DA32C7DEB21@.m
icrosoft.com...
> How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
> I've got backups scheduled nightly, but don't know how to schedule something like
this. I've got 7 tables
that need to be reindexed and defragged every Saturday night.
> Tips/Help/etc appreciated.
> Thanks!
> Ysandre|||Thanks for the article.
I know the commands for DBReindex and indexDefrag.
What I don't know is how to schedule it so it kicks off at, say, 9pm tonight
, without me having to be here (physically or remotely).
I'm fairly familiar with NT Scheduler, and I have scheduled backups of the S
QL database running nightly.
How do I create a job/task/process that kicks off the DBCC DBReindex at a sp
ecific time, either daily or weekly?
I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
Help appreciated!
Thanks . . .|||How do you schedule the backups? I hope you don't backup just the database f
iles... You should use the TSQL
BACKUP DATABASE and possibly the BACKUP LOG command to do SQL Server backups
.
Anyhow, you can use the NT Scheduler and use OSQL.EXE with the /i option to
specify a script file containing
the TSQL commands you want to execute.
Most dba's, however, use SQL Server's scheduler for these things: SQL Server
Agent. In Enterprise Manager,
Management, SQL Server Agent, Jobs, create a new job, add a TSQL job step wh
ich contains your TSQL commands.
To get fancy, I also suggest you specify an output file for the jobsteps, an
d then add a couple of finishing
job steps to send email on success/failure. Use xp_smtp_sendmail for the email (w.SQLDev.Net" target="_blank">ww
w.SQLDev.Net) and include
the output file in the email. See p." target="_blank">http://www.karaszi.com/sqlserver/in...pi.as
p.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <anonymous@.discussions.microsoft.com> wrote in message
news:9387A1F6-A6C3-49B3-A939-0E983AEC2256@.microsoft.com...
> Thanks for the article.
> I know the commands for DBReindex and indexDefrag.
> What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, with
out me having to be here
(physically or remotely).
> I'm fairly familiar with NT Scheduler, and I have scheduled backups of the
SQL database running nightly.
> How do I create a job/task/process that kicks off the DBCC DBReindex at a specific
time, either daily or
weekly?
> I'm not familiary with TSQL, and BOL is not giving me what I'm looking for
.
> Help appreciated!
> Thanks . . .|||yes this is exactly how I do it.
I just reindex every night in a SQL Agent Job.
the job has the following steps.
1. run indexDefrag on all indexes that I have decided need to be in my
maintenance list (Details would bore you)
2. After the index step has ran (either successfully or unsuccessfully) I
run the full backup
I want to make sure that the two steps above are not running at the same
time....
hope this, along with Tibors post, helps
Greg Jackson
PDX, Oregon|||Thank you! That (and Tibor's post) were exactly what I needed.

Scheduling DBCC DBReindex

How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
I've got backups scheduled nightly, but don't know how to schedule something like this. I've got 7 tables that need to be reindexed and defragged every Saturday night.
Tips/Help/etc appreciated.
Thanks!
Ysandre
Just create a TSQL job step which includes your DBCC DBREINDEX or DBCC INDEXDEFRAG commands. The commands are
documented in Books Online. You also want to read below excellent article abut defragmentation. :-)
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <ysandre@.hotmail.com> wrote in message news:317B96C9-15E6-46A7-972E-7DA32C7DEB21@.microsoft.com...
> How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
> I've got backups scheduled nightly, but don't know how to schedule something like this. I've got 7 tables
that need to be reindexed and defragged every Saturday night.
> Tips/Help/etc appreciated.
> Thanks!
> Ysandre
|||Thanks for the article.
I know the commands for DBReindex and indexDefrag.
What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, without me having to be here (physically or remotely).
I'm fairly familiar with NT Scheduler, and I have scheduled backups of the SQL database running nightly.
How do I create a job/task/process that kicks off the DBCC DBReindex at a specific time, either daily or weekly?
I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
Help appreciated!
Thanks . . .
|||How do you schedule the backups? I hope you don't backup just the database files... You should use the TSQL
BACKUP DATABASE and possibly the BACKUP LOG command to do SQL Server backups.
Anyhow, you can use the NT Scheduler and use OSQL.EXE with the /i option to specify a script file containing
the TSQL commands you want to execute.
Most dba's, however, use SQL Server's scheduler for these things: SQL Server Agent. In Enterprise Manager,
Management, SQL Server Agent, Jobs, create a new job, add a TSQL job step which contains your TSQL commands.
To get fancy, I also suggest you specify an output file for the jobsteps, and then add a couple of finishing
job steps to send email on success/failure. Use xp_smtp_sendmail for the email (www.SQLDev.Net) and include
the output file in the email. See http://www.karaszi.com/sqlserver/info_no_mapi.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <anonymous@.discussions.microsoft.com> wrote in message
news:9387A1F6-A6C3-49B3-A939-0E983AEC2256@.microsoft.com...
> Thanks for the article.
> I know the commands for DBReindex and indexDefrag.
> What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, without me having to be here
(physically or remotely).
> I'm fairly familiar with NT Scheduler, and I have scheduled backups of the SQL database running nightly.
> How do I create a job/task/process that kicks off the DBCC DBReindex at a specific time, either daily or
weekly?
> I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
> Help appreciated!
> Thanks . . .
|||yes this is exactly how I do it.
I just reindex every night in a SQL Agent Job.
the job has the following steps.
1. run indexDefrag on all indexes that I have decided need to be in my
maintenance list (Details would bore you)
2. After the index step has ran (either successfully or unsuccessfully) I
run the full backup
I want to make sure that the two steps above are not running at the same
time....
hope this, along with Tibors post, helps
Greg Jackson
PDX, Oregon
|||Thank you! That (and Tibor's post) were exactly what I needed.

Scheduling an sp

Hi All,
I hope I can explain this well.
I have an sp that populates certain tables with historical data and I've
scheduled
a job (using EM) that runs this sp on the first day of each quarter.
My question is, within the sp, if anything goes wrong, I do a rollback and
issue
a Return(5). Will this be interpeted as a failure within the job? Somehow I
think not.
So, how do I indicate failure to the job that schedules the sp?
DanDan,
Add a RAISERROR statement to your stored procedure.
HTH
Jerry
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I hope I can explain this well.
> I have an sp that populates certain tables with historical data and I've
> scheduled
> a job (using EM) that runs this sp on the first day of each quarter.
> My question is, within the sp, if anything goes wrong, I do a rollback and
> issue
> a Return(5). Will this be interpeted as a failure within the job? Somehow
> I think not.
> So, how do I indicate failure to the job that schedules the sp?
>
> Dan
>
>|||Thanks Jerry
Dan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7haHMfxFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Dan,
> Add a RAISERROR statement to your stored procedure.
> HTH
> Jerry
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Hi All,
>> I hope I can explain this well.
>> I have an sp that populates certain tables with historical data and I've
>> scheduled
>> a job (using EM) that runs this sp on the first day of each quarter.
>> My question is, within the sp, if anything goes wrong, I do a rollback
>> and issue
>> a Return(5). Will this be interpeted as a failure within the job? Somehow
>> I think not.
>> So, how do I indicate failure to the job that schedules the sp?
>>
>> Dan
>>
>sql

Scheduling a job more frequently than every minute

I have a SQL Agent job that is scheduled to run every minute. Works like a
charm, however, my client would like it to run more frequently, like every 15
seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
equally sure it can be done. I just don't know the right verbiage or
commands to use or how to execute it. Any help would be greatly appreciated.
Thanks in advance.
d
http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
Above is for 2000. I haven't tried it on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>I have a SQL Agent job that is scheduled to run every minute. Works like a
> charm, however, my client would like it to run more frequently, like every 15
> seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
> equally sure it can be done. I just don't know the right verbiage or
> commands to use or how to execute it. Any help would be greatly appreciated.
> Thanks in advance.
>
> d
|||Thanks!!!!
"Tibor Karaszi" wrote:

> http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
> Above is for 2000. I haven't tried it on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
> news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>
>

Scheduling a job more frequently than every minute

I have a SQL Agent job that is scheduled to run every minute. Works like a
charm, however, my client would like it to run more frequently, like every 1
5
seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
equally sure it can be done. I just don't know the right verbiage or
commands to use or how to execute it. Any help would be greatly appreciated
.
Thanks in advance.
dhttp://sqldev.net/sqlagent/SQLAgent...gJobsInSecs.htm
Above is for 2000. I haven't tried it on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>I have a SQL Agent job that is scheduled to run every minute. Works like a
> charm, however, my client would like it to run more frequently, like every
15
> seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I
'm
> equally sure it can be done. I just don't know the right verbiage or
> commands to use or how to execute it. Any help would be greatly appreciat
ed.
> Thanks in advance.
>
> d|||Thanks!!!!
"Tibor Karaszi" wrote:

> http://sqldev.net/sqlagent/SQLAgent...gJobsInSecs.htm
> Above is for 2000. I haven't tried it on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
> news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>
>

Scheduling a job more frequently than every minute

I have a SQL Agent job that is scheduled to run every minute. Works like a
charm, however, my client would like it to run more frequently, like every 15
seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
equally sure it can be done. I just don't know the right verbiage or
commands to use or how to execute it. Any help would be greatly appreciated.
Thanks in advance.
dhttp://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
Above is for 2000. I haven't tried it on 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
>I have a SQL Agent job that is scheduled to run every minute. Works like a
> charm, however, my client would like it to run more frequently, like every 15
> seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
> equally sure it can be done. I just don't know the right verbiage or
> commands to use or how to execute it. Any help would be greatly appreciated.
> Thanks in advance.
>
> d|||Thanks!!!!
"Tibor Karaszi" wrote:
> http://sqldev.net/sqlagent/SQLAgentRecuringJobsInSecs.htm
> Above is for 2000. I haven't tried it on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darlene Mack" <DarleneMack@.discussions.microsoft.com> wrote in message
> news:0A8AD7BE-245F-4912-8FC5-9BDE45D389A1@.microsoft.com...
> >I have a SQL Agent job that is scheduled to run every minute. Works like a
> > charm, however, my client would like it to run more frequently, like every 15
> > seconds. I'm pretty sure this cannot be scheduled in the SQL Agent, but I'm
> > equally sure it can be done. I just don't know the right verbiage or
> > commands to use or how to execute it. Any help would be greatly appreciated.
> >
> > Thanks in advance.
> >
> >
> > d
>
>

Scheduling a DTS

I have created a DTS Import routine and scheduled it to run every hour.
I started the agent but despite that, the schedule never seems to work.
I am forced to run it manually.
Any Ideas how I can get it to work ?Could it be it's still running, or has it run but failed.
In EnterPrise Manager :- Management
SQL Server Agent
Jobs
If you right click your job and 'Show History' has it ever ran..?
Also ensure Enabled = 'Yes' and Schedule = 'Yes'
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> I have created a DTS Import routine and scheduled it to run every hour.
> I started the agent but despite that, the schedule never seems to work.
> I am forced to run it manually.
> Any Ideas how I can get it to work ?
>
>|||" Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
Where do I access the Enabled and the Shedule ?
Sorry for being ignorant but I have never done this before.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> Could it be it's still running, or has it run but failed.
> In EnterPrise Manager :- Management
> SQL Server Agent
> Jobs
> If you right click your job and 'Show History' has it ever ran..?
> Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > I have created a DTS Import routine and scheduled it to run every hour.
> > I started the agent but despite that, the schedule never seems to work.
> > I am forced to run it manually.
> >
> > Any Ideas how I can get it to work ?
> >
> >
> >
>|||Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
Scheduled.
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> Where do I access the Enabled and the Shedule ?
> Sorry for being ignorant but I have never done this before.
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Could it be it's still running, or has it run but failed.
> >
> > In EnterPrise Manager :- Management
> > SQL Server Agent
> > Jobs
> >
> > If you right click your job and 'Show History' has it ever ran..?
> >
> > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > I have created a DTS Import routine and scheduled it to run every
hour.
> > > I started the agent but despite that, the schedule never seems to
work.
> > > I am forced to run it manually.
> > >
> > > Any Ideas how I can get it to work ?
> > >
> > >
> > >
> >
> >
>|||Sorry.
I can see it now.
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> Where do I access the Enabled and the Shedule ?
> Sorry for being ignorant but I have never done this before.
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > Could it be it's still running, or has it run but failed.
> >
> > In EnterPrise Manager :- Management
> > SQL Server Agent
> > Jobs
> >
> > If you right click your job and 'Show History' has it ever ran..?
> >
> > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > I have created a DTS Import routine and scheduled it to run every
hour.
> > > I started the agent but despite that, the schedule never seems to
work.
> > > I am forced to run it manually.
> > >
> > > Any Ideas how I can get it to work ?
> > >
> > >
> > >
> >
> >
>|||No problem, if your new to the SQL Server Agent this may be of use :-
http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Sorry.
> I can see it now.
>
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> >
> > Where do I access the Enabled and the Shedule ?
> > Sorry for being ignorant but I have never done this before.
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > Could it be it's still running, or has it run but failed.
> > >
> > > In EnterPrise Manager :- Management
> > > SQL Server Agent
> > > Jobs
> > >
> > > If you right click your job and 'Show History' has it ever ran..?
> > >
> > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > I have created a DTS Import routine and scheduled it to run every
> hour.
> > > > I started the agent but despite that, the schedule never seems to
> work.
> > > > I am forced to run it manually.
> > > >
> > > > Any Ideas how I can get it to work ?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||None of my DTS services appear in jobs.
What am I doing wrong.
I thought that after creating a schedule event for a dts it would appear as
a job ?
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Oz4SPMUpDHA.1672@.TK2MSFTNGP09.phx.gbl...
> Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
> Scheduled.
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> >
> > Where do I access the Enabled and the Shedule ?
> > Sorry for being ignorant but I have never done this before.
> >
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > Could it be it's still running, or has it run but failed.
> > >
> > > In EnterPrise Manager :- Management
> > > SQL Server Agent
> > > Jobs
> > >
> > > If you right click your job and 'Show History' has it ever ran..?
> > >
> > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > I have created a DTS Import routine and scheduled it to run every
> hour.
> > > > I started the agent but despite that, the schedule never seems to
> work.
> > > > I am forced to run it manually.
> > > >
> > > > Any Ideas how I can get it to work ?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Right click on Jobs and Choose Refresh
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:e2BLOQUpDHA.3320@.tk2msftngp13.phx.gbl...
> None of my DTS services appear in jobs.
> What am I doing wrong.
> I thought that after creating a schedule event for a dts it would appear
as
> a job ?
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:Oz4SPMUpDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > Whilst viewing the Jobs window, 2 of the columns are entitled Enabled,
> > Scheduled.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > >
> > > Where do I access the Enabled and the Shedule ?
> > > Sorry for being ignorant but I have never done this before.
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > Could it be it's still running, or has it run but failed.
> > > >
> > > > In EnterPrise Manager :- Management
> > > > SQL Server Agent
> > > > Jobs
> > > >
> > > > If you right click your job and 'Show History' has it ever ran..?
> > > >
> > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > I have created a DTS Import routine and scheduled it to run every
> > hour.
> > > > > I started the agent but despite that, the schedule never seems to
> > work.
> > > > > I am forced to run it manually.
> > > > >
> > > > > Any Ideas how I can get it to work ?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Ryan.
I might have learned a little but I still canr figure out how to run a DTS
from a job.
Its driving me nuts.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eL8KFPUpDHA.2488@.TK2MSFTNGP12.phx.gbl...
> No problem, if your new to the SQL Server Agent this may be of use :-
> http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > Sorry.
> > I can see it now.
> >
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > >
> > > Where do I access the Enabled and the Shedule ?
> > > Sorry for being ignorant but I have never done this before.
> > >
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > Could it be it's still running, or has it run but failed.
> > > >
> > > > In EnterPrise Manager :- Management
> > > > SQL Server Agent
> > > > Jobs
> > > >
> > > > If you right click your job and 'Show History' has it ever ran..?
> > > >
> > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > I have created a DTS Import routine and scheduled it to run every
> > hour.
> > > > > I started the agent but despite that, the schedule never seems to
> > work.
> > > > > I am forced to run it manually.
> > > > >
> > > > > Any Ideas how I can get it to work ?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Another good resource at :- www.sqldts.com
--
HTH
Ryan Waight, MCDBA, MCSE
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:OI84bhUpDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Thanks Ryan.
> I might have learned a little but I still canr figure out how to run a DTS
> from a job.
> Its driving me nuts.
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:eL8KFPUpDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > No problem, if your new to the SQL Server Agent this may be of use :-
> >
> > http://www.sqlpass.org/news/02feb/index.cfm?passnews=DBA1
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > news:ua218MUpDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > Sorry.
> > > I can see it now.
> > >
> > >
> > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > news:uJdjHJUpDHA.3256@.tk2msftngp13.phx.gbl...
> > > > " Also ensure Enabled = 'Yes' and Schedule = 'Yes' "
> > > >
> > > > Where do I access the Enabled and the Shedule ?
> > > > Sorry for being ignorant but I have never done this before.
> > > >
> > > >
> > > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > > news:Oi$fOCUpDHA.2868@.TK2MSFTNGP09.phx.gbl...
> > > > > Could it be it's still running, or has it run but failed.
> > > > >
> > > > > In EnterPrise Manager :- Management
> > > > > SQL Server Agent
> > > > > Jobs
> > > > >
> > > > > If you right click your job and 'Show History' has it ever ran..?
> > > > >
> > > > > Also ensure Enabled = 'Yes' and Schedule = 'Yes'
> > > > >
> > > > > --
> > > > > HTH
> > > > > Ryan Waight, MCDBA, MCSE
> > > > >
> > > > > "Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
> > > > > news:%230hc$$TpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> > > > > > I have created a DTS Import routine and scheduled it to run
every
> > > hour.
> > > > > > I started the agent but despite that, the schedule never seems
to
> > > work.
> > > > > > I am forced to run it manually.
> > > > > >
> > > > > > Any Ideas how I can get it to work ?
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>sql

Scheduler Mystery

I have a scheduled job that executes sql to dump tran with no log. I have i
t
running at night so no users are affected. The next morning the job says it
succeded with no errors accounted for. However I have checked the size of
the ldf and it is huge. Our server space is getting smaller and smaller
every day, so I am suspect that the log file is not being truncated. When I
checked the SQL server event viewer, it shows an Error application event whe
n
the truncate process is started. Can anyone tell me why the scheduled job
shows a successfull run, while the event viewer tells another story?What version of SQL Server?

>I have a scheduled job that executes sql to dump tran with no log.
As of 7.0, we use BACKUP instead of DUMP, but DUMP is still allowed. Anyhow,
this doesn't shrink
physical file size. Are you doing regular transaction log backups? If nor, u
se simple recovery
model. If you are, don't just cut the log as it will break the log backup se
quence.
As for shrinking the file size, I've listed a number of articles in this art
icle:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Toco" <Toco@.discussions.microsoft.com> wrote in message
news:723A119C-5D64-4C36-9865-9BD8FBAF2775@.microsoft.com...
>I have a scheduled job that executes sql to dump tran with no log. I have
it
> running at night so no users are affected. The next morning the job says
it
> succeded with no errors accounted for. However I have checked the size of
> the ldf and it is huge. Our server space is getting smaller and smaller
> every day, so I am suspect that the log file is not being truncated. When
I
> checked the SQL server event viewer, it shows an Error application event w
hen
> the truncate process is started. Can anyone tell me why the scheduled job
> shows a successfull run, while the event viewer tells another story?