Pre-SQL based service execution

[bo5i]

Hi,

I need to run services based on information thats loaded in the warehouse. We have number of customers whose data comes and gets loaded at different time. Every customer has a service associated with it. As soon as "customerA" load is complete i would like to kick off the "serviceA". For this i have a Pre-SQL which returns me "1" or "error".

If value returned from this Pre-SQl is "1" then service kicks off, but if failed where is the option to give more retry and whats the time interval for these retry.

How do we associate time schedule to this service which has Pre-SQL. How does this works if the service fails.

How to implement this in a better way.

Thanks

 

[petperson]

We do something similiar with pre SQL statements on the service. Unfortunatley, there is currently no way to stop a service from executing after it has already executed successfully. (Enhancmement request has been logged - you may want to do the same to increase the pain index). What we do to workaround is:
1) create a datamart table that is tied to an event based schedule. For example, when CustomerA load has completed, kick off a datamart report called CustomerA (simple report, used as a trigger)
2)set your service to look for this datamart. Use a 'drop table' command in the PRE statement. IE, "Drop Table CustomerA"
3)If the datamart report CustomerA is there, the service drops the table and runs successfully. If the datamart report is not there, the service fails.
4) Use this process in conjunction to a time based re-occuring schedule. For example, if you set a schedule to run every 10 minutes for 2 hours, every 10 minutes it will look for the datamart table to drop in the PRE SQL statement. If it finds the table, drops it, and runs the service, the next time it looks 10 minutes later, the table will be gone and the service fails. This is the workaround for the option of 'running until successful' that we'd like to see in Narrowcast.

This scenario works. Drawback: Alot of services clogging the DM - they usually pop in and pop right out after they fail, but it can make administration cumbersome if you have alot of these services running. Also, PRE statements (drop table) are broken in 7.5.0 and 7.5.1. They are working on a hot fix..

Hope this helps.
P.P.

 

[bo5i]

petperson,

Could you pls let me know whats the time interval between each SQL retry in PreSQL editor in Narrowcast Server