SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


pass variables between SQL Server tasks in a JOB


pass variables between SQL Server tasks in a JOB

Author
Message
knight-794779
knight-794779
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 293
I'm using Sql Server 2005. I'm trying to pass a variable between the tasks in a job. NOT SSIS. We specifically want to stick with the sQL Agent jobs.



any suggestions?
SQL ORACLE
SQL ORACLE
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6407 Visits: 1314
How about the global variable?
knight-794779
knight-794779
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 293
global variable might work.....
but i'd still like to know if there is a standard for passing a variable between the tasks similar to how you pass in SSIS.
Marcin Gol
Marcin Gol
SSC Eights!
SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)

Group: General Forum Members
Points: 864 Visits: 178
maybe you can create a configuration table with proper schema? I think it can be better then global variables (easier to mange and more relational).
sgmunson
sgmunson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16299 Visits: 4621
I have to agree strongly that a table is a far better container for items between job steps when not using SSIS, and for the following reasons:

1.) It's easy to see within the job steps how different tasks get data from or to each other.

2.) It's a lot easier to see what happened if intermediate results are in a table when a job fails in the middle.

3.) It's much easier to document and explain, and someone coming in cold could probably quite easily figure it out without having to go to BOL or do research.

4.) With the right design, you might even be able to just rerun the entire job when it breaks, and it will figure out it's state from such a table.

Great Idea !!!

Steve
(aka smunson)
:-):-):-)

Marcin Gol [SQL Server MVP] (9/6/2009)
maybe you can create a configuration table with proper schema? I think it can be better then global variables (easier to mange and more relational).


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
knight-794779
knight-794779
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 293
yes, we have tables, but i'd really need to do it via variable. there's no way to pass a variable along that's not global???
sureshr69
sureshr69
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 81
Use the combination of sp_add_job_step and @command option with dynamic SQL. Have provisions for creating and dropping them(job steps) at runtime. It will be possible.


Thanks
Suresh Ramamurthy
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11510 Visits: 37411
knight (9/4/2009)
I'm trying to pass a variable between the tasks in a job.


Is the variable a result from an earlier task in the job or something else..perhaps a bit more detail may help us help you.

regards gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202711 Visits: 41943
SQL ORACLE (9/4/2009)
How about the global variable?


I know this is an old post and I might not get an answer from you but I have to ask... keeping in mind that SSIS/DTS is not being used for this particular task, what are you calling a "global variable" in this case? To the best of my knowledge, there is no such thing as a "global variable" either in the T-SQL world nor in the SQL Server Job world.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sgmunson
sgmunson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16299 Visits: 4621
The closest thing I can think of is a package variable with a scope that allows access across an entire package, and to refer to such as a global variable has at least some validity during package execution, at least at the level of scope of the package anyway. But to think of it as being global outside of that package wouldn't make any sense. You might consider a declared variable within a T-SQL script to be global, but it wouldn't be changeable from within a package that gets executed by the script, assuming that can be done, and that the variable is somehow passed to the package in some way (perhaps dynamic SQL ?), but then, that isn't really applicable to this problem in the first place. Maybe the person just figured a declared variable could be at least "thought of" as global, to the extent that once declared, it's available across the rest of the T-SQL script up to the next GO statement. Maybe it just depends on how they want to define the word global.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search