February 19, 2020 at 11:59 am
Hi
we have a test rig for proving our code can work on sql server 2019.
It's legacy code and is too long to post here (lets put it this way, the error occurs at line 2753)
the code works 100% fine on 2016, but on 2019 (even in 2016 compat mode) it bums out in less than a second
the proc inserts into an "order item" table which has a 500 line "instead of" trigger - i promise you that i didn't write it 🙁
the error is "The transaction ended in the trigger. The batch has been aborted."
There is nothing in the trigger which touches any other database or table(I already thought of nested triggers) and SQL compare gives me a 100% match to my 2016 version.
before I start recoding , does anyone know why this works on 2016 and not 2019?
Thanks
MVDBA
February 19, 2020 at 2:26 pm
does the trigger itself begin / end transaction, too?
February 19, 2020 at 2:54 pm
yes,
there is also a begin tran/try catch/commit/rollback in the calling proc
I know the proc should be handling the transaction, but someone has been a bit "slap happy" with XACT_ABORT and BEGIN TRAN
I'm not looking forward to re-writing almost 100 triggers and then finding where they are used (7000 objects)
I'm trying to figure out why 2016 and 2019 are behaving differently
I can pinpoint the exact line of code in the stored procedure that is causing the issue, it's a simple insert statement with 4 fields... unfortunately SQL's error handling does not always give you usefull info - i'm loathe to add logging to the trigger (performance is bad enough)
MVDBA
February 19, 2020 at 3:44 pm
I managed to track down the code in the trigger (using print statements on my 2019 dev kit)
INSERT INTO order_line
(
account_code,
order_code,
order_line_id,
stock_code,
stock_code_parent,
qty_required,
qty_allocated,
qty_back_order,
back_order_code,
unit_cost,
unit_price,
unit_price_discount,
unit_price_extra,
unit_tax,
unit_tax_discount,
unit_tax_extra,
unit_weight,
notes,
line_ref,
price_notes,
shelf_number,
shelf_check_code,
MTO_ref,
unit_contains_qty_now,
unit_commission,
unit_commission_recipient_id,
FromBulkOrderCreate,
WebKitID,
UnitPriceOverride,
UnitPriceOverrideSetBy,
UnitPriceOverrideDatetimeSet,
WebKitOrderedAmount
)
SELECT account_code,
order_code,
@order_line_id,
stock_code,
stock_code_parent,
qty_required,
qty_allocated,
qty_back_order,
back_order_code,
@unit_cost,
CASE
WHEN Inserted.UnitPriceOverride IS NOT NULL THEN
Inserted.UnitPriceOverride
ELSE
@unit_price
END,
unit_price_discount,
unit_price_extra,
@unit_tax,
unit_tax_discount,
unit_tax_extra,
@unit_weight,
notes,
line_ref,
price_notes,
shelf_number,
shelf_check_code,
MTO_ref,
@unit_contains_qty,
@unit_commission,
@unit_commission_recipient_id,
Inserted.FromBulkOrderCreate,
Inserted.WebKitID,
Inserted.UnitPriceOverride,
Inserted.UnitPriceOverrideSetBy,
Inserted.UnitPriceOverrideDatetimeSet,
WebKitOrderedAmount
FROM inserted;
It's not bad data (i took a backup of live an hour ago, which works in 2016)
what am I missing?
MVDBA
February 19, 2020 at 4:42 pm
I think I found the issue, not sure why , but the "instead of" trigger on that table seems to be re-firing itself
I turned the "allow triggers to fire triggers" to on and I got the following
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
MVDBA
February 19, 2020 at 6:40 pm
what does the output of this query give you on new server and old server?
select name, is_recursive_triggers_on
from sys.databases
where name = 'mydbname'
replace mydbname with correct name
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply