pass variables between SQL Server tasks in a JOB

  • 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?

  • How about the global variable?

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

  • 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).

  • 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) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • 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???

  • 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

  • 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

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thanks for the feedback. But, remember, this was supposed to be done in a job with multiple steps without the use of SSIS. The global variables there do no apply to this particular request because SSIS is not allowed.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi, I would also need to pass a string from one JobAgent Step to the next (WITHOUT SSIS). In addition, my first step is a PowerShell Command which fetches the string basically. So I also need to know if it's possible to pass a value from a PS-Step to a T-SQL-Step?

    Many Thanks in advance!

    FS

  • I'm not aware of any solution other than that originally offered for this chain of posts.... In your case, use PowerShell to run a VBScript with ADO code and insert the string into a single-record table and then use that table as the string source for your T-SQL script.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • It would be nice to be able to pass parameters between steps. I'm doing something similar. I would have liked to do the related T-SQL in a separate step. However, I execute the T-SQL in same PSH step.

    invoke-sqlcmd -Query $query `

    -serverinstance $serverinstance -database 'master' -QueryTimeout 600

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply