SP stopped working after build update

nldeshpande · September 29, 2022, 7:25am

Hi All,

I have a stored procedure that creates #temptb, then inserts some rows in it. After that it updates a few columns, then alters the same #temptb, adds a few columns and updates them as well. All has been working fine till there was a update and the SQL server build 13.0.5026.0 got updated to build 13.0.5882.1

The SP is still working flawlessly on other SQL servers where this update was not applied. So apparently there is nothing wrong with the code within the stored procedure.

However, on the affected server, the stored procedure does not throw up any errors and keeps on running endlessly without returning any results or errors.

What could be the resolution of this problem?

Thanks in advance.

Nilesh Deshpande

JamesK · September 29, 2022, 11:25am

I haven't looked up the details of the specific SQL Server update, but it seems like a minor update. So my guess would be that the issue is caused by either something in the data, or another procedure or process holding a lock that is blocking your stored proc.

To debug the first issue, you will have to step through the code and see where it is being held up. To see if some process is blocking your stored proc, run sp_who2 from a SSMS query window and see if there is anything in blk_by column that is holding up your stored proc.

nldeshpande · September 29, 2022, 1:23pm

Hello JamesK,

Thanks for your reply.

I am executing the stored procedure using the query window in SSMS. There are absolutely no other connections to the server at that time. So the suspicion of rows being locked is eliminated.

I restored the database on two other SQL servers where the update was not applied, and there in the query window of SSMS, the stored procedure returns the result set in just 7 seconds.

It is a single user environment and is used once every fortnight for a critical application. Currently I have replaced the production server with a development one. But I need to resolve the issue on the prod server and put it back to use.

I am suspecting there is some issue with the TempDB or the MSDB where #temptb is processed. But I am not sure.

Any help is always welcome.

Thanks

Nilesh Deshpande

Ifor · September 29, 2022, 3:01pm

It looks as though you have upgraded from SQL2016 SP2 to SQL2016 SP2 CU17. You should really upgrade to SQL2016 SP3 GDR due to the security fixes in the GDR.

As for your SP, I suggest you manually run each statement in the proc until you isolate where the problem is.

nldeshpande · September 30, 2022, 1:13am

Hi,

"It looks as though you have upgraded from SQL2016 SP2 to SQL2016 SP2 CU17. You should really upgrade to SQL2016 SP3 GDR due to the security fixes in the GDR."

I will certainly try this suggestion.

"As for your SP, I suggest you manually run each statement in the proc until you isolate where the problem is."

I guess you overlooked the fact, that the same SP runs perfectly and returns results within 7 seconds with the same data on two other SQL server instances where the update is not applied.

Thanks

Nilesh Deshpande

Ifor · September 30, 2022, 7:43am

No. Unless you are using trace flag 4199 CUs should not alter the shape of the query plan. I suspect something else has changed on your server. Also adding columns to a temp table sounds questionable due to recompiles/expansive updates etc - I would be inclined to create the temp table with the extra columns as a default to start with.

nldeshpande · September 30, 2022, 8:38am

Hi,

That is what I am suspecting too.. but i am falling short of ideas, where to look out for.

Any pointers in that direction are welcome.

Thanks

Nilesh Deshpande

Ifor · September 30, 2022, 8:52am

I suggest you manually run each statement in the proc until you isolate where the problem is.

yosiasz · September 30, 2022, 12:50pm

Please post the stored procedure. If you want the doctor to check health status you got to expose things

nldeshpande · September 30, 2022, 1:48pm

Here is the SP if you want. However, I would like to remind that this same SP runs smoothly and returns the correct resultset on other servers where the update is not applied.

=====CODE STARt===

CREATE procedure [dbo].[CheckInputsBeforeCalcs]

@PRODGRP INT =0,
@PERIOD INT =3,
@USERID INT =18
As

set nocount on

