Getting Weird Error in any SQL Server Link Table that has Triggers

[Mohsin Malik]

Hello,

I am encountering a strange issue with link tables that have insert triggers for some users. Although the triggers do not have an OUTPUT Clause, direct appending to the table is not working properly (screenshot). This problem seems to be occurring for some users, but not for others. Is there a known solution or if it is a new bug? I have attempted to update the SQL Server ODBC Drivers and tried linking through Native Client and OLEDB Drivers, but none of these efforts have resolved the issue. Any assistance in resolving this problem would be greatly appreciated.

ODBC--insert on a linked table 'ContractTaxPrep' failed.


[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The target table 'dbo.ContractTaxPrep' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. (#334)

 

[sonic8]

Wild guess: Is there a SET NOCOUNT ON statement at the beginning of the trigger? If not, add it and check whether it makes a difference.

If this doesn't lead to anything, post the code of the trigger.

 

[Mohsin Malik]

Thank you for the suggestions, I have checked and SET NOCOUNT ON already exists at the beginning of the trigger and adding/removing this line does not make any difference. Below is the trigger code and following is my MSAccess version: Microsoft® Access® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20186) 64-bit.

/****** Object:  Trigger [dbo].[contracttaxprep_after_update]    Script Date: 3/3/2023 3:52:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   TRIGGER [dbo].[contracttaxprep_after_update]
ON [dbo].[ContractTaxPrep] after insert, update
as

IF(ROWCOUNT_BIG() = 0)
RETURN;

SET NOCOUNT ON
IF NOT EXISTS (SELECT 1 FROM INSERTED)
RETURN;

;with cte AS
(
    SELECT        ID, TaxReturnType, Amend, FIncome, K1, Stock, CancelledDebt, Gambling, Rentals, States, CommMFS, FC_PropSale, EmpStock
    FROM    INSERTED
    WHERE TaxReturnType < 3

    EXCEPT
    SELECT        ID, TaxReturnType, Amend, FIncome, K1, Stock, CancelledDebt, Gambling, Rentals, States, CommMFS, FC_PropSale, EmpStock
    FROM   DELETED
    WHERE TaxReturnType < 3
)
UPDATE ctp
SET ctp.Fee = v.estimated_fee
FROM dbo.contracttaxprep ctp
inner join cte on ctp.id = cte.id
inner join contracts.vw_calculate_tax_prep_fee v on v.id = ctp.id

;with cte AS
(
    SELECT        ID, TaxReturnType, Amend, TPCount
    FROM    INSERTED
    WHERE TaxReturnType >= 3

    EXCEPT
    SELECT        ID, TaxReturnType, Amend, TPCount
    FROM   DELETED
    WHERE TaxReturnType >= 3
)
UPDATE ctp
SET ctp.Fee = v.estimated_fee
FROM dbo.contracttaxprep ctp
inner join cte on ctp.id = cte.id
inner join contracts.vw_calculate_tax_prep_fee v on v.id = ctp.id

 

[sonic8]

SET NOCOUNT ON already exists at the beginning of the trigger

The code shows this is not true. It is not at the beginning of the trigger but only after the first statements of the trigger.

Do want to achieve anything different with ROWCOUNT_BIG() that is not also checked by EXISTS ... FROM INSERTED?
The ROWCOUNT_BIG() would make me uneasy. I don't know which statement's ROWCOUNT should be checked there. Was it the statement that triggered the current trigger or was it a random statement from a different trigger? - I'm not sure if this question really arises in the context of SQL Server, but I still wouldn't want to worry about it.

I'm not sure if any of this is related to the error you asking about, but these would be the first things I would put right when facing this problem.

 

[Gregory Gross]

I am encountering the same issue that Mohsin Malik described earlier. I first noticed this problem a few days ago.

For years and without any problems, I've been using Access as a front end for SQL Server databases I run locally. I use linked tables via the ODBC Driver 17 for SQL Server. I am currently running Microsoft Office 2016 Version 2302 (Build 16130.20218).

I started noticing these errors after I applied 2023-02 Cumulative Update for Windows 11 Version 22H2 for x64-based Systems (KB5022913) on March 1. I also see that MS Office Version 2302 (Build 16130.20218) released on February 28. Looking through the current MS Office release notes, I don't see any indication of changes that could be responsible for this error.

For any table with an identity column and with a trigger that fires after an insert, I'm getting this error.

Upon running a SQL Server Profiler trace to see what SQL is hitting the database from Access while attempting an insert into a table with an identity column and with insert triggers that perform basic data integrity checks with no data modification within the trigger's code, I see that Access is generating something like this SQL code:

exec sp_executesql N'INSERT INTO  "dbo"."SampleTable"  ("SampleColumn1","SampleColumn2","SampleColumn3","SampleColumn4")  OUTPUT INSERTED."SampleTableID" VALUES (@P1,@P2,@P3,@P4)',N'@P1 int,@P2 nvarchar(50),@P3 bit,@P4 bit',49,N'ABC123',0,0

I think root of the problem is the way that SQL Server is reporting the new identity column value ("SampleTableID" in my sample code above) back to Access. Has this changed in recent days? Is using OUTPUT new?

 

[Gregory Gross]

Looking more deeply into the update history on my machine, I see that the current build of MS Office (Version 2302 (Build 16130.20218)) was installed on March 2, which is right when I started seeing this error. I'm thinking that something in that build is responsible for this error. I don't think this is an ODBC issue since the current version of the ODBC Driver 17 was installed weeks ago.

 

[GPGeorge]

I'm seeing a similar report at MS Answers.

If you can provide a reproducible test case, preferably with an accdb, that would be great. I realize that's difficult because the BE is SQL Server, though.

We've alerted the Access team to the potential bug in the recent (2/28/2023) update/

 

[Gregory Gross]

I realized that I had an old laptop with SQL Server and MS Office that hasn't been updated in months. I created a test database, table, and trigger as follows:

use master;
go

create database SampleDatabase;
go

use SampleDatabase;
go

create table SampleTable
(
SampleTableID int identity not null primary key,
SampleColumn1 int null,
SampleColumn2 int null,
SampleColumn3 int null
);
go

create trigger SampleTableTrigger
on SampleTable
after insert
as
begin

    if exists    (
                select *
                from SampleTable
                where 1 = 0
                )
        begin
            rollback transaction;
            raiserror('The impossible has happened.', 16, 1);
        end;

end;
go

I created an Access database with linked table, started a trace, attempted to do an insert, and succeeded. My trace output shows a plain vanilla insert statement without OUTPUT clause:

exec sp_executesql N'INSERT INTO  "dbo"."SampleTable"  ("SampleColumn1","SampleColumn2","SampleColumn3") VALUES (@P1,@P2,@P3)',N'@P1 int,@P2 int,@P3 int',1,2,3

In a separate call, the Access database retrieved the new value for the identity column:

SELECT IDENT_CURRENT('"dbo"."SampleTable"')

When I do this same test on my current machine, my trace revealed this call:

exec sp_executesql N'INSERT INTO  "dbo"."SampleTable"  ("SampleColumn1","SampleColumn2","SampleColumn3")  OUTPUT INSERTED."SampleTableID" VALUES (@P1,@P2,@P3)',N'@P1 int,@P2 int,@P3 int',1,2,3

It looks like the new version of MS Office is combining the insert operation and the retrieval of the new identity column value into one operation. But, of course, this is causing the error when a trigger is on that table.

Thanks for alerting the Access team about this problem.

 

[GPGeorge]

The MS Access team has identified the issue and will address the problem soon. Details to follow.

Thanks for calling this to their attention.

 

[ptodd]

Has this issue been resolved? I started getting this error last week.