Post reply

return control from xp_cmd and dts

  • April 26, 2007 at 7:54 am

    #178048

    I have a situation where I have a remote system that I need a File from.

    I have created a database link from the oracle system to sql server.

    When oracle generates the file it then writes a row to a indicatator table.

    I need to then execute a dts job to ftp the file from the remote server to sql so I can then do the processesing of the file.

    If i put a trigger on the table to execute xp_cmd this works until the file is to large  then the oracle system will get an error . Because xp_cmdshell operates synchronously.

    Control is not returned until the command shell command completes which in this case is many steps.

    So the question is how can I have a trigger that will execute a job to retrieve the file, after reciept of file return a code of success, and then continue with the processing of the file?

    It seems to me if I break up the dts job so that after I get the file it then calls another dts job, the return still won't be untill the second job completes.

    I don't want to create a job that just checks for the file, and runs when it finds one, the file is only create once a day 4 days a week on the remote system. The file needs to be loaded when created.

    any help would be apprecaited.

    my trigger executes like so:

    EXEC master..xp_cmdshell 'dtsrun /S server /U USER /P pass /N "dts job"', no_return

    I don't remember where I got the syntax and now I can't find any reference to no_return. What is no_return doing if anything?

    BTY the server is sql ver 8.0

    Rick

  • April 26, 2007 at 8:35 pm

    #702841

    no_return should actually be no_output and, in this case, it will be of NO_HELP...  no_output is part of xp_cmdshell and it just means not to display any output it may create.

    --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)
    Intro to Tally Tables and Functions

  • April 27, 2007 at 6:45 am

    #702893

    Thanks Jeff, I found that in BOL, and was drawing the same concusion.

    I've decided on a new tact, just don't know how to do it yet.

    Rather than using the indicator table with a trigger, monitor the indicator table for a new row. in vbscript you can "watch" for a creation of a new file, not sure if there is a equilvilant in sql for a new row. If so put it in an activex script and dts can do the rest.

    So new quetsion is;how do you know when a new row is inserted without using a trigger?

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

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