Executing a stored procedure in Transaction notification SP

[sanderappel]

Hi experts,

I need to check three different VAT-codes in five different document types, and to avoid the transaction notification blowing up, I have created a new stored procedure, which is executed by a trigger in the transaction notification.

If the custom SP triggers an error it does display correctly in SAP and blocks the creation/update of the document. If however no error is triggered (because no incorrect VAT-codes where used), the rest of the transaction notification isn't triggered.

I have created an easier code on a test server was able to replicate this: if the custom stored procedure should trigger an error, it is triggered successfully and displayed in SAP, but if the custom SP should NOT trigger an error, the rest of the transaction notification isn't executed.

See below what I created in test.
The custom stored procedure:

ALTER PROCEDURE [dbo].[SE_TEST]
	-- Add the parameters for the stored procedure here
	@object_type nvarchar(30), 				-- SBO Object Type
@transaction_type nchar(1),			-- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)

	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Return values
declare @error  int				-- Result (0 for no error)
declare @error_message nvarchar (200) 		-- Error string to be displayed
select @error = 0
select @error_message = N'Ok'

    -- Insert statements for procedure here
IF 1=2 
BEGIN 
		SET @error = '99'
	SET @error_message = 'This customer is a test'

END
select @error, @error_message
end

Naturally, this will never trigger the error message.

But adding or updating a sales quotation should still always be blocked, because of what follows in the transaction notification after executing this SP:

IF @object_type in ('23','17','15','14','13') and @transaction_type IN ('A','U')

	BEGIN EXEC SE_TEST     
	@object_type
  ,@transaction_type
  ,@num_of_cols_in_key
  ,@list_of_key_cols_tab_del
  ,@list_of_cols_val_tab_del
  END

  IF @object_type = '23' and @transaction_type IN ('A','U')

  BEGIN
		SET @error = '99'
	SET @error_message = 'This customer is blocked for creating Sales Orders, Sales Invoices and Deliveries'
	END

This isn't the case however, any sales quotation can be added or updated (unless I update the custom SP to IF 1=1, which will always trigger the error message).

Does anyone know whether this is expected behavior with the transaction notification and SQL Server, or whether I'm missing anything.

I know that in the Post Transaction notice, executing a custom SP from the PTN will still execute the rest of the PTN.

[Johan_H]

Hi,

First thing that comes to mind is that SE_TEST returns an error. That would stop the rest of the code after it from being executed.

Regards,

Johan

[sanderappel]

Hi Johan, thanks for the response, but I have confirmed that the SP does not return an error.

[Johan_H]

I found this question about a similar issue, perhaps it helps.

[Johan_H]

Come to think of it, you can also try to end the previous statement with a semicolon (;)

so something like this:

EXEC SE_TEST ... ;
IF ...

[sanderappel]

Thanks for the help, but unfortunately this still does not resolve the issue, the rest of the transaction notification isn't executed.