April 26, 2007 at 7:54 am
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
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
Change is inevitable... Change for the better is not.
April 27, 2007 at 6:45 am
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