create table #Temptb
(
SRC INT,
SRCSAPCODE VARCHAR(10),
SOURCE VARCHAR(50),
DEST INT,
DSTSAPCODE VARCHAR(10),
DESTINATION VARCHAR(50),
VATINDICATOR VARCHAR(10),
PRODGRP INT,
MATERIAL VARCHAR(10),
MODECODE INT,
MODENAME VARCHAR(15),
DISTANCE FLOAT,
FREIGHTRATE DECIMAL(10,5),
FREIGHT DECIMAL(10,2),
DSTSTATEID INT,
SRCSTATEID INT,
SERTAXONFREIGHT DECIMAL(10,2),
TOTALFREIGHT DECIMAL(10,2),
SRCSTATE VARCHAR(25),
DESTSTATE VARCHAR(25),
SRCCO VARCHAR(50),
DSTCO VARCHAR(50)
)
IF @PRODGRP<>0
BEGIN
INSERT INTO #TEMPTB SELECT SOURCE,NULL,NULL,DESTINATION,NULL,NULL,NULL,PRODGRP,NULL,MODE,NULL,DISTANCEOVERRIDE,0,0,0,0,0,0,NULL,NULL,NULL,NULL FROM SB_LINKAGE WHERE PRODGRP=@PRODGRP AND ENABLED='TRUE'
END
ELSE
BEGIN
INSERT INTO #TEMPTB SELECT SOURCE,NULL,NULL,DESTINATION,NULL,NULL,NULL,PRODGRP,NULL,MODE,NULL,DISTANCEOVERRIDE,0,0,0,0,0,0,NULL,NULL,NULL,NULL FROM SB_LINKAGE WHERE ENABLED='TRUE'
END
UPDATE #TEMPTB SET SRCSAPCODE=(SELECT LOCCODESAP FROM SB_LOC WHERE LOCCODESB=#TEMPTB.SRC)
UPDATE #TEMPTB SET SOURCE=(SELECT LOCATION FROM SB_LOC WHERE LOCCODESB=#TEMPTB.SRC)
UPDATE #TEMPTB SET DSTSAPCODE=(SELECT LOCCODESAP FROM SB_LOC WHERE LOCCODESB=#TEMPTB.DEST)
UPDATE #TEMPTB SET DESTINATION=(SELECT LOCATION FROM SB_LOC WHERE LOCCODESB=#TEMPTB.DEST)
UPDATE #TEMPTB SET MATERIAL=(SELECT PRODGRADE FROM SB_PROD WHERE PRODGRADESBCODE=#TEMPTB.PRODGRP)
UPDATE #TEMPTB SET MODENAME=(SELECT MODEDESC FROM SB_MODE WHERE MODECODE=#TEMPTB.MODECODE)
UPDATE #TEMPTB SET DSTSTATEID=(SELECT STATE FROM SB_LOC WHERE LOCCODESB=#TEMPTB.DEST)
UPDATE #TEMPTB SET SRCSTATEID=(SELECT STATE FROM SB_LOC WHERE LOCCODESB=#TEMPTB.SRC)
UPDATE #TEMPTB SET SRCSTATE=(SELECT STATE FROM SB_STATE WHERE STATEID=#TEMPTB.SRCSTATEID)
UPDATE #TEMPTB SET DESTSTATE=(SELECT STATE FROM SB_STATE WHERE STATEID=#TEMPTB.DSTSTATEID)
UPDATE #TEMPTB SET SRCCO=(SELECT CONAME FROM SB_CO WHERE COCODE=(SELECT COCODE FROM SB_LOC WHERE LOCCODESB=#TEMPTB.SRC))
UPDATE #TEMPTB SET DSTCO='XXXX'

UPDATE #TEMPTB SET VATINDICATOR=(SELECT DESCRIPTION FROM SB_VATINDICATOR WHERE ENABLED='TRUE' AND ID=(SELECT VATINDICATOR FROM SB_TAXES WHERE STATE=#TEMPTB.DSTSTATEID AND PRODGRP=#TEMPTB.PRODGRP))
UPDATE #TEMPTB SET VATINDICATOR='CST' WHERE SRCSTATEID<>DSTSTATEID

--FRIEGHT RATE UPDATE FOR MODETYPE='R'
UPDATE #TEMPTB SET FREIGHTRATE=(SELECT RATE FROM SB_ROAD WHERE SRCLOC=#TEMPTB.SRC AND DSTLOC=#TEMPTB.DEST AND PROD=#TEMPTB.PRODGRP AND ENABLED='TRUE') WHERE MODECODE IN (SELECT MODECODE FROM SB_MODE WHERE MODETYPE='R' AND ENABLED='TRUE')

-- FREIGHT CALCULATIONS COMPLETE

alter table #temptb add LFAR decimal(10,2),RSP DECIMAL(10,2),FIXCOMM DECIMAL(10,2),VARCOMMF DECIMAL(10,2),QTVA decimal(10,2),DSP DECIMAL(10,2),ROTYPEID INT,ROTYPENAME VARCHAR(5)

UPDATE #TEMPTB SET ROTYPEID=(SELECT ROTYPE FROM SB_LOC WHERE LOCCODESB=#TEMPTB.DEST)
UPDATE #TEMPTB SET ROTYPENAME=(SELECT ROTYPENAME FROM SB_ROTYPE WHERE ROTYPEID=#TEMPTB.ROTYPEID)

UPDATE #TEMPTB SET LFAR=(SELECT LFAR FROM SB_ROWISECOMMISSION WHERE ROCODE=#TEMPTB.DEST AND MATERIALCODE=#TEMPTB.PRODGRP AND ENABLED='TRUE')
UPDATE #TEMPTB SET FIXCOMM=(SELECT FIXEDCOMMISSION FROM SB_ROWISECOMMISSION WHERE ROCODE=#TEMPTB.DEST AND MATERIALCODE=#TEMPTB.PRODGRP AND ENABLED='TRUE')
UPDATE #TEMPTB SET FIXCOMM=0 WHERE ROTYPENAME='COCO'
UPDATE #TEMPTB SET QTVA=(SELECT QRTLYTVA FROM SB_ROWISECOMMISSION WHERE ROCODE=#TEMPTB.DEST AND MATERIALCODE=#TEMPTB.PRODGRP AND ENABLED='TRUE')
UPDATE #TEMPTB SET QTVA=0 WHERE QTVA IS NULL
UPDATE #TEMPTB SET VARCOMMF=(SELECT VARCOMM FROM SB_ROWISECOMMISSION WHERE ROCODE=#TEMPTB.DEST AND MATERIALCODE=#TEMPTB.PRODGRP AND ENABLED='TRUE')
UPDATE #TEMPTB SET VARCOMMF=0 WHERE ROTYPENAME='COCO'
UPDATE #TEMPTB SET RSP=(SELECT PRICE FROM SB_PRICE WHERE LOC=#TEMPTB.DEST AND PRODUCT=#TEMPTB.PRODGRP AND PERIODID=@PERIOD AND PRICETYPE='RSP' AND ENABLED=1)

--UPDATE #TEMPTB SET RSP=0 WHERE RSP IS NULL

UPDATE #TEMPTB SET DSP=RSP-FIXCOMM+QTVA-TOTALFREIGHT WHERE RSP<>0 AND VATINDICATOR='N'
UPDATE #TEMPTB SET DSP=0 WHERE DSP IS NULL AND VATINDICATOR='N'

ALTER TABLE #TEMPTB ADD DCA_LFAR DECIMAL(10,2)

ALTER TABLE #TEMPTB ADD IRRTAXRATE DECIMAL(10,2), ED DECIMAL(10,2),PP DECIMAL(10,2),SALESTAXRATE DECIMAL(10,5),MINIMUMTAX DECIMAL(10,2),SPSALESTAX DECIMAL(10,2)

UPDATE #TEMPTB SET IRRTAXRATE=(SELECT IRRECOVERABLETAX/100 FROM SB_TAXES WHERE STATE=#TEMPTB.DSTSTATEID AND PRODGRP=#TEMPTB.PRODGRP AND IRRECOVERABLETAX IS NOT NULL)
UPDATE #TEMPTB SET ED=(SELECT (SPLADDLEXCISE+BASICCENVATFIXED+ROADINFRACESS)*1000 FROM SB_ED WHERE PRODGRP=#TEMPTB.PRODGRP)
UPDATE #TEMPTB SET PP=(SELECT PRICE FROM SB_PRICE WHERE LOC=#TEMPTB.SRC AND PRODUCT=#TEMPTB.PRODGRP AND ENABLED='TRUE' AND PERIODID=@PERIOD AND PRICETYPE='RTP')
UPDATE #TEMPTB SET SALESTAXRATE=(SELECT LSTRATE/100 FROM SB_TAXES WHERE STATE=#TEMPTB.DSTSTATEID AND PRODGRP=#TEMPTB.PRODGRP AND LSTRATE IS NOT NULL)
UPDATE #TEMPTB SET MINIMUMTAX=(SELECT MINIMUMLST FROM SB_TAXES WHERE STATE=#TEMPTB.DSTSTATEID AND PRODGRP=#TEMPTB.PRODGRP AND MINIMUMLST IS NOT NULL)
UPDATE #TEMPTB SET SPSALESTAX=(SELECT ADDLTAXONST FROM SB_TAXES WHERE STATE=#TEMPTB.DSTSTATEID AND PRODGRP=#TEMPTB.PRODGRP AND ADDLTAXONST IS NOT NULL)

