topic Re: SQL Trigger on table causes CODE 500 Error. in ArcGIS Survey123 Questions

SQL Trigger on table causes CODE 500 Error.

TL2 — Thu, 04 Jan 2018 21:59:45 GMT

I have a trigger on the base table of one of my feature services. After Insert seems to work fine as I have concluded that a Survey123 to FS transaction does a Input, then an Update.

After Insert statements to not cause the Code 500 Error.

After Update does cause the Code 500 error, but not everytime, most of the time.

Is there a rational reason for this where I can code around it. I need to pull data from insert after an update without the code 500.

I should add that the trigger performs fine and correctly when making the edits right in ArcMap, every time. Also I have never had these issues with Collector (however most of my Collector FS is versioned, Survey123 is archive enabled.

Re: SQL Trigger on table causes CODE 500 Error.

JohnathanHasthorpe — Fri, 05 Jan 2018 02:56:23 GMT

Hi Tucker

Does you service have attachments? The reason I ask is that on submission, Survey123 will do an ApplyEdits and then an AddAttachments and this is why you would get two calls.

In the future we will change this into a single transaction using apply edits with UploadID, but we are still working on this implementation.

Thanks

John

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Wed, 10 Jan 2018 18:08:34 GMT

I was skeptical on this response at first but apparently this is exactly what is going on.

I realized that all of my After Insert triggers work when there are no attachments.

When there are attachments, my After Insert triggers fire, but the declared variables are always null.

It seems to go something like this... Insert Row, Update Attachments, Update Row. Unfortunately After Update does give the Code 500 error and freezes up the feature service as well.

Do you have any idea on the implementation of the apply edits with UploadID? I would guess that is the method Collector uses.

Thanks.

Re: SQL Trigger on table causes CODE 500 Error.

JohnathanHasthorpe — Thu, 11 Jan 2018 02:49:33 GMT

We are aiming for 2.7 - there are just a couple of bugs that we need to iron out.

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Tue, 03 Apr 2018 21:52:40 GMT

Was this able to be fixed in 2.7 (before I begin to test)?

THANKS!!!!!!

Re: SQL Trigger on table causes CODE 500 Error.

JamesTedrick — Tue, 03 Apr 2018 22:28:22 GMT

Hi TL,

With 2.7, we have instituted a change in the submission process where supported. This reduces the number of calls to the database to 1. Note that this is not supported with ArcGIS Enterprise hosted services (they currently do not advertise support for the submission procedure we are using) - it sounds as if you service is federated, correct (it should be using the new submission if that's the case).

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Thu, 12 Apr 2018 13:13:11 GMT

James,

I am testing my triggers using 2.7 and while I do not receive the code 500 error when posting my surveys, the data still does not seem to post before the trigger is fired.

If I make edits or create a new feature within ArcMAP, my trigger contains the variables. When I create a survey in Survey123 2.7, the trigger is fired but the variables are not passed.

Any Suggestions?

Note: I do receive the error on "update" but not on "insert"

Thanks.

Re: SQL Trigger on table causes CODE 500 Error.

JamesTedrick — Thu, 12 Apr 2018 21:53:19 GMT

This is probably a bit complex to debug via GeoNet; it may make sense to try to do a webcast to interactively debug/understand the issue. That being said, there are some areas to examine:

What is the database Survey123 is writing to- and enterprise GDB or the hosted feature store?
Can you attempt editing via the REST endpoint directly to see if the behaviors are consistent?
- Survey123 uses the applyEdits call at the FeatureService endpoint (i.e., https://gis.server.com/arcgis/MyService/FeatureService/applyEdits)
- Most other web editing techniques use applyEdits at the Feature Service Layer endpoint (i.e., https://gis.server.com/arcgis/MyService/FeatureService/applyEdits

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Fri, 13 Apr 2018 14:05:06 GMT

It seems to be behaving like version 2.6 and prior. I have republished with 2.7 Connect, republished my service mxd and upgraded S123 to 2.7. Is there a way to verify it is using the new method to post. Portal federated with server 10.5.

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Fri, 13 Apr 2018 14:06:08 GMT

I should say, posting with attachments disabled still works, just not with attachments.

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Mon, 10 Jun 2019 21:14:12 GMT

I am still unable to use an "AFTER UPDATE" sql trigger on the table of a survey123 feature class. I am able to use "AFTER INSERT". "AFTER INSERT" does do one insert and I am able to pull data from the submission (before after insert would only work with Attachments disabled.

"AFTER UPDATE" still causes the survey submission to hang.

Sure would be nice to to use "AFTER UPDATE" on a survey123 feature table...

Re: SQL Trigger on table causes CODE 500 Error.

JamesTedrick — Mon, 10 Jun 2019 22:51:39 GMT

Hi,

What DBMS are you using? Which version of ArcGIS Enterprise?

Could I also ask that you try a direct REST submission request on the feature service's ApplyEdits call (Apply Edits (Feature Service)—ArcGIS REST API: Services Directory | ArcGIS for Developers ) and see if you get the same results?

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Tue, 05 Nov 2019 14:08:21 GMT

James,

Error: Underlying DBMS error [HY000:[Microsoft][ODBC Driver 13 for SQL Server]Connection is busy with results for another command] [GISDB.DBO.FC_S123_TABLE].

I believe this to be the one error in my way. Here are the scenarios that work and fail.

This seems to not be a S123 issue so maybe you can point me to someone else.

After Insert

a. The post is successful and I am able to use data from inserted and update tables in the geodatabase with inserted data.
b. The post is successful when I use an exec store procedure and do not use any data from inserted in the SP.
c. The post fails when I use an exec store procedure and try to capture any data from inserted in the SP.
- example: exec sp_send_dbmail fails with inserted data in the email body/subject...
  - This is the one I want and this is the one that gives me the error above.

After Update

d. The post is successful and I am able to use data from inserted and update tables in the geodatabase with inserted data.

e. The post fails when I use an exec store procedure and do not use any data from inserted in the SP.
f. The post fails when I use an exec store procedure and try to capture any data from inserted in the SP.
- example: exec sp_send_dbmail fails with inserted data in the email body/subject...
  - This is the one I want and this is the one that gives me the error above.

I do receive these same results when applying edits at the rest end point as mentioned above.

I am using SS2016 and 10.7. Data is Archived.

When applying the edits directly in ArcMap I do not have any issues.

Thanks.

Re: SQL Trigger on table causes CODE 500 Error.

JamesTedrick — Tue, 12 Nov 2019 18:35:27 GMT

Hi,

Yes, this appears to be out of scope for Survey123 - I would suggest opening an Esri technical support ticket focusing on the DB behavior.

Re: SQL Trigger on table causes CODE 500 Error.

vijaybadugu — Thu, 21 Nov 2019 21:12:28 GMT

I was facing same issue

Re: SQL Trigger on table causes CODE 500 Error.

TL2 — Thu, 21 Nov 2019 22:25:23 GMT

Its actually not a S123 issue but the way the rest endpoint posts to the database using Apply Edits at the feature service level. From my testing it does two transactions, an insert and then an update. Any executions fail in the trigger because they are called on the insert but the update has not occurred yet. Hence the warning. I have not found a work around, but that is whats happening.

Additionally it is by design so basically not an ESRI problem.

I'm not sure how Collector posts but this is not an issue with Collector.

Re: SQL Trigger on table causes CODE 500 Error.

vijaybadugu — Thu, 21 Nov 2019 22:32:13 GMT

Yes .. The problem with Insert when we append update to it AFTER INSERT, UPDATE . Whenever Inserting records , Internally update is also firing . If you write Trigger in Attachment table . it should work . it is not working on Base table

Re: SQL Trigger on table causes CODE 500 Error.

vijaybadugu — Fri, 22 Nov 2019 17:49:36 GMT

The problem is when i did not pass attachment is also. it is firing twice on insert trigger