return control from xp_cmd and dts

Question

return control from xp_cmd and dts

Rick Ziminski

Old Hand

Points: 323
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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)

Rick Ziminski Old Hand Points: 323 More actions · Answer 2

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?