Blocking access to running reports until overnight jobs are finished

[youngcougar]

We currently use Crystal Enterprise 10. All of our reporting is hooked to Microsoft SQL server. Everything runs fine with the exception of users logging onto Crystal and running reports before overnight jobs are finished (we span a couple of time zones) Any suggestions on how to block user access until the overnight jobs are done, so that they can complete, as when users try to run reports, they tie up the table(s) that could be in the process of being populated - which then causes the jobs to take longer.

Any ideas would be greatly appreciated!

 

[ShortyA]

Are you able to disable the CMS during out-of-hours processing ie whilst the database is being updated. Then set a trigger from the database refresh that will restart the CMS once the data has been updated ? We did something similar a couple of years ago and although not the tidiest solution it worked.

ShortyA

 

[youngcougar]

I was thinking that was our solution - I've tried to use a DTS package that would disable the CMS after a certain time at night an have the same type of package that would either enable the CMS or restart it - I can't seem to get the package (ETL tool prepacked with SQL Server) to actually stop or disable the CMS. Then I was thinking maybe have the DTS package run a .bat file, but don't know a lot about 'DOSINESE' either - any ideas how I could either write a script file that would do this (.bat) or have the DTS package successfully disable or Enable the CMS?

Thanks for your prompt reply btw!

 

[Turkbear]

Hi,
Assuming your services are on a windows box, you should be able to schedule an AT job that would stop the CMS service at a specified hour and one that restarts it at some later time..

The windows docs should have the command line info needed for starting and stopping a service and also info on how to set up a job to run on a schedule..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[youngcougar]

Thanks for both of your help but for future users, this is what I did - I created a .cmd file with the following lines of code
<startCMS.cmd>

net start "Central Management Server"
exit

and...
<stopCMS.cmd>
net stop "Central Management Server"
exit

I have pointed the DTS package 'run process' task to these files and all works good!

As for the scheduling, I will get the SQL server to run the package as a job step, at a certain time in the evening, then start things again based on Job events.