SQL Server Agent Job with Multi-Versioned View

MeleKoneya — Tue, 13 Jul 2010 14:17:11 GMT

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

Re: SQL Server Agent Job with Multi-Versioned View

MeleKoneya — Wed, 21 Jul 2010 22:40:46 GMT

One of DBAs was able to find a solution.

The "SQL Server Agent User" user was given the 'db_owner' role membership and it seems to have resolved our issue with the Stored Procedure.

topic Re: SQL Server Agent Job with Multi-Versioned View in Data Management Questions

SQL Server Agent Job with Multi-Versioned View

Re: SQL Server Agent Job with Multi-Versioned View