We have a stored procedure which updates a field in four featureclasses. The stored procedure is as follows:
--UPDATE SYMBOLOGY FIELD
EXEC sde.set_current_version 'ums_app.MapGuide'
EXEC sde.edit_version 'ums_app.MapGuide', 1
Begin Try
Begin Transaction;
--wtr_hdyrant table
UPDATE ums.wtr_hydrant_mvw
SET symbology = owner + '/' + pm_state
WHERE owner + '/' + pm_state <> symbology
-- Commit Transaction;
--wtr_valve table
-- Begin Transaction;
EXEC sde.set_current_version 'ums_app.MapGuide'
UPDATE ums.wtr_valve_mvw
SET symbology = owner + '/' + pm_state
WHERE owner + '/' + pm_state <> symbology
-- Commit Transaction;
--wtr_main table
-- Begin Transaction;
EXEC sde.set_current_version 'ums_app.MapGuide'
UPDATE ums.wtr_main_mvw
SET symbology = owner + '/' + main_type
WHERE owner + '/' + main_type <> symbology
-- Commit Transaction;
--swr_main table
-- Begin Transaction;
EXEC sde.set_current_version 'ums_app.MapGuide'
UPDATE ums.swr_main_mvw
SET symbology = owner + '/' + main_type
WHERE owner + '/' + main_type <> symbology
------------------------------------
Commit Transaction;
When I try to run the stored procedure as a SQL Server Agent Job, it does not update the field in the feature classes.
If I run it 'manually' as a stored procedure it does work. I am dbo. Has anyone set up a SQL Server Agent Job to run a Stored Procedure? If so, do we need to 'run as' a particular user? I had the owner set to my name, sde, and the data owner name. Are there any other caveats to using this method?
Thanks for any suggestions,
Mele
