Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RitaBowman
Starting Member
10 Posts |
 Posted - 2005-07-11 : 08:12:18
|
[purple][/ purple]I have a DTS package which I want to start only on Mondays once another DTS package completes successfully. So within the primary package I have setup an Execute SQL task that contains the following:If DATEPART(dw, GETDATE()) = 2BeginUse MSDBEXEC msdb.dbo.sp_start_job @job_name = 'Sharp Weekly Reports', @server_name="(local)"EndWhen I parse the query, everything is fine, when I execute the query in Query Analyser, again everything is fine. But in the DTS package, the step fails withStep Error Description:The specified @job_name ('Sharp Weekly Reports') does not exist. (Microsoft OLE DB Provider for SQL Server (0): Changed database context to 'msdb'.)Step Error code: 80040E14I cut and pasted the name directly from sysjobs so I know the job exists, the SQL Service Agent account owns the job so there should not be a permissions issue.I've googled the error, but can't find anything that appears to be related. Any ideas?Regards. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-07-11 : 19:39:21
|
| Is your task in the package connected to the server which has the job?Is the step connecting as sa? A user won't be able to execute a job it doesn't own.What you're doing sounds a bit odd. If you want to control the execution from the scheduler why not run both packages as two steps. In the second step put the test before the dtsrun.If you want to control from the opackage why not execute the second package rather than start a job.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
sp_Start_Job