I'm looking for a simple way to run a Maintenance Plan's backup job from a
DTS package. I know I can use the sp_Start_Job, but this requires a jobname
or jobID. To get those I would need to parse through the output from a
sp_Help-Job & look for 'Backup' & 'databasename'.

Also, I want to use the backup job from my Maintenance Plan, so that if I or
another DBA needs to perform a recovery, all of the backup files are in the
same location with the sme naming conventions.

Thanks

Is there some reason you would need to dunamically dtermine the job name?
Can you not just hard code it ++ you cannot just start any old job, you have
to have permissions.

From BOL

Execute permissions default to the public role in the msdb database. A user
who can execute this procedure and is a member of the sysadmin fixed role
can start any job. A user who is not a member of the sysadmin role can use
sp_start_job to start only the jobs he/she owns.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Sam" <Sam (AT) discussions (DOT) microsoft.com> wrote

Yes I could hard code the jobname, but I was looking for something a little
more dynamic that would allow me to more easily deploy the package to
multiple servers & hopefully allow me to specify the UserID/Passwword to run
the job.

Sam
"Allan Mitchell" wrote:

Can't do that. You do not get the option to "Run As". If you schedule your
DTS package then you can have the Account under which it runs have perms to
do the job running.

How do you propose to know that the backup job you have selected comes from
your maint plan? I can backup databases without using main plans
Which maint plan? You may have one for logs and one for some databases,
others for other databases.


In msdb you have

dbo.sysdbmaintplan_jobs which will give you the Guids of the Job and the
Plan
dbo.sysdbmaintplans will give you name of the plans matched on GUIDs
dbo.sysdbmaintplan_databases will give you the name(s) of the databases upon
which your plan operates.

At no point do I see an action type though.


You still need to know the name of the plan though.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Sam" <Sam (AT) discussions (DOT) microsoft.com> wrote

Thanks. I'll buid the package with hardcoded job names & schedule it for
nightly execution, well before my regular maintenance plan. In the package
I'll control the execution of the backup & maintenace steps with Global
variable, as you describe them at SQLDTS.com. The global variables will be
built by a user request via an ASP page & destryoyed at the end of the
package. Since this job will run as the SQL Agent I should not have any
permission problems with the BackupStep.

Sam

"Allan Mitchell" wrote: