Conditionally skip or jump steps in a job

Is it possible to conditionally skip or jump steps in a job without failing
a
step?
For example I don’t want to run a step if the are no records in a table.
I also need to loop based on a condition.About loops:
http://www.sqldts.com/default.aspx?246
About conditional execution:
http://www.sqldts.com/default.aspx?218
http://www.sqldts.com/default.aspx?214
Chris
"Dave" wrote:

> Is it possible to conditionally skip or jump steps in a job without failin
g a
> step?
> For example I don’t want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>|||You could place your (IF.. BEGIN.. END) condition within the T-SQL or stored
procedure beging called.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:27AF395F-A67D-43E6-9F40-009ACCCB0877@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
> failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>sqlsql

conditionally execute a subreport

I have a report that contains several subreports, one of which is meant to be
displayed only when the subject of the report has a particular job title. The
data source for this subreport is a stored procedure, and in that procedure I
examine the job title and execute the code only for the specified job title,
but SQL Server goes ahead and opens all of the tables anyway, increasing
overhead on a report that already takes several hours to run.
A better solution would be to conditionally execute the subreport. Is there
a way to do that?
thanks, bethMy understanding of Reporting Services is that it is not meant to work that
way. This is not a client program; it is a server platform. There is
therefore no event-driven programming paradigm like you get in Access. It is
by design.
If you want to do the kind of thing you are talking about, you will need to
change your design. You can design two separate reports, or you can write
your own client program to conditionally load reports.
There is a sample demo of how to integrate reports in your application on
http://www.microsoft.com/sql/reporting. It shows how to use a browser control
in a Windows application.
sorry if this is not the answer you wanted.
Charles Kangai, MCT, MCDBA
"beth" wrote:
> I have a report that contains several subreports, one of which is meant to be
> displayed only when the subject of the report has a particular job title. The
> data source for this subreport is a stored procedure, and in that procedure I
> examine the job title and execute the code only for the specified job title,
> but SQL Server goes ahead and opens all of the tables anyway, increasing
> overhead on a report that already takes several hours to run.
> A better solution would be to conditionally execute the subreport. Is there
> a way to do that?
> thanks, beth|||Charles,
I will persue the alternatives you suggested - thank you.
I don't understand your point about client versus server platforms.
If I can conditionally toggle visibility of an object, why could I not toggle
say, an enable/disable property, of a subreport? If this is by design though,
I guess that's that.
I could try placing another stored procedure up front that examines the job
title, that will then conditionally execute the stored procedure that opens
all the
tables and collects the data; using this method, I could at least stop SQL
Server from opening tables it is not going to use. Not sure that will work
though.
thank you for your reply Charles...beth
"Charles Kangai" wrote:
> My understanding of Reporting Services is that it is not meant to work that
> way. This is not a client program; it is a server platform. There is
> therefore no event-driven programming paradigm like you get in Access. It is
> by design.
> If you want to do the kind of thing you are talking about, you will need to
> change your design. You can design two separate reports, or you can write
> your own client program to conditionally load reports.
> There is a sample demo of how to integrate reports in your application on
> http://www.microsoft.com/sql/reporting. It shows how to use a browser control
> in a Windows application.
> sorry if this is not the answer you wanted.
> Charles Kangai, MCT, MCDBA

Conditional SQL Ajent Job execution

