Disabling a Step While a Job is Running

Hi,
Kind of a weird question. If I have a SQL job that runs for a long
time and while it is running I realize I want to skip one of the steps
which is still to come, can I update the job while it's running to
make it's On Success action either be, Stop the Job Reporting Success
or Go To Step X? Or does the job essentially get "loaded" when it
starts and no changes will take effect until the next run?
Thanks,
BillMy gut says no, but you could test it easilly by setting job steps with
'WAITFOR' .
"zinck74" <bkelly3@.gmail.com> wrote in message
news:1190238399.358816.128400@.k35g2000prh.googlegroups.com...
> Hi,
> Kind of a weird question. If I have a SQL job that runs for a long
> time and while it is running I realize I want to skip one of the steps
> which is still to come, can I update the job while it's running to
> make it's On Success action either be, Stop the Job Reporting Success
> or Go To Step X? Or does the job essentially get "loaded" when it
> starts and no changes will take effect until the next run?
> Thanks,
> Bill
>

Disable Job Steps

Hi
I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
packages. Each package is run as a separate step in the job. I am busy
testing the job and want to skip some of the longer running packages for now.
Is there a way to disable a job step and then enable it again when I am
ready. Deleting the step and recreating it again is one option, but there
must be a better way
ThanksTake a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e158802c-c347-4a5d-bf75-c03e5ae56e6b.htm
"Trevor Howe" <TrevorHowe@.discussions.microsoft.com> wrote in message
news:13A6603B-003D-4BD8-805F-F37C8AC23A6F@.microsoft.com...
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for
> now.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks|||Trevor Howe wrote:
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for now.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks
On each job step you have to option to decide what to do on "Success"
and on "Failure". Here you can choose to go to certain step in the job
on e.g. "Success".
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks guys. That was fast!! I also found the solution about five minutes
after I sent out the original post. Would'nt it be nice if all responses were
as successful and quick as this one. Now for my next mind bender: What is the
first name of the founder of Microsoft - :0) - Jokes
""Steen Schlüter Persson (DK)"" wrote:
> Trevor Howe wrote:
> > Hi
> >
> > I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> > packages. Each package is run as a separate step in the job. I am busy
> > testing the job and want to skip some of the longer running packages for now.
> > Is there a way to disable a job step and then enable it again when I am
> > ready. Deleting the step and recreating it again is one option, but there
> > must be a better way
> >
> > Thanks
> On each job step you have to option to decide what to do on "Success"
> and on "Failure". Here you can choose to go to certain step in the job
> on e.g. "Success".
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Disable Job Steps

Hi
I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
packages. Each package is run as a separate step in the job. I am busy
testing the job and want to skip some of the longer running packages for now
.
Is there a way to disable a job step and then enable it again when I am
ready. Deleting the step and recreating it again is one option, but there
must be a better way
ThanksTake a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e158802c-c347-4a5d-bf75-
c03e5ae56e6b.htm
"Trevor Howe" <TrevorHowe@.discussions.microsoft.com> wrote in message
news:13A6603B-003D-4BD8-805F-F37C8AC23A6F@.microsoft.com...
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for
> now.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks|||Trevor Howe wrote:
> Hi
> I have a job setup in SQL Server Agent (SQL 2005) that runs multiple
> packages. Each package is run as a separate step in the job. I am busy
> testing the job and want to skip some of the longer running packages for n
ow.
> Is there a way to disable a job step and then enable it again when I am
> ready. Deleting the step and recreating it again is one option, but there
> must be a better way
> Thanks
On each job step you have to option to decide what to do on "Success"
and on "Failure". Here you can choose to go to certain step in the job
on e.g. "Success".
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks guys. That was fast!! I also found the solution about five minutes
after I sent out the original post. Would'nt it be nice if all responses wer
e
as successful and quick as this one. Now for my next mind bender: What is th
e
first name of the founder of Microsoft - :0) - Jokes
""Steen Schlüter Persson (DK)"" wrote:

> Trevor Howe wrote:
> On each job step you have to option to decide what to do on "Success"
> and on "Failure". Here you can choose to go to certain step in the job
> on e.g. "Success".
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

disable job

I'm using SQL server 2000
when i disable or delete a job, it returns
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."Win
Examine MSDB..sysjobs system table. Probably there is ni match a server name
in originating_server column
"Win" <aaa@.aaa.com> wrote in message
news:%23Br9haUUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> I'm using SQL server 2000
> when i disable or delete a job, it returns
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
>
>
>

Disable a task in the current package programmatically?

Hi,

I have a package comprising a number of Data Flow Task steps, to import various tables of data from some Access databases into SQL Server. The name of some of these Access databases will change depending on the date, e.g. last year's data is currently in a database called "2005data.mdb". At year end this will be superseded by "2006data.mdb". The Access databases are within a 3rd party system so I have no control over the file names.

I have a Script Task that checks the current date, and changes the name of an Access database in the connection string to reflect last year's date. But to complicate matters, last year's file might not exist.

So the Script Task checks whether the file name exists. If not, I would like to disable the Task that uses this connection. But how do I reference a task within the package that contains my Script Task, by name, to set the Disabled attribute to true?

I want to do something like CurrentPackage.Tasks("MyDataFlowTask").Disabled = True.

I would also welcome alternative suggestions for how to achieve this.

Many thanks,

Keith.

You cannot reference inwards within a package, so you cannot reference a task. The task must reference the value. The way to do this is to set a variable, and use a property expression that uses that variable value. So you would set a variable, DisableMyTask as true to false in the script then set the property expression on the task's Disable property to that variable, "@.DisableMyTask".

