question

mooriginal avatar image
mooriginal asked

Tsql batch script does not stop executing

We have the following update script we are trying to do in batches.

Currently the execution never stops running - so needed help understanding what's wrong with the logic or syntax thats missing.


--Create Temp table
create table #TempIFSC
(
    [EnrolledPaymentMethodAccountId] uniqueidentifier, 
    [PaymentAccountId] uniqueidentifier, 
    [ExternalSystemId] int, 
    [EnrolledPaymentMethodAccountStatusId] int, 
    [Extension] xml,
    [EnrollmentAccountRevisionId] int,
[BankName] NVARCHAR(100) NULL,
[BankBranch] NVARCHAR(100) NULL,
[IFSC] NVARCHAR(100) NULL
)

--Insert record into temp table who does not have IFSC code and currency INR
Insert Into #TempIFSC 
Select EP.[EnrolledPaymentMethodAccountId],EP.[PaymentAccountId],EP.[ExternalSystemId],EP.[EnrolledPaymentMethodAccountStatusId],CAST(EP.[Extension] AS XML),EP.[EnrollmentAccountRevisionId],NULL,NULL,NULL
from [dbo].[EnrolledPaymentMethodAccount] EP With (NOLOCK)
INNER JOIN [dbo].[PaymentAccount] PA With (NOLOCK) ON EP.PaymentAccountId = PA.PaymentAccountId and PA.CurrencyCode = 'INR'
where ISNULL(CAST(EP.Extension AS XML).value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = '' 
and EP.ExternalSystemId = 52 
and EP.EnrolledPaymentMethodAccountStatusId = 1 

--Update the BankName and BankBranch value in temp table
Declare @Rowcount INT = 1;
WHILE (@Rowcount > 0)  
Begin
UPDATE TOP (4999) #TempIFSC
SET
BankName = b.Name,
BankBranch = TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)')
FROM #TempIFSC TMP
INNER JOIN [dbo].[Bank] b ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)')
Where b.ExternalSystemId = TMP.ExternalSystemId and b.CurrencyCode = 'INR'

SET @Rowcount = @@ROWCOUNT;

print @Rowcount

CHECKPOINT; 
End

--Update IFSC value in temp table
Declare @IFSCRowcount INT = 1;
WHILE (@IFSCRowcount > 0)   
BEGIN
UPDATE TOP (4999) #TempIFSC
SET IFSC = IM.IFSC
FROM #TempIFSC TMP
INNER JOIN [taurus].[IFSCMasterList] IM (NOLOCK) ON TMP.BankBranch = IM.BankBranchName and TMP.BankName = IM.BankName

SET @IFSCRowcount = @@ROWCOUNT;

CHECKPOINT;   --<-- to commit the changes with each batch
End


--Remove blank node of IFSC
Declare @XMLRowcount INT = 1;

WHILE (@XMLRowcount > 0)   
BEGIN
DECLARE @NodeName NVARCHAR(500) = 'NameValueEntity'
Update TOP (4999) #TempIFSC
SET Extension.modify('delete /ArrayOfNameValueEntity/*[local-name(.) eq sql:variable("@NodeName")][6]') 
FROM #TempIFSC
Where Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1 and ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

SET @XMLRowcount = @@ROWCOUNT;
Print @XMLRowcount
CHECKPOINT;   --<-- to commit the changes with each batch
END

--Update extension value in temp table
Declare @CodeRowcount INT = 1;

WHILE (@CodeRowcount > 0)   
BEGIN
Update TOP (4999) #TempIFSC
SET Extension.modify('insert <NameValueEntity><Name>IFSCCode</Name><Value>{sql:column("#TempIFSC.IFSC")}</Value></NameValueEntity>
into (/ArrayOfNameValueEntity)[1]')
FROM #TempIFSC
WHERE ISNULL(IFSC, '') <> '' 

SET @CodeRowcount = @@ROWCOUNT;

CHECKPOINT;   --<-- to commit the changes with each batch
END

tsqlcodemssql2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Your never breaking out of the loop as your always updating the same 4999 rows.


You need a way to pull a batch out something like a done column which is updated at the end of the loop then the start of the loop pulls the next 4999 where done is 0 so that your actually going through the data instead of being constantly in the same set of data.

Or you have an identity column where you pull the data based on the id

Batch size. Start of batch

Where id between start of batch and startofbatch + batch size


You need to iterate through your data set which your not doing.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mooriginal avatar image
mooriginal answered

have update the script with the following but we still have a problem with an infinite loop


--Create Temp table
create table #TempIFSC
(
    [EnrolledPaymentMethodAccountId] uniqueidentifier, 
    [PaymentAccountId] uniqueidentifier, 
    [ExternalSystemId] int, 
    [EnrolledPaymentMethodAccountStatusId] int, 
    [Extension] xml,
    [EnrollmentAccountRevisionId] int,
[BankName] NVARCHAR(100) NULL,
[BankBranch] NVARCHAR(100) NULL,
[IFSC] NVARCHAR(100) NULL,
[IsUpdate] bit NULL
)

--Insert record into temp table who does not have IFSC code and currency INR
Insert Into #TempIFSC 
Select EP.[EnrolledPaymentMethodAccountId],EP.[PaymentAccountId],EP.[ExternalSystemId],EP.[EnrolledPaymentMethodAccountStatusId],CAST(EP.[Extension] AS XML),EP.[EnrollmentAccountRevisionId],NULL,NULL,NULL,0
from [dbo].[EnrolledPaymentMethodAccount] EP With (NOLOCK)
INNER JOIN [dbo].[PaymentAccount] PA With (NOLOCK) ON EP.PaymentAccountId = PA.PaymentAccountId and PA.CurrencyCode = 'INR'
where ISNULL(CAST(EP.Extension AS XML).value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = '' 
and EP.ExternalSystemId = 52 
and EP.EnrolledPaymentMethodAccountStatusId = 1 

--Update the BankName and BankBranch value in temp table
Declare @Rowcount INT = 1;
WHILE (@Rowcount > 0)  
Begin
UPDATE TOP (4999) #TempIFSC
SET
BankName = b.Name,
BankBranch = TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)'),
IFSC = IM.IFSC,
IsUpdate = 1
FROM #TempIFSC TMP
INNER JOIN [dbo].[Bank] b WITH(NOLOCK) ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)') AND b.ExternalSystemId = TMP.ExternalSystemId 
INNER JOIN [taurus].[IFSCMasterList] IM WITH(NOLOCK) ON b.Name = IM.BankName AND TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)') = IM.BankBranchName
Where b.CurrencyCode = 'INR'
AND b.Name IS NOT NULL
AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)'),'') != ''