I have a job set to execute every 3 minutes. Only one instance of the
job should be running at any given time. If execution time exceeds 3
minutes - job should not start. Currently I have a flag in a
user-defined table that is set to 1 by the SP that is called by the
job when it begins, and reset back to 0 when it ends. The SP checks
the value of the flag, if it is currently = 1 then the SP exits.
The problem is that if the SP fails half-way through the flag is never
reset back to 0, thus preventing subsequent executions.
Is there a way to set the job to execute conditionally based on
whether or not another instance of same job is already running?I will probably add check the sysjobhistory to verify that
the job didn't fail:
select top 2 *
from sysjobhistory
where job_id = (select job_id from sysjobs
where name = <'Job Name'>)
order by run_date, convert(smalldatetime, (case len
(run_time)
when 1 then '00'+':'+'00'+':'+'0'+ right
(run_time,2)
when 2 then '00'+':'+'00'+':'+right
(run_time,2)
when 3 then '00'+':'+'0'+left(convert(char
(3), run_time), 1)+':'+right(run_time,2)
when 4 then '00'+':'+left(convert(char(4),
run_time), 2)+':'+right(run_time,2)
when 5 then '0'+left(convert(char(5),
run_time), 1)+':'+substring(convert(char(5), run_time), 2,
2)+':'+right(run_time,2)
when 6 then left(convert(char(6),
run_time), 2)+':'+substring(convert(char(6), run_time), 3,
2)+':'+right(run_time,2)
END)) desc
If the job failed, it should continue with the schedule:
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I have a job set to execute every 3 minutes. Only one
instance of the
>job should be running at any given time. If execution
time exceeds 3
>minutes - job should not start. Currently I have a flag
in a
>user-defined table that is set to 1 by the SP that is
called by the
>job when it begins, and reset back to 0 when it ends.
The SP checks
>the value of the flag, if it is currently = 1 then the SP
exits.
>The problem is that if the SP fails half-way through the
flag is never
>reset back to 0, thus preventing subsequent executions.
>Is there a way to set the job to execute conditionally
based on
>whether or not another instance of same job is already
running?
>.
>|||You shouldn't need such flags etc. Agent will not start a job if it is still executing (from last
time).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alexey Aksyonenko" <Alexey.Aksyonenko@.coanetwork.com> wrote in message
news:1449e414.0309300914.2af4241@.posting.google.com...
> I have a job set to execute every 3 minutes. Only one instance of the
> job should be running at any given time. If execution time exceeds 3
> minutes - job should not start. Currently I have a flag in a
> user-defined table that is set to 1 by the SP that is called by the
> job when it begins, and reset back to 0 when it ends. The SP checks
> the value of the flag, if it is currently = 1 then the SP exits.
> The problem is that if the SP fails half-way through the flag is never
> reset back to 0, thus preventing subsequent executions.
> Is there a way to set the job to execute conditionally based on
> whether or not another instance of same job is already running?

conditional execution of the next job in DTS

I have a job which exports and emails the data from a table (subject to some conditions) . The data is exported to a test file. I donot want to send the email if there are no rows exported. or the filesieze is 0. Otherwsie I want to send the email with this text file as attachement.

Any ideas?

Thanks
RaguYou can turn steps on or off. See the topic titled "Using ActiveX Scripts in a DTS Workflow" in BOL.

Also the topic "Using ActiveX Scripts in DTS" may be helpful.

Phil|||activex in dts is single threaded in dts and slows all kinds of stuff down.
try using any built in task first if you can.
if you want to try something trick, try the mesage queue task.
or
you can provide if logic for rows returned in an execute sql task and if successfull have the on success run an xpcmdshell from the sql task/
or try a data driven query task ++++ place if logic in the query
if rows returned then transfer to text
if no rows returned then raiserror

Books Online {Data Driven Query Task}
Books Online {Execute SQL Task}
Books Online {Message Queue Task}
Books Online {DTS tasks, overview}|||Phil/Ruprect: Thanks for your replies. I was using logic in sql (Execute SQL Task) to initiate the next step but the table has grown to 15 M rows. I plan to use activex script to just check the size of the output file (Thanks to sqldts.com site for the script) and accordingly initiate the next job.

Thanks again for the replies.

Ragu

Concurrent stored procs?

Hi all

I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.

What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)

(I am trying not to use a table to store whether the stored proc is running or not)

Thanks in advance.

RochanaOne can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Thanks Hans for quick reply.

I tried it but found some problems.
sp_getapplock needs an active transaction, without which it fails.
I am trying not to use Transactions because it slows down the system so badly.

Originally posted by HansVE
One can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||It works to some extent Hans.
The error checking never happens. It quits the process without displaying the error.
..
IF @.@.error <> 0
PRINT 'Proc already running'
...

Originally posted by HansVE
Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||Yes, the error is too serious to continue. If you cannot trap that error at the client, you could instead check for existence of the table object.

IF OBJECT_ID('tempdb.dbo.##proc_running') > 0|||sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..|||I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..

One problem with this is that two people could still start the proc simultaneously.

Hans.|||Enigma here is the scenario:
Say, the job is scheduled at 12noon everyday.
One of the bosses come and asks us to run that particular job becos they need to see the data on their screen. So one of the programmers of the team runs that job, or invoke the particular sp.
The sp takes about 10-15 mins to complete.
If it reaches 12noon while that particular sp is running, job kicks in and invoke that same sp again. (our sp is still running)
Thats why I want to check if the particular sp is already running or not, so that the sp wont run again.

Originally posted by Enigma
I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Thanks Jonte..
Your suggestion worked !! ;)
I have to check the Event Info for my stored proc, thats running in the server.

I have pasted code to test what processes are currently running on the server on a db. May be you can run and see the results too

Thanks

declare @.spid bigint
declare crsr cursor read_only
for
select spid from master..sysprocesses where dbid>1 and kpid>1

open crsr
fetch next from crsr
into @.spid

while @.@.fetch_status<>-1
begin
dbcc inputbuffer(@.spid)
fetch next from crsr
into @.spid

end

close crsr
deallocate crsr

Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..