December 6, 2001 at 4:34 am
Hi Guys...
Problem.. have an online datawarehouse that lives on SQL, but gets data thrown into it from a UNIX production system. I scrub the given data using SP's and triggers, but the final stage of scrubbing involves moving the cleansed data to a table using a SP & cursors etc.. If I paste the code from the SP into a trigger, then it makes the UNIX engine fall over (still waiting on the UNIX developer to come back to me on why it does this) so I had to use a Job, firing every minute. All was well untill someone noticed the 1 minute lag on data!! so now I need to get the trigger firing the Job... CAN THIS BE DONE?? thought about using xp_cmdshell, but don't know how to initiate a Job in this way...
cheers in advance
Mr Hutch
December 6, 2001 at 5:05 am
Yes you can do it using sp_start_job. But you honestly can't live with a one min or greater delay moving stuff into a warehouse?
Andy
December 6, 2001 at 6:04 am
You can call a SP from a job, the SP just loops calling your transfer SP every 10 secs or so - see if they notice the 10 sec delay.
Just schedule the job to run every minute in case the SP crashes.
Cursors never.
DTS - only when needed and never to control.
December 6, 2001 at 6:12 am
hey hey - thanks a lot guys.. I'm looking @ that sp now...
as for the delay.. well... I thought that 1 minute was being overly generous, but then you get the boss realizing that UNIX inserts/updates "on the fly" and I have a minute lag.... raises the question why is there a lag when UNIX is sooo d@mn efficient!
some days you can't win
cheers again
Mr Hutch
December 6, 2001 at 7:09 pm
If you're doing it real time, why not look at replication? In SQL2K you can do some pretty advanced transforms of the data as it moves? I find that the need for "real time" data is usually way overblown, but if you need it, replication is the best tool I think, you get the whole infrastructure for free.
Andy
December 6, 2001 at 8:19 pm
December 8, 2002 at 6:50 pm
quote:
Hi Guys...Problem.. have an online datawarehouse that lives on SQL, but gets data thrown into it from a UNIX production system. I scrub the given data using SP's and triggers, but the final stage of scrubbing involves moving the cleansed data to a table using a SP & cursors etc.. If I paste the code from the SP into a trigger, then it makes the UNIX engine fall over (still waiting on the UNIX developer to come back to me on why it does this) so I had to use a Job, firing every minute. All was well untill someone noticed the 1 minute lag on data!! so now I need to get the trigger firing the Job... CAN THIS BE DONE?? thought about using xp_cmdshell, but don't know how to initiate a Job in this way...
cheers in advance
Mr Hutch
Can you define "fall over?" If you are using a cursor in a trigger, did you happen to lock yourself accidentally? SQL server shouldn't care what the source of its request is: UNIX, mac, PC, windows, tin can (ok maybe tin can).
I would work more on finding out what's wrong with the trigger so you can show that your system is just as efficient as UNIX.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply