nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL Trigger on table causes CODE 500 Error.

TL2

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.

Encontre mais publicações marcadas com

Accepted answers

All comments

JamesTedrick

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.

TL2

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.

JamesTedrick

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?

TL2

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...

TL2

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

THANKS!!!!!!

JHasthorpe

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

TL2

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.

JHasthorpe

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

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.