SELECT DISTINCT(SRCSAPCODE),SOURCE,DSTSAPCODE,DESTINATION,MODENAME,MATERIAL,FREIGHTRATE FROM #TEMPTB where FREIGHTRATE IS NULL
SELECT DISTINCT(DSTSAPCODE),DESTINATION,MATERIAL,RSP FROM #TEMPTB WHERE RSP IS NULL
SELECT DISTINCT(SRCSAPCODE),SOURCE,MATERIAL,PP FROM #TEMPTB WHERE PP IS NULL
SELECT DISTINCT(DSTSAPCODE),DESTINATION,MATERIAL,FIXCOMM FROM #TEMPTB WHERE FIXCOMM IS NULL
SELECT DISTINCT(DSTSAPCODE),DESTINATION,MATERIAL,SALESTAXRATE FROM #TEMPTB WHERE SALESTAXRATE IS NULL

=====CODE END=====

yosiasz · September 30, 2022, 3:24pm

scary SP.

Doesnt matter whether it works elsewhere or not on some version and not on others. Seeing the code might expose something you totally are missing.

Good to have other eyes on it. Also we can test it on our sql server identical to yours

We cant guess and stumble in the dark going back and forth with questions and guesses without seeing the code

Ifor · October 1, 2022, 2:03pm

Try the following:

-- These environment settings are important
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE PROCEDURE [dbo].[CheckInputsBeforeCalcs]
	@PRODGRP INT = 0
	,@PERIOD INT = 3
	,@USERID INT = 18
AS
-- A very good reason is needed not to set XACT_ABORT
SET NOCOUNT, XACT_ABORT ON;

-- If creating a temp table always include the collation for strings
CREATE TABLE #Temptb
(
	SRC INT
	,SRCSAPCODE VARCHAR(10) COLLATE DATABASE_DEFAULT
	,[SOURCE] VARCHAR(50) COLLATE DATABASE_DEFAULT
	,DEST INT
	,DSTSAPCODE VARCHAR(10) COLLATE DATABASE_DEFAULT
	,DESTINATION VARCHAR(50) COLLATE DATABASE_DEFAULT
	,VATINDICATOR VARCHAR(10) COLLATE DATABASE_DEFAULT
	,PRODGRP INT
	,MATERIAL VARCHAR(10) COLLATE DATABASE_DEFAULT
	,MODECODE INT
	,MODENAME VARCHAR(15) COLLATE DATABASE_DEFAULT
	,DISTANCE FLOAT
	,FREIGHTRATE DECIMAL(10, 5)
	,FREIGHT DECIMAL(10, 2)
	,DSTSTATEID INT
	,SRCSTATEID INT
	,SERTAXONFREIGHT DECIMAL(10, 2)
	,TOTALFREIGHT DECIMAL(10, 2)
	,SRCSTATE VARCHAR(25) COLLATE DATABASE_DEFAULT
	,DESTSTATE VARCHAR(25) COLLATE DATABASE_DEFAULT
	,SRCCO VARCHAR(50) COLLATE DATABASE_DEFAULT
	,DSTCO VARCHAR(50) COLLATE DATABASE_DEFAULT
	--
	,LFAR DECIMAL(10, 2)
	,RSP DECIMAL(10, 2)
	,FIXCOMM DECIMAL(10, 2)
	,VARCOMMF DECIMAL(10, 2)
	,QTVA DECIMAL(10, 2)
	,DSP DECIMAL(10, 2)
	,ROTYPEID INT
	,ROTYPENAME VARCHAR(5) COLLATE DATABASE_DEFAULT
	,DCA_LFAR DECIMAL(10, 2)
	,IRRTAXRATE DECIMAL(10, 2)
	,ED DECIMAL(10, 2)
	,PP DECIMAL(10, 2)
	,SALESTAXRATE DECIMAL(10, 5)
	,MINIMUMTAX DECIMAL(10, 2)
	,SPSALESTAX DECIMAL(10, 2)
);

