Timeline for Variable Value Set in TRY CATCH is Discarded When Trigger is Coded a Certain Way
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 6, 2023 at 19:04 | answer | added | Ross Bush | timeline score: 0 | |
| Jul 6, 2023 at 17:23 | comment | added | Ross Bush | @MartinSmith :( I think I have a plan B. However, I was asked to try and get the above solution to work. | |
| Jul 6, 2023 at 17:21 | comment | added | Martin Smith |
Right, if I try the command EXECUTE ('CREATE PROC P1 AS SELECT 1') WITH RESULT SETS NONE - which fails on the second attempt as the proc already exists - then SET XACT_ABORT OFF doesn't save the day. The transaction is still doomed
|
|
| Jul 6, 2023 at 17:15 | answer | added | StrayCatDBA | timeline score: 2 | |
| Jul 6, 2023 at 17:12 | comment | added | Ross Bush | @MartinSmith - Commands are always non- batched in the form of exec sp_execute 1,1040,N'CREATE FUNCTION...'... exec sp_execute 1,1041,N'CREATE FUNCTION...'... exec sp_execute 1,1044,N'DROP VIEW...'... exec sp_execute 1,1059,N'CREATE FUNCTION...'... exec sp_execute 1,1060,N'CREATE FUNCTION...'... | |
| Jul 6, 2023 at 17:11 | comment | added | Martin Smith |
Also the inserts are definitely single row as SELECT @Command = Command from inserted implies and not happening in a single multi row insert? The trigger isn't written correctly to handle the case that a single insert can insert 0, 1 or many rows.
|
|
| Jul 6, 2023 at 17:09 | comment | added | Ross Bush | I did not want to complicate matters, but this table gets populated by inserts into another table via trigger and that table participates in replication. The table above does not participate in replication and just acts as a way to avoid re-running already ran commands during a full load of the source table. I think that maybe the client application doing the replication, aws/data migration service, has another transaction because the @@TRANSCOUNT in the replicated tables trigger is always 2. | |
| Jul 6, 2023 at 17:08 | comment | added | Martin Smith |
I've tried with EXECUTE ('EXEC FOO') WITH RESULT SETS NONE; and EXECUTE ('PRINT 1/0') WITH RESULT SETS NONE; and both log the expected errors. What is the nature of the command you are trying to run?
|
|
| Jul 6, 2023 at 17:01 | comment | added | Ross Bush | @MartinSmith - Thanks, I tried something like this yesterday. When I replace EXECUTE (@Command) with SET @X = 1 / 0, then all the data comes across and I get 120 divide by zero errors | |
| Jul 6, 2023 at 16:58 | comment | added | Martin Smith | Is this basically what you are trying to do? dbfiddle.uk/svf1vwQR | |
| Jul 6, 2023 at 16:46 | comment | added | Ross Bush | @MartinSmith - Thanks for the reply. I commented out the COMMIT TRANS and BEGIN TRANS and added SET XACT_ABORT OFF at the top, however, when I reload table, no records are saved. | |
| Jul 6, 2023 at 16:41 | comment | added | Martin Smith |
Ugh. Have you tried SET XACT_ABORT OFF in the trigger (implicitly it is on) instead of that horrible workaround?
|
|
| Jul 6, 2023 at 16:37 | history | asked | Ross Bush | CC BY-SA 4.0 |