SET @Rowcount = @@ROWCOUNT;

print @Rowcount

RETURN 
End

--Remove the records which does not have BankName or BankBranch
DELETE FROM #TempIFSC WHERE BankName IS NULL OR BankBranch IS NULL OR IFSC IS NULL

--Update IFSC value in temp table
--Declare @IFSCRowcount INT = 1;
--WHILE (@IFSCRowcount > 0)   
--BEGIN
--UPDATE TOP (4999) #TempIFSC
--SET IFSC = IM.IFSC
--FROM #TempIFSC TMP
--INNER JOIN [taurus].[IFSCMasterList] IM WITH(NOLOCK) 
--ON TMP.BankBranch = IM.BankBranchName and TMP.BankName = IM.BankName
--WHERE IM.BankName IS NOT NULL 
--AND IM.IFSC IS NOT NULL
--AND IM.BankBranchName IS NOT NULL
----AND TMP.Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1
----OR ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

--SET @IFSCRowcount = @@ROWCOUNT;

--CHECKPOINT;   --<-- to commit the changes with each batch
--End


--Remove blank node of IFSC
Declare @XMLRowcount INT = 1;

WHILE (@XMLRowcount > 0)   
BEGIN
DECLARE @NodeName NVARCHAR(500) = 'NameValueEntity'
Update TOP (4999) #TempIFSC
SET Extension.modify('delete /ArrayOfNameValueEntity/*[local-name(.) eq sql:variable("@NodeName")][6]') 
FROM #TempIFSC
Where Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1 and ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

SET @XMLRowcount = @@ROWCOUNT;
Print @XMLRowcount
CHECKPOINT;   --<-- to commit the changes with each batch
END

--Update extension value in temp table
Declare @CodeRowcount INT = 1;

WHILE (@CodeRowcount > 0)   
BEGIN
Update TOP (4999) #TempIFSC
SET Extension.modify('insert <NameValueEntity><Name>IFSCCode</Name><Value>{sql:column("#TempIFSC.IFSC")}</Value></NameValueEntity>
into (/ArrayOfNameValueEntity)[1]')
--FROM #TempIFSC
WHERE ISNULL(IFSC,'') <> ''
AND BankName IS NOT NULL
AND IsUpdate = 1
AND ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

SET @CodeRowcount = @@ROWCOUNT;

CHECKPOINT;   --<-- to commit the changes with each batch
END

--Actual table updated from ##TempIFSC
Declare @TblRowcount INT = 1;

--WHILE (@TblRowcount > 0)   
--BEGIN
--Update TOP (4999) [dbo].[EnrolledPaymentMethodAccount_20220921]
--SET [Extension] = CAST(TMP.[Extension] AS nvarchar(max))
--FROM [dbo].[EnrolledPaymentMethodAccount_20220921] EP WITH(NOLOCK)
--INNER JOIN #TempIFSC TMP WITH(NOLOCK) ON EP.[PaymentAccountId] = TMP.PaymentAccountId AND EP.[EnrolledPaymentMethodAccountId] = TMP.[EnrolledPaymentMethodAccountId]
--Where EP.[ExternalSystemId] = TMP.ExternalSystemId
--AND EP.[EnrolledPaymentMethodAccountStatusId] = TMP.EnrolledPaymentMethodAccountStatusId
--AND EP.[EnrollmentAccountRevisionId] = TMP.EnrollmentAccountRevisionId
--AND TMP.IsUpdate = 1
--AND TMP.IFSC <> '' OR TMP.IFSC IS NULL
--AND TMP.BankName IS NOT NULL AND TMP.BankBranch IS NOT NULL
--AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') != ''

--SET @TblRowcount = @@ROWCOUNT;

--CHECKPOINT;   --<-- to commit the changes with each batch
--END

Select * from #TempIFSC WITH(NOLOCK)

DROP Table #TempIFSC
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mooriginal avatar image
mooriginal answered

This is the part that is still looping indefinitely

image-2.png


image-2.png (41.1 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.