I am facing an issue with my stored procedure in SQL Azure, which has responsibility to create records on a table. When the same stored procedure is called more than once / or more than twice, it fails.
ALTER PROCEDURE [dbo].[usp_ActiveTouchPoints]
-- Add the parameters for the stored procedure here
@pAppOrgSyncId uniqueidentifier,
@pTouchPointId uniqueidentifier = null,
@pDoActive bit = 1,
@pRetValue BIT OUTPUT
AS BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--SET XACT_ABORT OFF
DECLARE @TransactionName varchar(20) = substring(CONVERT(NVARCHAR(50), NEWID()), 25,12);
BEGIN TRANSACTION @TransactionName
-- 2. Copy Attribute mappings
BEGIN TRY
DECLARE @vTaskIds Table(TaskId uniqueidentifier)
DECLARE @vCreatedTasks VARCHAR(MAX)
print 'usp_ActiveInitialTouchPoints for OrgAPPSYNCID : ' + CAST(@pAppOrgSyncId as VARCHAR(100))
Exec usp_ActiveInitialTouchPoints @pAppOrgSyncId = @pAppOrgSyncId,
@pTouchPointId = @pTouchPointId,
@pDoActive = @pDoActive,
@pTaskIds = @vCreatedTasks OUTPUT
DECLARE @vReverseOrgAppSyncId uniqueidentifier
DECLARE @vOrgId uniqueidentifier
DECLARE @vTemplateId uniqueidentifier
DECLARE @vRevTemplateId uniqueidentifier
SELECT @vTemplateId = TemplateId
From OrgAppSync
where OrgAppSyncId = @pAppOrgSyncId
SELECT @vReverseOrgAppSyncId = ReverseOrgAppSyncId,
@vOrgId = OrgId,
@vRevTemplateId = TemplateId
From OrgAppSync
where OrgAppSyncId = @pAppOrgSyncId
DECLARE @vRevCreatedTasks VARCHAR(MAX)
print 'usp_ActiveInitialTouchPoints for OrgAPPSYNCID : ' + CAST(@vReverseOrgAppSyncId as VARCHAR(100))
Exec usp_ActiveInitialTouchPoints @pAppOrgSyncId = @vReverseOrgAppSyncId,
@pTouchPointId = @pTouchPointId,
@pDoActive = @pDoActive,
@pTaskIds = @vRevCreatedTasks OUTPUT
--INSERT INTO @vTaskIds
-- Select * from FN_STRINGTOTABLE(@vCreatedTasks, ',')
--INSERT INTO @vTaskIds
-- Select * from FN_STRINGTOTABLE(@vRevCreatedTasks, ',')
Insert into @vTaskIds
SELECT TaskId from Task
Where OrgAppSyncId in (@pAppOrgSyncId, @vReverseOrgAppSyncId)
DECLARE @vTaskId uniqueidentifier
DECLARE @vParentTaskId uniqueidentifier
DECLARE @vTouchpointId uniqueidentifier
DECLARE @vTouchpointDependencyId uniqueidentifier
DECLARE @vEventId INT
DECLARE @vOrdering INT
DECLARE @vFlag INT
print 'usp_ActiveTouchPoints - CTD'
-- Create Dependent Task before creating the TaskDependencies
IF @pTouchpointId IS NOT NULL
BEGIN
DECLARE @vReturnVal BIT
Declare @vDTouchpointId uniqueidentifier
Declare @vOrgAppSyncId uniqueidentifier
DECLARE TPD CURSOR LOCAL
FOR
SELECT TouchpointId from TouchpointDependency
where ParentTouchpointId = @pTouchpointId
OPEN TPD
FETCH NEXT FROM TPD into @vDTouchpointId
WHILE @@Fetch_status = 0
BEGIN
Select @vOrgAppSyncId = OrgAppSync.OrgAppSyncId
from
Touchpoint
Inner join Template
on Touchpoint.TemplateId = Template.Templateid
Inner join OrgAppSync
on OrgAppSync.TemplateId = Template.TemplateId
where
OrgAppSync.OrgAppSyncId in (@pAppOrgSyncId, @vReverseOrgAppSyncId)
And Touchpoint.TouchpointId = @vDTouchpointId
Exec [dbo].[usp_ActiveTouchPoints] @pAppOrgSyncId = @vOrgAppSyncId, @pTouchPointId = @vDTouchpointId, @pDoActive = @pDoActive, @pRetValue = @vReturnVal OUTPUT
FETCH NEXT FROM TPD into @vDTouchpointId
END
CLOSE TPD;
DEALLOCATE TPD;
END
DECLARE CTD CURSOR LOCAL
FOR
SELECT TouchpointDependency.ParentTouchpointId
, TouchpointDependency.TouchpointDependencyId
, TouchpointDependency.EventId
, TouchpointDependency.Ordering
, Task.TaskId
, IsNull(TouchpointDependency.Flag, 0) from Task
Inner join TouchpointDependency on Task.TouchpointId = TouchpointDependency.ParentTouchpointId
AND IsNull(TouchpointDependency.Active, 1) = 1
where Task.TaskId in (select TaskId from @vTaskIds)
OPEN CTD
FETCH NEXT FROM CTD into @vTouchpointId, @vTouchpointDependencyId, @vEventId, @vOrdering, @vParentTaskId, @vFlag
WHILE @@Fetch_status = 0
BEGIN
IF NOT EXISTS(SELECT * FROM TASKDEPENDENCY where TouchpointDependencyId = @vTouchpointDependencyId
And ParentTaskId = @vParentTaskId)
BEGIN
print 'usp_ActiveTouchPoints - TDD'
Insert into TaskDependency
(
TaskDependencyId,
ParentTaskId,
TouchpointDependencyId,
TaskId,
EventId,
Ordering,
ACTIVE,
Flag
)
SELECT NEWID()
, @vParentTaskId
, @vTouchpointDependencyId
, TaskId
, @vEventId
, @vOrdering
, 1
, @vFlag
From Task
Inner join TouchpointDependency
on Task.TouchpointId = TouchpointDependency.TouchpointId
where TouchpointDependency.TouchpointDependencyId = @vTouchpointDependencyId
AND Task.TaskId in (select TaskId from @vTaskIds)
END
FETCH NEXT FROM CTD into @vTouchpointId, @vTouchpointDependencyId, @vEventId, @vOrdering, @vParentTaskId, @vFlag
END
CLOSE CTD;
DEALLOCATE CTD;
IF XACT_STATE() = 1
BEGIN
COMMIT TRANSACTION @TransactionName
END
SET @pRetValue = 1
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION @TransactionName
END
INSERT INTO ERRORLOG
SELECT
ERROR_NUMBER(),
ERROR_MESSAGE(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_LINE(),
ERROR_PROCEDURE(),
NEWID()
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
SET @pRetValue = 0
END CATCH
END
There is lot of inserts happening in the Stored procedure. A number of Insert / Update. It also opens a Cursor but I marked it as local scope cursor.
Now this stored procedure is called using Entity Framework, and a website calls it using WebAPI.
Now the problem is when I am calling the same stored procedure multiple times, it says :
"Cannot roll back 76C80318BB6A. No transaction or savepoint of that name was found"
But the same works fine when I call it once and wait until I finish and then call it again. I have no idea hoe to fix the issue. Do you think there is an issue with code ?
