Checking that event files have been dropped.

[mrees]

We are using CE9 (Due to upgrade to 10 very soon) and SQL2K.

It could be that someone out there could save my neck!

We have created various schedules that run our reports from files dropped onto the eventserver. These files are placed there by our SQL Server once the relevant jobs to populate the tables has finished.

The problem is our DBA's sometimes find it hard to understand that many reports are critical to our clients. And will quite happily cancel a job before the event file is dropped. So the report doesn't run, the client isn't happy - and the first thing we know about it is when our div exec starts shouting at us....

Is it possible to be notified if a schedule does not run when is should, or just when an event file doesn't appear when it should?

Thanks in anticipation

mrees

 

[mdwyer]

What jobs are the DBA's cancelling? The ones that generate the event files from within the database, or the CE scheduled instances that are waiting for the event file to be created? And, do you mean that the instances are scheduled to run at a particular time, but are also dependent on a File Event firing?

 

[mrees]

Sorry, I should have made myself more clear. The DBA's have in the past cancelled the SQL jobs that create the file(s) on the event server - so the file never appears.

The schedules are set up to run "daily, with events" with appropriate file event linked to it.

thanks

Martin

 

[Turkbear]

Hi,
If , after you have asked the DBAs not to do that without notifying you, management still allows your DBAs to interfere with needed business processes, then be sure they get a message every time it happens ( copy all of top management) and remind them that your clients will assume that the company is incompetent..

As a DBA I would be out the door if I did that sort of thing to my users/clients...

 

[mdwyer]

Exactly right, Turkbear!

But it doesn't help Martin - at least until the DBA's are, uh, re-oriented.

I'm not aware of a built-in way to do what you want, but various thoughts come to mind. Like having a process outside of CE look for the expected file. Or scheduling a report that tries to access them and reports on what it finds - or doesn't. Or a .csp page that monitors report instances and includes logic to highlight stale-dated recurring schedules.

There is a page in CE9, available from the Crystal Enterprise launchpad that might help. It's called Instance Manager, found on the left under Administrative Tools. Select a status of Recurring, a specific user (or blank for all), and click Go. You'll have to visually scan for schedules that are not in the future, but that will show you what didn't run.

 

[mdwyer]

I've uploaded my Report and Instance Listing csp code at

http://groups.yahoo.com/group/Crystal_Enterprise/files/.

This is a feature of Yahoo! Groups that is new to me, so I don't know how well it works. It seems that I have to use View Source in the browser to see the actual code after selecting the file. Feedback is appreciated.

 

[mrees]

Thanks for those thoughts, unfortunately we can only use CE "out of the box" we have neither the skills or resource to start developing our own pages. We have created a report from a replication of the CE90 database that tells when any schedules fail, but it does not tell us when a schedule does not run. We did think of trying to create something from the CE90 base, where we counted the number of report instances, and compared that to the number we think we should have. But that seemed like a lot of work, and would probably not be fool proof.

Turkbears comments are completely correct, and believe me we do as he suggests. It just seems that actual report production (our area) is quite low on their priorities, which is mainly getting the stuff in. I suppose I could argue whats the point in the DBA's putting stuff in, if we can't get it out afterwards....maybe I should start another thread "Dealing with DBA's who have a God complex"?

Thanks again.

Martin

 

[mdwyer]

You might be interested in the recent thread about reading from the APS into and XML data source: thread782-841589
This would allow you to write a report that could be filtered and scheduled to tell you what did not run. Gortuk's code works pretty well!