SQL Jobs problem - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Jobs problem

Author

Topic

sameerv
Starting Member

29 Posts

Posted - 2002-10-16 : 04:25:45

Hi Guys,

How do I access the name of a SQL job in a Job Step of the same job.
i.e.

I create a job named "TestJob"
I create a job step named "TestJobStep" belonging to the "TestJob" job.
In the "TestJobStep" job step, I wish to declare a variable and store the value "TestJob" (which is the name of the parent job) in this variable.

Can this be done?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-16 : 06:31:53

I would just code this in the script that crates the job.
When you create the job the job name is fixed so you can hard code it.

==========================================
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.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-16 : 06:31:54

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-16 : 08:34:41


declare @jobname sysname

select @jobname = name
from msdb.dbo.sysjobs (nolock)
where job_id=[JOBID]

You can ignore the parse error - [JOBID] is a token that gets replaced at runtime.

HTH
Jasper Smith

sameerv
Starting Member

29 Posts

Posted - 2002-10-16 : 10:10:14

quote:

I would just code this in the script that crates the job.
When you create the job the job name is fixed so you can hard code it.

==========================================
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.

I will be generating the job dynamically from ASP using SQL-DMO and the job name will also be dynamically assigned.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-16 : 10:15:09

quote:
I will be generating the job dynamically from ASP using SQL-DMO and the job name will also be dynamically assigned.

Why?

sameerv
Starting Member

29 Posts

Posted - 2002-10-16 : 11:20:41

quote:

quote:
I will be generating the job dynamically from ASP using SQL-DMO and the job name will also be dynamically assigned.
Why?

This is related to a problem I had posted earlier.(see topic fire a trigger from asp).
Just to recap..
I need to execute an sp from an ASP page. Since the sp takes a long time to execute I need to execute the sp from a job and use sp_start_job from ASP page to start the job.
But the sp requires 2 parameters from the ASP page which, I presume, cannot be passed alongwith the sp_start_job call.
So I will create a job dynamically from ASP whose name will be a concatenation of the 2 parameters.
In the job step I will read the parent job's name into a variable, split it and then call the sp passing these 2 values as parameters.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-16 : 11:40:05

I don't see any need to create a job or job step dynamically. You can set up a regular job and insert the variable information into a table, and have the job/procedure read the data from the table. You can then have the job DELETE the row(s) from the table so that it won't overlap another process.

Secondly, I think you should look at optimizing your procedure:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20696

You're using nested cursors to perform those tasks, and it's a wonder it works at all, no offense. It's like painting a brick wall one brick at a time: pull the brick out, paint it, put it back, repeat; I think you can see how much slower that would be than just painting the entire wall!

Mark's advice about using set-based operations is a really good point, and in all likelihood you can improve the performance so much that you don't even need to put this into a job at all. There's a really nice comment from someone in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=7149

That they changed an 8 HOUR CURSOR procedure into a 2 MINUTE set-based operation. That is a lot more typical than you might believe at first. You might want to do a forum search on "cursors" and read some of the comments. Also read this:

http://www.sqlteam.com/item.asp?ItemID=5761

Edited by - robvolk on 10/16/2002 11:42:15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources