Dataflux Error - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

[nbkohkt]

We are getting the below error in one of our job.

2019-07-10T09:33:58,059 ERROR [ROOT][53A9AEFD4BDBEE9E_JOB_7_0][539E8D0EF9C1EC5D_JOB_13_0][DATAFLOW_10_0]Node DATAFLOW_10 error: [1:EXPRESSION:  Start Process Control] Expression Plugin - [25000] [DataFlux][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903);[25000] [DataFlux][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (50000).

When we checked our job, it is coming from the below expression step, but it has the proper begin and end transaction statement, 

Could you please help me where the error comes from?

Pre-processing expression.

hidden string default_dsn
hidden string PKG
hidden dbconnection conn
hidden dbcursor db_curs
hidden dbstatement stmt
integer stats_process_id
string(25) ENTITY_TYPE
string STAT_LOCATION
integer MDM_STAGING_ID
hidden integer entity_type_id
hidden integer srcSysId
hidden string(50) process_name
hidden string(50) process_type
hidden string(513) user
hidden integer i
default_dsn = getvar("MDM_DSN")
PKG = getvar("MDM_PACKAGE")
ENTITY_TYPE = 'PERSON'
STAT_LOCATION = 'POST_LIVE'
MDM_STAGING_ID=getvar('MDM_STAGING_ID')
entity_type_id = getvar("ENTITY_TYPE_ID")
srcSysId = getvar("srcSysId")
process_name = 'GATHER POST-LIVE STATS'
process_type = 'Index Rebuild'
user = getvar("DFINTL_DIS_USER",username())
i = 0

conn=dbconnect(default_dsn)
stmt=conn.prepare("{CALL " & PKG & "MDM_START_PROCESS_CONTROL(?, ?, ?, ?, ?, ?)}")
stmt.setparaminfo(0, "integer", 15)
stmt.setparameter(0, MDM_STAGING_ID)
stmt.setparaminfo(1, "integer", 15)
stmt.setparameter(1, entity_type_id)
stmt.setparaminfo(2, "integer", 15)
stmt.setparameter(2, srcSysId)
stmt.setparaminfo(3, "string", 50)
stmt.setparameter(3, process_name)
stmt.setparaminfo(4, "string", 50)
stmt.setparameter(4, process_type)
stmt.setparaminfo(5, "string", 513)
stmt.setparameter(5, user)

Expression.

if (i == 0)
begin
i = i + 1
/* Start process control */
db_curs = stmt.select()
db_curs.next()
stats_process_id = db_curs.valueinteger(0)
db_curs.release()
stmt.release()
conn.release()
end

[VincentRejany]

Hi

It seems that you are using SAS MDM, and that the code you sent is part of the post process updates.

Could you specify which process job and which node are exactly in error? Was this process working before? Is it a production environment?

Cheers

[nbkohkt]

Thanks for your response,

Process job is 'add_update_person'.

yes, it worked before, and this issue happens only in production and we know there are lot process that runs in parallel in production that updates mdm_process_conrol table. But we are getting this error frequently nowadays. 

We added this begin and end transaction statements, but still we are getting this error. We know that this is not a deadlock error. But not sure why we are getting this error. We checked with DBA and he confirmed he is not seeing any thing weird in the SQL server logs.

I have also attached the screenshot of the process job and the node in error.

Node name : Gather Post-Live Stats

[VincentRejany]

Would suggest to open a ticket by tech support and to look at the TSQL code of the procedure called. Iirw it is supposed to read/write into the process control table.

[nbkohkt]

Below is the stored procedures, when i checked the syntax of these, i dont see any issue, could you please check it for me?

USE [SASMDM];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE [sasmdm].[mdm_start_process_control]
(
@parm_staging_id INT,
@parm_entity_type_id INT,
@parm_src_sys_id INT,
@parm_process_name VARCHAR(50),
@parm_process_type VARCHAR(50),
@parm_user NVARCHAR(513)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @process_id INT
EXEC @process_id=[mdm_start_process_control_next] @parm_staging_id,@parm_entity_type_id,@parm_src_sys_id,@parm_process_name,@parm_process_type,@parm_user
select @process_id as process_id
END

GO

--------------

USE [SASMDM];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE [sasmdm].[mdm_start_process_control_next]
(
@parm_staging_id INT,
@parm_entity_type_id INT,
@parm_src_sys_id INT,
@parm_process_name VARCHAR(50),
@parm_process_type VARCHAR(50),
@parm_user NVARCHAR(513)
)
AS
DECLARE @timestamp datetime;
DECLARE @process_id INT;
SET NOCOUNT ON
BEGIN TRY

SELECT @timestamp = GETDATE()

BEGIN TRAN
INSERT INTO [MDM_PROCESS_CONTROL]
(
mdm_staging_id, mdm_entity_type_id, mdm_src_sys_id, process_name, process_type, process_running, process_user, process_start_dttm)
VALUES
(@parm_staging_id, @parm_entity_type_id, @parm_src_sys_id, @parm_process_name, @parm_process_type, 'Y', @parm_user, @timestamp)

COMMIT TRAN

SELECT @process_id = process_id
FROM mdm_process_control
WHERE process_name = @parm_process_name
AND mdm_entity_type_id = @parm_entity_type_id
AND mdm_staging_id = @parm_staging_id
AND mdm_src_sys_id = @parm_src_sys_id
AND process_running = 'Y'
AND process_start_dttm = @timestamp;

RETURN @process_id

END TRY
BEGIN CATCH
ROLLBACK TRAN

DECLARE @error_messages VARCHAR(2000)
DECLARE @errorSeverity INT
DECLARE @errorState INT
DECLARE @errorStatus INT
DECLARE @errorProc VARCHAR(150)
DECLARE @errorDate DATETIME

DECLARE @mdm_error_status VARCHAR(10)
DECLARE @mdm_error_messages VARCHAR(4000)
DECLARE @errorSeverityString VARCHAR(100)
DECLARE @errorStateString VARCHAR(100)

SELECT @errorStatus = ERROR_NUMBER()
SELECT @error_messages = ERROR_MESSAGE()
SELECT @errorSeverity = ERROR_SEVERITY()
SELECT @errorState = ERROR_STATE()
SELECT @errorProc = ERROR_PROCEDURE()
SELECT @errorDate = getDate()

SELECT @errorSeverityString = CONVERT(VARCHAR,@errorSeverity)
SELECT @errorStateString = CONVERT(VARCHAR,@errorState)
SELECT @mdm_error_status = CONVERT(VARCHAR,@errorStatus)

SELECT @mdm_error_messages = @errorProc+ ' : '+@error_messages+
' : '+@errorSeverityString+
' : '+@mdm_error_status+
' : '+@errorStateString
-- Log Error
EXEC [mdm_log_error] 'MDM_PROCESS_CONTROL',null,0,null,'mdm_start_process_control_next',@mdm_error_status,@mdm_error_messages,@errorDate

-- Raise Error
RAISERROR(@error_messages, @errorSeverity, @errorState)
END CATCH

GO

[nbkohkt]

Hi,

Could you please confirm the syntax of the above SQL procedure is correct? Do i need insert Begin after the below statement

DECLARE @process_id INT;

and End in at the last statement?

[nbkohkt]

Can anyone please confirm if the below SQL procedure is good.

We are getting the below error when we call this SQL procedure from SAS MDM Dataflux job.

2019-07-31T08:57:47,916 ERROR [ROOT][53A9AEFD4BDBEE9E_JOB_7_0][539E8D0EF9C1EC5D_JOB_13_0][DATAFLOW_10_0]Node DATAFLOW_10 error: [1:EXPRESSION:Start Process Control] Expression Plugin - [25000] [DataFlux][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903);[25000] [DataFlux][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Below is the stored procedures, when i checked the syntax of these, i dont see any issue, could you please check it for me?

USE [SASMDM];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE [sasmdm].[mdm_start_process_control]
(
@parm_staging_id INT,
@parm_entity_type_id INT,
@parm_src_sys_id INT,
@parm_process_name VARCHAR(50),
@parm_process_type VARCHAR(50),
@parm_user NVARCHAR(513)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @process_id INT
EXEC @process_id=[mdm_start_process_control_next] @parm_staging_id,@parm_entity_type_id,@parm_src_sys_id,@parm_process_name,@parm_process_type,@parm_user
select @process_id as process_id
END

GO

--------------

USE [SASMDM];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE [sasmdm].[mdm_start_process_control_next]
(
@parm_staging_id INT,
@parm_entity_type_id INT,
@parm_src_sys_id INT,
@parm_process_name VARCHAR(50),
@parm_process_type VARCHAR(50),
@parm_user NVARCHAR(513)
)
AS
DECLARE @timestamp datetime;
DECLARE @process_id INT;
SET NOCOUNT ON
BEGIN TRY

SELECT @timestamp = GETDATE()

BEGIN TRAN
INSERT INTO [MDM_PROCESS_CONTROL]
(
mdm_staging_id, mdm_entity_type_id, mdm_src_sys_id, process_name, process_type, process_running, process_user, process_start_dttm)
VALUES
(@parm_staging_id, @parm_entity_type_id, @parm_src_sys_id, @parm_process_name, @parm_process_type, 'Y', @parm_user, @timestamp)

COMMIT TRAN

SELECT @process_id = process_id
FROM mdm_process_control
WHERE process_name = @parm_process_name
AND mdm_entity_type_id = @parm_entity_type_id
AND mdm_staging_id = @parm_staging_id
AND mdm_src_sys_id = @parm_src_sys_id
AND process_running = 'Y'
AND process_start_dttm = @timestamp;

RETURN @process_id

END TRY
BEGIN CATCH
ROLLBACK TRAN

DECLARE @error_messages VARCHAR(2000)
DECLARE @errorSeverity INT
DECLARE @errorState INT
DECLARE @errorStatus INT
DECLARE @errorProc VARCHAR(150)
DECLARE @errorDate DATETIME

DECLARE @mdm_error_status VARCHAR(10)
DECLARE @mdm_error_messages VARCHAR(4000)
DECLARE @errorSeverityString VARCHAR(100)
DECLARE @errorStateString VARCHAR(100)

SELECT @errorStatus = ERROR_NUMBER()
SELECT @error_messages = ERROR_MESSAGE()
SELECT @errorSeverity = ERROR_SEVERITY()
SELECT @errorState = ERROR_STATE()
SELECT @errorProc = ERROR_PROCEDURE()
SELECT @errorDate = getDate()

SELECT @errorSeverityString = CONVERT(VARCHAR,@errorSeverity)
SELECT @errorStateString = CONVERT(VARCHAR,@errorState)
SELECT @mdm_error_status = CONVERT(VARCHAR,@errorStatus)

SELECT @mdm_error_messages = @errorProc+ ' : '+@error_messages+
' : '+@errorSeverityString+
' : '+@mdm_error_status+
' : '+@errorStateString
-- Log Error
EXEC [mdm_log_error] 'MDM_PROCESS_CONTROL',null,0,null,'mdm_start_process_control_next',@mdm_error_status,@mdm_error_messages,@errorDate

-- Raise Error
RAISERROR(@error_messages, @errorSeverity, @errorState)
END CATCH

GO