I would however be careful using the Disable property, as the evaluation can be done before you set the variable, so it is too late. A better way to do this is to use an expression on Workflow. Have a constraint leading to your task, and then just add the variable as the test, or rather the negative, so that the constraint is only satisfied when the variable is false. If you have no preceding task to anchor the constraint, just use a sequence container. It will act as an anchor, but do nothing else.

|||

Keith,

You can also add an Expression to the constraint from your Script Task that is modifying the connection string. By doing so you can control the flow without having to enable/disable the invalid tasks.

Here's a link on how to add expressions to a precendence constraint: http://msdn2.microsoft.com/en-us/library/ms140040.aspx

Cheers,
Patrik

|||

Hi,

Thanks for the replies. I have tried adding expressions to the precedence constraints, but it is not working correctly.

I originally had 7 Tasks, with a simple precedence constraint of Success between one and the next. I've now added an expression based on a variable, and changed the precedence constraint to "Constraint and Expression", but with this change, steps 5 to 7 no longer execute. They are still validated, but do not Start.

In simplistic terms, the logic I have added is:

After Task 3, if Success and (@.boolPCStatsLastYearExists) then Execute Task 4.

After Task 4, if Success, execute Task 5.

After Task 3, if Success and (@.boolPCStatsLastYearExists = False) then Execute Task 5.

So, whether the boolean value @.boolPCStatsLastYearExists is True or False, Task 5 should be executed. In fact I think it is set to True, because Task 4 executes and creates rows in the database, so it appears Task 4 is executing but Task 5 isn't.

I am developing on my local machine, but cannot run locally (I get an error on the first Data Flow Task), because the SQL database is on a server. So I am deploying to the server and running there.

Can you advise how I should get debugging information to indicate what happens after Task 4 executes, and why Task 5 isn't reached?

Many thanks,

Keith.

|||

To debug stuff, use the debugger. Creating a mock DB on your local machine would be a good idea.

Sounds like you have two constraints leading to task 5, but they follow different, mutually exclusive execution paths. So you will never satisfy both constraints in a single execution, correct? If so, have you turned the Or logic option on for those constsraints? It is in the constraint UI, or the LogicalAnd property of the constraint, set it to false.

|||

Hi again,

Thanks for that. I had left the default setting (AND) on the constraints. Changing all the constraints where 2 constraints affect one task, led to some progress.

Tasks 1 - 6 now execute fine. But Task 7 never executes.

The constraints on it are:

From Task 5, Success AND @.boolPCFDR1LastYearExists = False

OR

From Task 6, Success.

The first of these conditions should be met, as Task 5 succeeds and writes data, and the boolean is set to False by Task 1 (the Script task), which I can verify in the debugger.

I would greatly appreciate any further suggestions. However, I suspect I will now need to install SQL Server on my already overloaded laptop and recreate the server database locally, so that I can debug. But having looked at the debugger, I am not confident I will have a sufficiently granular view to identify the problem. I'll report back!

Thanks for all your help so far,

Keith.

|||

Hi,

OK, I've installed SQL Server locally, copied the database to it, and changed my connection to point to the local database. The same error occurs, and I don't see how to use the debugger to help me resolve it.

I can put a stop in Step 5 Post-Execute, but I can't do any more than verify that the boolean is set to False as expected. What can I do to identify why the processor does not proceed to execute Step 7?

Thanks,

Keith.

|||

Solved! (Sort of). I've had a problem with conditions on a precedence constraint. I can set a condition of @.myBooleanVariable, and it executes correctly, only when the variable is set to true. However, I can't find a syntax to say execute when this boolean is False. I tried the obvious NOT @.myBooleanVariable, but this does not pass the syntax check. @.myBooleanVariable = False passes the syntax check, but crucially, does not execute. I've not found another syntax that is accepted.

So reluctantly, I created 2 new variables, set them to the boolean opposite of the 2 existing variables, and used them in my "negative" conditions. So all my conditions on Precedence Constraints now comprise just the name of a boolean variable, prefixed with @..

Thanks,

Keith.

|||

Keith,

In the expressions not is represented with ! and equivalency is tested with ==. This should help you remove the additional variable.

Cheers,
Patrik

Disable a job schedule

Hi,
I have a job with three steps and four schedule time per day.
I schedule a job to disable one of schedule time, such as 5:00PM schedule.
The job is still enable, but this schedule time show enabled =NO before
5:00PM. However, the 5:00PM schedule still run regardless schedule
enabled=NO.
Is this flag in sysjobschedules play any role for enable and disable a
schedule? Or, I have to disable and enable from the job level (sysjobs).
Please advise.
Best Regards,
Lynn
Hmm, I disable job in the job level (sysjobs) at 4:45PM and the 5:00PM
schedule is enabled. And, the job still run at 5:00PM even though the job is
disabled. I don't understand what's wrong in my procedure. From the
Enterprise Manager, I see the job was disabled.
Looks like either way (from job level or schedule level) won't work.
Please help.
Best Regards,
Lynn
"Lynn" wrote:

> Hi,
> I have a job with three steps and four schedule time per day.
> I schedule a job to disable one of schedule time, such as 5:00PM schedule.
> The job is still enable, but this schedule time show enabled =NO before
> 5:00PM. However, the 5:00PM schedule still run regardless schedule
> enabled=NO.
> Is this flag in sysjobschedules play any role for enable and disable a
> schedule? Or, I have to disable and enable from the job level (sysjobs).
> Please advise.
> --
> Best Regards,
> Lynn