I have two environments, one production and the other a UAT, with the identical stored procedure and insert trigger. In the production environment, when there is a file is loaded and the insert trigger fails due to “fail to insert due to column is not nullable, the procedure continues processing. The file continues to processs in production, but stops processing’s in the UAT server. The two SQL Servers are on the same version and R2 level, both have the exact same server options and both databases have the same options set. Why does the procedure fails on the UAT, but continues processing in production (the preferred action)?
Answer by bjoe ·
The order file is a XML file that is processed. I have had the developer to provide a concise description of how the file is processed.
Usually a file is dropped in some FTP location and then webMethods picks up the file, parses it, then makes a SOAP http call to our InocOrders service on one of our web servers. InocOrders, a .NET web service, processes the SOAP request using a custom .NET library called OEWriter3. This in turn, using ADO.NET, writes order data to various temp tables (OE tables like trnOEOrderHeader), then calls several stored procs including oeProcedures;6 which starts the order flush process that copies from the OE tables to the Trn tables (trnOrderHeader, etc). It’s in oeProcedures;6 where the exception is thrown - and STOPS in UAT, but proceeds in Production.