I have a problem. Scheduled jobs fail when Facets updates the reporting instance from which the queries in the job will run. It used to get updated at a scheduled time so we just avoided that time slot for the jobs. Now it gets behind and runs at any point in the day, kicking jobs out in the middle and preventing others from running any step that accesses that server. What I want to do is be able to check the connection before before I run the query and if the connection is successful then run the query, if not pause and try again in one hour. I can set up a while loop with a pause step in it but how do I check the connection? Also I would like to put the import/query step inside a while loop as well that checks to see if it succeded and pauses and repeats if it did not (to correct for the times when the reporting instance load interrupts the connection). Any ideas?
Yes, you can do this. Try adding a Execute Script activity. Enter a SQL statement such as 'select 1 from dual'. Something that will return one row. Mark the activity as 'Continue on error'. It will put the result of this activity into the row count variable. If successful the value will be 1. If the connection fails the value will be -1
I attached screenshot. I have sample script but need to figure out how to attach to post.
Debbie
Brilliant! I am making the changes now. I set up variable in advance (called Test_Connection) and set the value to -1. Then used a while loop to run the sql with the row count variable updating Test_Connection each hour (Pause 360 seconds if value is -1). As soon as the row count comes out different (i.e. 1) then it leaves the while loop and processes the rest of the job.
Thank you so much.
- Greg
Here is the sample script I promised in case you need it.
Debbie[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/SampleScript.zip:550:0]