The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Updating data via scheduled task

Highlighted
-=R=-
Frequent Visitor

Updating data via scheduled task

Ok... HP has told me I have too many business rules. I find this ludicrous given the amount of hardware I'm throwing at their "enterprise application"... but anyway...

I have a rule that will change the status from Informed to Closed one week after its changed to Informed. I've built a scheduled task on the database server to do this via script every night ("update all records that are informed with a modified date of 7 days ago and set status to closed").

Problem now is that the database shows Closed on all the appropriate records. Crystal shows Closed on all appropriate records. But the damned application still shows INFORMED!

Is there something obvious that I'm missing or is OVSD once again just doing whatever the damned hell it pleases?
9 REPLIES
AndrewB
Regular Collector

Re: Updating data via scheduled task

You are updating the Status_oid field in the Service Call table?
-=R=-
Frequent Visitor

Re: Updating data via scheduled task

Actually we're only modifying the status field, not the statsus_oid. I'll try updating status_oid instead and let you know how it goes.
-=R=-
Frequent Visitor

Re: Updating data via scheduled task

Ok... tested the following script in staging (object ID's corresponding to the appropriate values in our database)

update OVSD_DB_STG.v_service_call
set status_objectid='186230206614137132'
where status_objectid = '186230206614137135'


And it gave us the following error:

UPDATE statement conflicted with COLUMN REFERENCE constraint 'ser_sta_oid_fk'.
The conflict occurred in database 'OVSD_DB_STG_data', table 'sd_servicecalls', column 'ser_sta_oid'.
The statement has been terminated.

... which is jibberish to me. Any illuminatino that anyone can provide would be greatly appreciated.
AndrewB
Regular Collector

Re: Updating data via scheduled task

This is the problem -

update OVSD_DB_STG.v_service_call

You are updating a View not a table. What you are doing _can_ be dangerous to your data. If you are not _very_ familiar with SQL, I would suggest getting an expert involved to do this for you.

Good luck.
Thorfinn Thomas
Regular Collector

Re: Updating data via scheduled task

I am not sure that updating a view is the only problem here, though.

The update clearly tried to update the correct column in sd_servicecalls, that is, the column that holds the pointer to the status oid. BUT, even with good SQL knowledge, you can get into serious trouble by updating the database directly (or via a view). As far as I know, the correct way to do this would be to use the stored procedures, which should be designed to maintain data integrity and keep all cross references "up to speed" with the changes.
In other words to use the same interface against the database that HP is using in their Service Desk solution.

I haven't seen any documentation on how to use this interface though.

The mentioned contraint in the error is a part of a set of mechanisms designed to prevent incorrect altering of data.

Did HP recommend to approach this dilemma by updating the database directly?

Best regards,
Thorfinn
Ruth Porter
Honored Contributor

Re: Updating data via scheduled task

Hi there,

As has been said above, it is highly dangerous to update at the DB level. You could use the OvObsLoadObject command instead which would be safer.

To use it you set up an import mapping as though you were going to import and then use this in the parameters to the command.

You can put details such as the server name, port, account, mapping in a config file and then do something like:

OvObsLoadObject -f configfile.ini -v ID=number, STATUS=Closed

In the example I have assumed you have a mapping called ID for the call ID and STATUS for the status.

OvObsLoadObject is documented in the data exchange admin guide cahpter 3.

Hope this helps, Ruth
http://www.teamultra.net
-=R=-
Frequent Visitor

Re: Updating data via scheduled task

Andrew [ You are updating a View not a table. What you are doing _can_ be dangerous to your data. If you are not _very_ familiar with SQL, I would suggest getting an expert involved to do this for you. ]

I'm no DBA but I'm not without a little database knowledge. I understand the dangers of view updates, I'll try at the table level and see if that's any better. (for the record... this is all being tested on my STAGING system. I'm not moving to prod until I'm good and damned sure it works).

Thorfin: [Did HP recommend to approach this dilemma by updating the database directly?]

No. But of course, HP only tells me what's wrong, and seldom offer viable solutions. HP simply told us we have too many Scheduled Tasks produced by business rules. Its up to ME to find a way to work around that, and you can bet I'm not going to alter process AGAIN to acomodate ANOTHER weakness of this tool.

-=R=-
Frequent Visitor

Re: Updating data via scheduled task

Ruth: [ As has been said above, it is highly dangerous to update at the DB level. You could use the OvObsLoadObject command instead which would be safer. ]

Hey Ruth, thanks for stopping by to lend your expertise. Please understand that I am sick... SICK of this tool not living up to expectation, and failing in every conceivable fashion. Consequently, I'm extremely dubious of using something within OVSD to compensate for the part that is SUPPOSED to do it, but just ain't up to task.

Here is what I need to do: Every day, "something" has to take every ticket in a Status of "Informed" with a Modified date over 7 days ago, and update the status to "Closed".

I can't understand what's overly difficult with doing that at the DB level (since OVSD is too impotent to perform). If I know the required column names (Modified, Status_OID) and I know the specific OID's, and I know the exact conditions, what is so difficult about doing this at the database level?
Ruth Porter
Honored Contributor

Re: Updating data via scheduled task

Hi there,
You comment on my point about it being "dangerous" to do things at the DB level:

The reason for my comment is that there is no guarantee that such code will continue to work if a SP is put on on and also that it can invalidate your support if you do (or at least that is what the company I used to work for was told when we were an HP partner).

While I do sympathise with your frustrations about version 5 (I agree with much of what you say and wonder how an organisation that produced 4.5 which was pretty good and had the advantage that there was no need for a techie or programmer to be system administrator, could have taken such a backward step as version 5 turned out to be) I think everyone in the HP community knows your opinion of version 5 by now and so could we focus on the issue you are trying to fix.

So to return to my suggestion of using the OOTB command line: it has the advantage that you do not need to know the DB structure or any of the joins between tables etc.

Hope this helps, Ruth
http://www.teamultra.net
//Add this to "OnDomLoad" event