2

I have a ArcSDE (10.3) geodatabase setup which is versioned as we have a few people working concurrently on the data. However, I want to run some background scripts in SQL Server to update the default version when no one is actually working on the data.

Anyway, the data I will be editing is actually a feature class. I'm not making any geometry changes, but just changing a few attribute values. However, this attribute change is based on a join to another SDE table residing inside the same database.

ESRI states that changes made from SQL Server must be made to the the table VIEW rather than the actual base table. However, when I execute my script, I'm given this error:

Msg 414, Level 16, State 1, Line 6
UPDATE is not allowed because the statement updates view "dbo.MyTable_evw" which participates in a join and has an INSTEAD OF UPDATE trigger.

How have other people have gotten around this issue?

Example of update statement that's throwing the above mentioned error.

BEGIN TRANSACTION
EXEC set_default

UPDATE [dbo].[MyFeatureClass_evw]
SET [dbo].[MyFeatureClass_evw].[Status] = [dbo].[MySDEDataTable].[Status_2],

FROM [dbo].[MyFeatureClass_evw] INNER JOIN
[dbo].[MySDEDataTable] ON [dbo].[MyFeatureClass_evw].[PrimaryKey] = [dbo].[MySDEDataTable].[PrimeKey]

COMMIT;

@MickyT's method works great. Also, it's worth mentioning that I also got the MERGE command to do this operation as well.

MERGE MyFeatureClass_evw as TargetTable
USING MySDEDataTable as SourceTable
ON TargetTable.PrimaryKey = SourceTable.PrimeKey
WHEN MATCHED THEN
  UPDATE SET TargetTable.Status = SourceTable.Status_2
;
  • 2
    I believe the Esri doc said you'd need to edit through a versioned view, which is a very different thing. – Vince Jan 12 '15 at 20:28
  • 1
    Are you able to do a straight update on the view, eg UPDATE dbo.MyTable_evw SET somecol = 'testvalue' WHERE someothercol = 'filtervalue' – MickyT Jan 12 '15 at 21:03
  • @Vince I am editing the view, I believe. The table does have "evw" as a suffix as noted in the documentation, however, it throws the error when I run the update. When I run update commands without a join of any sort, the operation works fine and the changes are reflected immediately in the ArcMap/ArcGIS Server. – Ruchira Welikala Jan 12 '15 at 21:04
  • @MickyT Yes, I am. That operation works perfectly. It's the join that seems to be issuing the error. – Ruchira Welikala Jan 12 '15 at 21:05
  • 1
    I believe there may be a way to do this. Can you edit your question with an example of your update statement and I will see if I can modify it for you – MickyT Jan 12 '15 at 21:06
2

This seems to have been a issue with SQL Server for some time now. Not sure why they don't allow it. If you change your update statement to something like this, it should work.

UPDATE [dbo].[MyFeatureClass_evw]
SET [dbo].[MyFeatureClass_evw].[Status] = (
    SELECT A.[Status_2] 
    FROM [dbo].[MySDEDataTable] AS A 
    WHERE [dbo].[MyFeatureClass_evw].[PrimaryKey] = A.[PrimeKey]
    ),
    [dbo].[MyFeatureClass_evw].[Value] = (
    SELECT A.[Value_2] 
    FROM [dbo].[MySDEDataTable] AS A 
    WHERE [dbo].[MyFeatureClass_evw].[PrimaryKey] = A.[PrimeKey]
    )
WHERE EXISTS (
    SELECT 1 
    FROM [dbo].[MySDEDataTable] AS A1 
    WHERE [dbo].[MyFeatureClass_evw].[PrimaryKey] = A1.[PrimeKey]
    )

It can get a bit wordy with the repetition of the SELECT queries for each column updated, but it works.

  • Wow, works perfectly! If I were to update more than one field, how would I go about amending your script? For instance, I want to bring over more than just the status field from the join table. Thanks for all your help, @MickyT – Ruchira Welikala Jan 12 '15 at 21:41
  • 1
    @RuchiraWelikala I'll edit in a second set statement – MickyT Jan 12 '15 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.