INSERT INTO #TEMPTB
(
	SRC, DEST, PRODGRP, MODECODE, DISTANCE, DSTCO
	,FREIGHTRATE, FREIGHT,  DSTSTATEID, SRCSTATEID, SERTAXONFREIGHT, TOTALFREIGHT
	,SRCSAPCODE, [SOURCE], SRCSTATEID
	,DSTSAPCODE, DESTINATION, DSTSTATEID
	,SRCCO, MATERIAL, MODENAME, SRCSTATE, DESTSTATE
	,ROTYPEID
)
SELECT LI.[SOURCE], LI.DESTINATION, LI.PRODGRP, LI.MODE, LI.DISTANCEOVERRIDE, 'XXXX'
	,0, 0, 0, 0, 0, 0
	,SL.LOCCODESAP, SL.[LOCATION], SL.[STATE]
	,SD.LOCCODESAP, SD.[LOCATION], SD.[STATE]
	,C.CONAME, P.PRODGRADE, M.MODEDESC, SS.[STATE], DS.[STATE]
	,SD.ROTYPE
FROM SB_LINKAGE LI
	LEFT JOIN SB_LOC SL
		ON LI.[SOURCE] = SL.LOCCODESB
	LEFT JOIN SB_LOC SD
		ON LI.DEST = SD.LOCCODESB
	LEFT JOIN SB_CO C
		ON SL.COCODE = C.COCODE
	LEFT JOIN SB_PROD P
		ON LI.PRODGRP = P.PRODGRADESBCODE
	LEFT JOIN SB_MODE M
		ON LI.MODE = M.MODECODE
	LEFT JOIN SB_STATE SS
		ON LI.SRCSTATEID = SS.STATEID
	LEFT JOIN SB_STATE DS
		ON LI.DSTSTATEID = DS.STATEID
WHERE LI.PRODGRP = CASE WHEN @PRODGRP <> 0 THEN @PRODGRP ELSE LI.PRODGRP END
	AND LI.[ENABLED] = 'TRUE';

UPDATE T
SET VATINDICATOR = V.[DESCRIPTION]
FROM #Temptb T
	JOIN SB_TAXES ST
		ON T.PRODGRP = ST.PRODGRP
	JOIN SB_VATINDICATOR V
		ON ST.VATINDICATOR = V.ID
			AND V.[ENABLED] = 'TRUE';

UPDATE #TEMPTB
SET VATINDICATOR = 'CST'
WHERE SRCSTATEID <> DSTSTATEID;

--FRIEGHT RATE UPDATE FOR MODETYPE='R'
UPDATE T
SET FREIGHTRATE = R.RATE
FROM #Temptb T
	JOIN SB_ROAD R
		ON T.SRC = R.SRCLOC
			AND T.DEST = R.DSTLOC
			AND T.PRODGRP = R.PROD
			AND R.[ENABLED] = 'TRUE'
	JOIN SB_MODE M
		ON T.MODECODE = M.MODECODE
			AND M.MODETYPE = 'R'
			AND M.[ENABLED] = 'TRUE';

UPDATE #TEMPTB
SET ROTYPENAME = (
		SELECT ROTYPENAME
		FROM SB_ROTYPE
		WHERE ROTYPEID = #TEMPTB.ROTYPEID
		)

UPDATE #TEMPTB
SET LFAR = (
		SELECT LFAR
		FROM SB_ROWISECOMMISSION
		WHERE ROCODE = #TEMPTB.DEST
			AND MATERIALCODE = #TEMPTB.PRODGRP
			AND [ENABLED] = 'TRUE'
		)

UPDATE #TEMPTB
SET FIXCOMM = (
		SELECT FIXEDCOMMISSION
		FROM SB_ROWISECOMMISSION
		WHERE ROCODE = #TEMPTB.DEST
			AND MATERIALCODE = #TEMPTB.PRODGRP
			AND [ENABLED] = 'TRUE'
		)

UPDATE #TEMPTB
SET FIXCOMM = 0
WHERE ROTYPENAME = 'COCO'

UPDATE #TEMPTB
SET QTVA = (
		SELECT QRTLYTVA
		FROM SB_ROWISECOMMISSION
		WHERE ROCODE = #TEMPTB.DEST
			AND MATERIALCODE = #TEMPTB.PRODGRP
			AND [ENABLED] = 'TRUE'
		)

UPDATE #TEMPTB
SET QTVA = 0
WHERE QTVA IS NULL

UPDATE #TEMPTB
SET VARCOMMF = (
		SELECT VARCOMM
		FROM SB_ROWISECOMMISSION
		WHERE ROCODE = #TEMPTB.DEST
			AND MATERIALCODE = #TEMPTB.PRODGRP
			AND [ENABLED] = 'TRUE'
		)

