Post reply

Exec 2k5 proc from 2k DTS

  • February 4, 2009 at 1:02 pm

    #72423

    When it comes to DTS, I'm still very much a newbie... I've never made or edited a DTS package so please understand my complete and total ignorance on the subject. 😛 Normally, I'd just test away and hammer through it on my own, but I don't have a 2k5 box to test against at work, yet (heh... we're migrating slowly ;)). I've been given a very short period of time to get the step working once they do and I want to be prepared. Google and Yahoo give a bazillion returns to different searches on the subject and none of the ones that I've looked at cover this particular problem.

    Simply stated, I'll have a stored proc on an SQL Server 2005 box and I'll want to execute that proc from an SQL Server 2000 box using a step in a DTS package that's located on the 2k box. What do I need to setup to do that?

    Looking at it logically, I think I just need to setup an ODBC connection and reference that connection in an Execute SQL Task block, but I don't know for sure.

    I also need to pass a couple of parameters to the proc and, of course, have it take a different path if an error occurs, but I believe I've got a handle on that... just mentioning that in case there's some caveat that anyone knows of.

    Thanks for your help folks.

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

  • February 5, 2009 at 1:09 pm

    #939150

    The bare essentials that you would need would be a connection and an Execute SQL task.

    Within the connection you would point it to your 2005 server and the db containing the proc you want to execute and the execute SQL task would contain the T-SQL for executing that proc.

    If that is all you are looking to do, that would be the minimum you would need to do that.

  • February 6, 2009 at 8:21 am

    #939564

    Thanks for the confirmation, Mike. I appreciate it.

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

Viewing 3 posts - 1 through 2 (of 2 total)

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