Hi All, I have a sql agent job "LoadfromOracle" which loads data from oracle to sql server tables(Orders table, Marketing table ) everyday. This job runs everyday at 7:00 am (Avg Execution time is 1 hour), so completes at 8:00 am. I have a report named "daily report" which uses Orders table, Marketing table to fetch the required data. we had created subscription for this report ("daily report") and this report is send to executives everyday as an email attachment at 8:20 am. Problem : some times the sql agent job "LoadfromOracle" is taking more time(4-5) hours to complete data load into Orders table, Marketing table. So, without complete data into Orders table, Marketing table the report ("daily report") is using incomplete data and email is sent to executives with wrong/incomplete data at 8:20 am. My question : Can I include this report subscription(process of sending report as email) as a SQL AGENT JOB STEP in sql agent job "LoadfromOracle". ???? so that in sql agent job "LoadfromOracle" i have step1: loading data from oracle to sql server step2: sending report to subscribers as an email attachment For step1 i will include OnFailureAction = "Quit the job reporting failure". By doing this i can be sure that unless the data load is completed, the report won't be sent to executives. and if step1 fails then step2(sending report) step will not be executed. Can any one give me advice on implementing this?? Thanks for your time!! Raj Vardhan

Hi Raj Thanks for your posting When you create a subscription all the set up information will be stored inside report server database table called [ReportServer].[dbo].[Subscriptions] So all your parameter info file path and type of file which you would like to save (PDF/XML/XLS etc) So when you create a subscription it will create a job under your SQL agent for all the info what time it supposed to run etc. That is why SQL agent must be running on the server where your report server database is when you would like your subscription to work properly So please go to your SQL Server where your report server database is and see under SQL Agent\Jobs there must be job which will be running for your subscription Go to the properties of that job and edit step and copy the step and put it in your LoadfromOracle job as a second step So LoadfromOracle job will look like this step1: loading data from oracle to SQL server step2: sending report to subscribers as an email attachment (copy step from the subscription job) Please make sure after successfully implementing and testing this please delete the first job which is running for your subscription to avoid rerunning job again For step1 you will include OnFailureAction = "Quit the job reporting failure". For your reference I am putting screenshot. If you have any questions please do ask. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Thanks Syed... I will try this... I have one doubt : what happens when the report subscription time(6:10 am) and the sql agent job "LoadfromOracle" execution timings(7:20 am)are different. I mean to say, for report i have the subscription time to send the report as 6:10 am and when i copy the step from subscription job to 2nd step of "LoadfromOracle" job, at what time will my report be sent ?? If the system uses "LoadfromOracle" execution time(7:20 am) then what is the use of subscription time ?? Thanks for your time!!