Scheduling SQL / ASP

Hi,
I've been asked to help with the automation of a nightly flat file
extraction of data from a SQL server (I think it's MS SQL 2000), i.e.
producing a text file containing specific data.
I have read previously in other news groups methods of scheduling ASP
pages/VB
scripts that could accomplish this task.
Is it possible to achieve this solely via the SQL server instead of using
ASP/VB scripts?
Thanks, Dominic
Check out Jobs in the BOL. Each job can have multiple steps and multiple
schedules. Each step can be VBScript or T-SQL or OS command or ...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dominic Marsat" <djmarsatAThotmail.com> wrote in message
news:eqXtuuGKEHA.2380@.TK2MSFTNGP09.phx.gbl...
Hi,
I've been asked to help with the automation of a nightly flat file
extraction of data from a SQL server (I think it's MS SQL 2000), i.e.
producing a text file containing specific data.
I have read previously in other news groups methods of scheduling ASP
pages/VB
scripts that could accomplish this task.
Is it possible to achieve this solely via the SQL server instead of using
ASP/VB scripts?
Thanks, Dominic

Scheduling SQL / ASP

Hi,
I've been asked to help with the automation of a nightly flat file
extraction of data from a SQL server (I think it's MS SQL 2000), i.e.
producing a text file containing specific data.
I have read previously in other news groups methods of scheduling ASP
pages/VB
scripts that could accomplish this task.
Is it possible to achieve this solely via the SQL server instead of using
ASP/VB scripts?
Thanks, DominicCheck out Jobs in the BOL. Each job can have multiple steps and multiple
schedules. Each step can be VBScript or T-SQL or OS command or ...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dominic Marsat" <djmarsatAThotmail.com> wrote in message
news:eqXtuuGKEHA.2380@.TK2MSFTNGP09.phx.gbl...
Hi,
I've been asked to help with the automation of a nightly flat file
extraction of data from a SQL server (I think it's MS SQL 2000), i.e.
producing a text file containing specific data.
I have read previously in other news groups methods of scheduling ASP
pages/VB
scripts that could accomplish this task.
Is it possible to achieve this solely via the SQL server instead of using
ASP/VB scripts?
Thanks, Dominicsql

Scheduling SQL / ASP

Hi,
I've been asked to help with the automation of a nightly flat file
extraction of data from a SQL server (I think it's MS SQL 2000), i.e.
producing a text file containing specific data.
I have read previously in other news groups methods of scheduling ASP
pages/VB
scripts that could accomplish this task.
Is it possible to achieve this solely via the SQL server instead of using
ASP/VB scripts?
Thanks, DominicCheck out Jobs in the BOL. Each job can have multiple steps and multiple
schedules. Each step can be VBScript or T-SQL or OS command or ...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dominic Marsat" <djmarsatAThotmail.com> wrote in message
news:eqXtuuGKEHA.2380@.TK2MSFTNGP09.phx.gbl...
Hi,
I've been asked to help with the automation of a nightly flat file
extraction of data from a SQL server (I think it's MS SQL 2000), i.e.
producing a text file containing specific data.
I have read previously in other news groups methods of scheduling ASP
pages/VB
scripts that could accomplish this task.
Is it possible to achieve this solely via the SQL server instead of using
ASP/VB scripts?
Thanks, Dominic

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.

Scheduled job "show step details" more info?

We have some very large DTS packages that are run nightly.
We they fail it is often difficult to diagnose because the error
details in Job History is truncated. Is this information written to a
log anywhere, or is there a setting to increase the amount of
information that is stored?
Or... am I stuck with logging each step.
Thanks for the help,
DGAny ideas? I havent been able to find any info on this. I know others
must be having the same problems.|||Any ideas? I havent been able to find any info on this. I know others
must be having the same problems.|||Any ideas? I havent been able to find any info on this. I know others
must be having the same problems.|||Hi
I believe the job step details are taken from the message column of the
sysjobhistory table which is limited to 1024 characters.
John
"Dave" wrote:

> Any ideas? I havent been able to find any info on this. I know others
> must be having the same problems.
>

Scheduled Backup Jobs Failing Sporadically

I have 2 types of SQL Server backup jobs that keep failing
sporadically. There are nightly full backups that fail and
there are hourly Transaction Log backups that fail
sporadically as well.
The nightly backups are scheduled for 2:00 a.m. I've just
changed the times and staggered them seconds apart. The
hourly Tlog backups run every hour during the day time.
The only error message I can find is:
Executed as user: KYEANTD\SQLAdmin. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.Executed
as user: KYEANTD\SQLAdmin. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
When a job fails I run it manually and the backup works.
These failures are only occuring on SQL Server 2000 SP3You need to specify a report file for your maint plan and look for error messages in that.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:046401c3c4e4$5e556c30$a301280a@.phx.gbl...
> I have 2 types of SQL Server backup jobs that keep failing
> sporadically. There are nightly full backups that fail and
> there are hourly Transaction Log backups that fail
> sporadically as well.
> The nightly backups are scheduled for 2:00 a.m. I've just
> changed the times and staggered them seconds apart. The
> hourly Tlog backups run every hour during the day time.
> The only error message I can find is:
> Executed as user: KYEANTD\SQLAdmin. sqlmaint.exe failed.
> [SQLSTATE 42000] (Error 22029). The step failed.Executed
> as user: KYEANTD\SQLAdmin. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> When a job fails I run it manually and the backup works.
> These failures are only occuring on SQL Server 2000 SP3|||I've found this error message:
Microsoft (R) SQLMaint Utility (Unicode), Version
[Microsoft SQL-DMO (ODBC SQLState: HYT00)] Error 0:
[Microsoft][ODBC SQL Server Driver]Timeout expired
>--Original Message--
>I have 2 types of SQL Server backup jobs that keep
failing
>sporadically. There are nightly full backups that fail
and
>there are hourly Transaction Log backups that fail
>sporadically as well.
>The nightly backups are scheduled for 2:00 a.m. I've just
>changed the times and staggered them seconds apart. The
>hourly Tlog backups run every hour during the day time.
>The only error message I can find is:
>Executed as user: KYEANTD\SQLAdmin. sqlmaint.exe failed.
>[SQLSTATE 42000] (Error 22029). The step failed.Executed
>as user: KYEANTD\SQLAdmin. sqlmaint.exe failed. [SQLSTATE
>42000] (Error 22029). The step failed.
>When a job fails I run it manually and the backup works.
>These failures are only occuring on SQL Server 2000 SP3
>.
>|||In the past I have found that SQLState42000 usually ment
the server was waiting on resources.
>--Original Message--
>I have 2 types of SQL Server backup jobs that keep
failing
>sporadically. There are nightly full backups that fail
and
>there are hourly Transaction Log backups that fail
>sporadically as well.
>The nightly backups are scheduled for 2:00 a.m. I've just
>changed the times and staggered them seconds apart. The
>hourly Tlog backups run every hour during the day time.
>The only error message I can find is:
>Executed as user: KYEANTD\SQLAdmin. sqlmaint.exe failed.
>[SQLSTATE 42000] (Error 22029). The step failed.Executed
>as user: KYEANTD\SQLAdmin. sqlmaint.exe failed. [SQLSTATE
>42000] (Error 22029). The step failed.
>When a job fails I run it manually and the backup works.
>These failures are only occuring on SQL Server 2000 SP3
>.
>|||Seems like sqlmaint.exe get a timeout during some operation. I can't say why that is, if it is a
long running backup (how big db, do you do backup to network) or some other operation...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:087701c3c57a$f6760ec0$a401280a@.phx.gbl...
> I've found this error message:
> Microsoft (R) SQLMaint Utility (Unicode), Version
> [Microsoft SQL-DMO (ODBC SQLState: HYT00)] Error 0:
> [Microsoft][ODBC SQL Server Driver]Timeout expired
>
> >--Original Message--
> >I have 2 types of SQL Server backup jobs that keep
> failing
> >sporadically. There are nightly full backups that fail
> and
> >there are hourly Transaction Log backups that fail
> >sporadically as well.
> >
> >The nightly backups are scheduled for 2:00 a.m. I've just
> >changed the times and staggered them seconds apart. The
> >hourly Tlog backups run every hour during the day time.
> >The only error message I can find is:
> >
> >Executed as user: KYEANTD\SQLAdmin. sqlmaint.exe failed.
> >[SQLSTATE 42000] (Error 22029). The step failed.Executed
> >as user: KYEANTD\SQLAdmin. sqlmaint.exe failed. [SQLSTATE
> >42000] (Error 22029). The step failed.
> >
> >When a job fails I run it manually and the backup works.
> >These failures are only occuring on SQL Server 2000 SP3
> >.
> >