Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

pass variables between SQL Server tasks in a JOB Expand / Collapse
Author
Message
Posted Friday, September 4, 2009 5:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:33 PM
Points: 8, 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?
Post #783219
Posted Friday, September 4, 2009 8:22 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
How about the global variable?
Post #783234
Posted Friday, September 4, 2009 8:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:33 PM
Points: 8, 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.

Post #783235
Posted Sunday, September 6, 2009 5:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 3, 2010 3:50 AM
Points: 146, 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).
Post #783425
Posted Monday, September 7, 2009 8:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 1,669, Visits: 2,221
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)

Internet ATM Machine
Post #783964
Posted Monday, September 7, 2009 9:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:33 PM
Points: 8, 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???
Post #783974
Posted Tuesday, September 8, 2009 5:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 10, 2012 12:13 PM
Points: 11, Visits: 79
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
Post #784123
Posted Tuesday, September 8, 2009 6:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 1,917, Visits: 19,596
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
Post #784165
Posted Thursday, January 24, 2013 10:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1411241
Posted Thursday, January 24, 2013 9:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 1,669, Visits: 2,221
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)

Internet ATM Machine
Post #1411455
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse