Store update, insert, or delete statement affected an unexpected number of rows (0)

[mike_meirovitz]

Hi,

Our SmartConnector extension is receiving an exception. It looks to me as a connection problem with SQL Server. See the error message below.

Any idea what may cause this issue? How can it be prevented?

2022-09-06 16:29:12.018 [14] ERROR, UpdateProcessor:Execute_Subclass, Long loop error 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.'

Thank you.

Michael

[Erwin-vd-Zwart]

Is the SQL db running on the same machine as the SC framework?

Are you using a EWS server in the framewok?

When reading the error message i get the idea that 2 processes are using the same data source and one process change them where the second process is not aware of it.

Do you run code that deletes entries in the data adapter? 

Adding DataAdapter.CreateDatabaseContext(); before executing the code that causes the error might help as it reloads the data adapter with the actual data in the SQL DB

[mike_meirovitz]

Hi Erwin,

Thank you for your answer. Yes, SQL Server running on the same computer where SmartConnector is running.

We will check your recommendations.

Mike

[mike_meirovitz · Accepted solution]

Hi Erwin,

Yes, we are using EWS server.

SQL Server on the same computer where SmartConnector is running.

We do have 2 processors (Setup and Update), however the setup processor was completed before the update processor has started. So in this case it is just the Update processor.

We have many installations of this extension, the problem is just with a specific installation where the EWS server has many values (~17,000).

I have attached the SQL server log file where you can see these errors below. It looks as a SmartConnector to SQL server issue.

Can you please involve SmartConnector R&D so they can assist?

1. 

Line 657: 07/07/2022 22:12:51,spid61,Unknown,Service Broker needs to access the master key in the database 'SmartConnector.Service'. Error code:32. The master key has to exist and the service master key encryption is required.
Line 658: 07/07/2022 22:12:51,spid61,Unknown,Error: 28054<c/> Severity: 11<c/> State: 1.
Line 662: 07/04/2022 16:15:50,spid61,Unknown,Service Broker needs to access the master key in the database 'SmartConnector.Service'. Error code:32. The master key has to exist and the service master key encryption is required.
Line 663: 07/04/2022 16:15:50,spid61,Unknown,Error: 28054<c/> Severity: 11<c/> State: 1.
Line 666: 07/04/2022 14:59:06,spid61,Unknown,Service Broker needs to access the master key in the database 'SmartConnector.Service'. Error code:32. The master key has to exist and the service master key encryption is required.
Line 667: 07/04/2022 14:59:06,spid61,Unknown,Error: 28054<c/> Severity: 11<c/> State: 1.
Line 672: 07/04/2022 11:42:48,spid61,Unknown,Service Broker needs to access the master key in the database 'SmartConnector.Service'. Error code:32. The master key has to exist and the service master key encryption is required.
Line 673: 07/04/2022 11:42:48,spid61,Unknown,Error: 28054<c/> Severity: 11<c/> State: 1.

2.

05/06/2022 09:25:21,spid14s,Unknown,0 transactions rolled back in database 'SmartConnector.Service' (5:0). This is an informational message only. No user action is required.
Line 962: 05/06/2022 09:25:21,spid14s,Unknown,360 transactions rolled forward in database 'SmartConnector.Service' (5:0). This is an informational message only. No user action is required.
Line 968: 05/06/2022 09:25:21,spid14s,Unknown,Starting up database 'SmartConnector.Service'.

Thank you.

Michael

[Horacio1]

Hello,

Maybe they just need to create the master key in the data base as the error says:

Service Broker needs to access the master key in the database 'SmartConnector.Service'. Error code:32. The master key has to exist and the service master key encryption is required.

This link might help:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-mast...

Looking forward to the solution.

Regards from Chile.

HSU

[mike_meirovitz]

Hi HSU,

Thank you for your answer. I don't think that we are allowed to change anything in the SmartConnector Database. We just use SmartConnector APIs. If there is a problem with the SmartConnector database, the fix should be done by Schneider Electric, not by us.

Thank you.

Regards,

Mike  

[mike_meirovitz]

Hi,

Please see the relevant part of our SmartConnector processor code below.

DataAdapter.CommitChanges();
DataAdapter.AutoCommit = true;

in this case, the DataAdapter is doing something in background which we don't when it ends. 

Then if we call following commands before this background task ends it causes the error,
dataAdapter.DeleteSubscription(ewsSubscriptions);
dataAdapter.CommitChanges();
dataAdapter.AutoCommit = true;

We must know when dataAdapter is finished and ready for new CommitChanges.

I appreciate your support.

Thank you.

Mike