BUG: RPC May Cause SQL Server to Generate Access Violation

  - Microsoft SQL Server version 4.2

SYMPTOMS

When a Remote Procedure Call (RPC) is made to execute a stored procedure which updates a table with a trigger containing a call to another stored procedure which in turn contains an unmatched COMMIT TRAN or ROLLBACK TRAN statement, SQL Server may generate an Access Violation and terminate.

This does not happen if the ROLLBACK TRAN or COMMIT TRAN statement itself is contained in the trigger, instead of being in the stored procedure that is called by the trigger.

For example, consider the following script:

   CREATE TABLE testtrig ( i INT,  j INT )
   go
   CREATE PROC myproc @P0 INT, @P1 INT, @P2 INT AS
    UPDATE testtrig SET i=@P0,j=2 WHERE i=@P1 AND j=@P2
   go
   CREATE PROC trigproc AS  ROLLBACK TRAN /* or a COMMIT TRAN */
   go
   CREATE TRIGGER trg ON testtrig FOR UPDATE AS
    EXECUTE trigproc
   go

WORKAROUND

TO workaround this problem, avoid calling the stored procedure within the trigger if it contains an unmatched rollback (or commit). Instead, place the rollback (or commit) directly within the trigger.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for Windows NT. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.