SQL Server Stumped Issue

Question

question

bjoe asked Jul 3, '18

SQL Server Stumped Issue

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)?

sql server 2008 r2

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Jul 03, 2018 at 10:23 PM

How is the file being processed? Agent job? SSIS? Is the procedure consuming the file?

0 ·

bjoe commented · Jul 03, 2018 at 11:03 PM

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.

0 ·

bjoe commented · Jul 04, 2018 at 01:38 AM

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.

0 ·

Answer 1 · 2018-07-10T08:59:50Z

bjoe answered Jul 10, '18

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question