UPDATE #TEMPTB
SET VARCOMMF = 0
WHERE ROTYPENAME = 'COCO'

UPDATE #TEMPTB
SET RSP = (
		SELECT PRICE
		FROM SB_PRICE
		WHERE LOC = #TEMPTB.DEST
			AND PRODUCT = #TEMPTB.PRODGRP
			AND PERIODID = @PERIOD
			AND PRICETYPE = 'RSP'
			AND [ENABLED] = 1
		)

--UPDATE #TEMPTB SET RSP=0 WHERE RSP IS NULL
UPDATE #TEMPTB
SET DSP = RSP - FIXCOMM + QTVA - TOTALFREIGHT
WHERE RSP <> 0
	AND VATINDICATOR = 'N'

UPDATE #TEMPTB
SET DSP = 0
WHERE DSP IS NULL
	AND VATINDICATOR = 'N'

UPDATE #TEMPTB
SET IRRTAXRATE = (
		SELECT IRRECOVERABLETAX / 100
		FROM SB_TAXES
		WHERE [STATE] = #TEMPTB.DSTSTATEID
			AND PRODGRP = #TEMPTB.PRODGRP
			AND IRRECOVERABLETAX IS NOT NULL
		)

UPDATE #TEMPTB
SET ED = (
		SELECT (SPLADDLEXCISE + BASICCENVATFIXED + ROADINFRACESS) * 1000
		FROM SB_ED
		WHERE PRODGRP = #TEMPTB.PRODGRP
		)

UPDATE #TEMPTB
SET PP = (
		SELECT PRICE
		FROM SB_PRICE
		WHERE LOC = #TEMPTB.SRC
			AND PRODUCT = #TEMPTB.PRODGRP
			AND [ENABLED] = 'TRUE'
			AND PERIODID = @PERIOD
			AND PRICETYPE = 'RTP'
		)

UPDATE #TEMPTB
SET SALESTAXRATE = (
		SELECT LSTRATE / 100
		FROM SB_TAXES
		WHERE [STATE] = #TEMPTB.DSTSTATEID
			AND PRODGRP = #TEMPTB.PRODGRP
			AND LSTRATE IS NOT NULL
		)

UPDATE #TEMPTB
SET MINIMUMTAX = (
		SELECT MINIMUMLST
		FROM SB_TAXES
		WHERE [STATE] = #TEMPTB.DSTSTATEID
			AND PRODGRP = #TEMPTB.PRODGRP
			AND MINIMUMLST IS NOT NULL
		)

UPDATE #TEMPTB
SET SPSALESTAX = (
		SELECT ADDLTAXONST
		FROM SB_TAXES
		WHERE [STATE] = #TEMPTB.DSTSTATEID
			AND PRODGRP = #TEMPTB.PRODGRP
			AND ADDLTAXONST IS NOT NULL
		)

SELECT DISTINCT SRCSAPCODE
	,[SOURCE]
	,DSTSAPCODE
	,DESTINATION
	,MODENAME
	,MATERIAL
	,FREIGHTRATE
FROM #TEMPTB
WHERE FREIGHTRATE IS NULL

SELECT DISTINCT DSTSAPCODE
	,DESTINATION
	,MATERIAL
	,RSP
FROM #TEMPTB
WHERE RSP IS NULL

SELECT DISTINCT SRCSAPCODE
	,[SOURCE]
	,MATERIAL
	,PP
FROM #TEMPTB
WHERE PP IS NULL

SELECT DISTINCT DSTSAPCODE
	,DESTINATION
	,MATERIAL
	,FIXCOMM
FROM #TEMPTB
WHERE FIXCOMM IS NULL

SELECT DISTINCT DSTSAPCODE
	,DESTINATION
	,MATERIAL
	,SALESTAXRATE
FROM #TEMPTB
WHERE SALESTAXRATE IS NULL

GO

Angelacce · October 3, 2022, 12:02am

Is it helped to manually run each statement?

nldeshpande · October 17, 2022, 11:40am

Hello Friends,

Microsoft SQL Server 2016 (SP2-CU16) (KB5000645) - 13.0.5882.1 (X64) Jan 25 2021 21:40:32 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

was rolled back to

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)

and lo behold, all Stored Procedures started functioning effectively and efficiently without requiring to check or change the code.

I would appreciate if you review and revise your opinion and replies.

Hope this update helps someone who is facing a similar issue.

Thanks a lot friends.