Stuck on a production issue, any suggestions or guidance is much appreciated
Our environment is PB 2017 R3. SQL Server 2016. We have about 6 users accessing a compiled application residing on a windows server and folks tend to be working from home more often connected via VPN
Manual Process - Insert into db1 and Update db2
It seems users are not having issues making inserts/updates in there database (db1), but within the same transaction are having issues when updates are performed in a different database (db2). No errors appearing. This logic all takes place within PB, using datastores and updatable views dealing with a record at a time.
This issue was very rare. Over the last 7-8 years it's happened a handful of times. We didn't spend much time investigating, thinking it must be some kind of network / connectivity issue and not worth putting much time into. Bad move on my part.
New Automatic Process (exists in addition to the Manual Process)- -Insert into db1 and Update db2
Business has changed, and the folks tend to spend more time working from home (not sure if it makes a difference). I've created an process that now reads a file and creates multiple records in db1, and within the same transaction updating db2. This logic takes place within a stored procedure called by PB. Sometimes those updates in db2 don't take place or only some records are successfully processed. The strange thing is that no errors are produced from SQL or PB (yeah, I do have my error handling :). So far 6 files have been loaded and two files have not had any issues.
To make matters more challenging, I can't seem to replicate the issue in our dev or test environment. It only happens in production.
As an example, file1 with a total of 170 records need to be fed into db1. All 170 records are inserted in db1, but only 150 updates take place in db2.
again, and advice is welcome!
Just an update, I didn't realize on the manual process the window has to be click twice for it to be saved. I have to investigate this, wondering if this is related ( unlikely, but you never know) . I'll update this with my results