pass variables between SQL Server tasks in a JOB

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » pass variables between SQL Server tasks in a...

14 posts, Page 1 of 2

1 2 »»

pass variables between SQL Server tasks in a JOB

Rate Topic

Display Mode

Topic Options

Author

Message

knight-794779

Posted Friday, September 04, 2009 5:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 PM
Points: 8, Visits: 252

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

SQL ORACLE

Posted Friday, September 04, 2009 8:22 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314

How about the global variable?

Post #783234

knight-794779

Posted Friday, September 04, 2009 8:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 PM
Points: 8, Visits: 252

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

Marcin Gol

Posted Sunday, September 06, 2009 5:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 03, 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

sgmunson

Posted Monday, September 07, 2009 8:33 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:36 AM
Points: 1,447, Visits: 1,888

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)
Smile

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

Weight Loss Tips

Post #783964

knight-794779

Posted Monday, September 07, 2009 9:29 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:10 PM
Points: 8, Visits: 252

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

sureshr69

Posted Tuesday, September 08, 2009 5:13 AM

Grasshopper

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

J Livingston SQL

Posted Tuesday, September 08, 2009 6:06 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 1,472, Visits: 14,595

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 !
__________________________________________________________________

Post #784165

Jeff Moden

Posted Thursday, January 24, 2013 10:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1411241

sgmunson

Posted Thursday, January 24, 2013 9:31 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:36 AM
Points: 1,447, Visits: 1,888

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

Weight Loss Tips

Post #1411455

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions