March 2, 2021 at 8:31 pm
I'm having an issue with a database that I recently moved from SQL 2014 to SQL 2019 (Standard in both cases). The users get data from the database using stored procedures either through the UI or linked to Excel (using an ODBC). The users will execute the same procedure multiple times with different parameters expecting to get different result sets to support daily processes. However, they're not getting the result set they expect.
As an example - we'll call the stored procedure PricingReport.
EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '12345';
EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '56789';
If the stored procedure gets executed as above, sometimes the user will get the result set for PricePoint = 12345 twice. If the two procedure calls get executed again immediately after, they might return the results for PricePoint = 56789 twice.
Under the hood, the stored procedure uses the parameters passed to generate dynamic SQL to populate a temp table and then returns the results in the temp table to the user.
I was able to replicate the behavior in SSMS, but I'm having trouble finding the right search phrase to figure what setting I need to change to fix this issue. I've simplified the example code a bit, but I hope it's enough for someone to at least point me in the right direction.
March 2, 2021 at 8:56 pm
Make sure the proc is dropping or truncating the temp table every time at the start of the proc.
It sounds as if rows from the prior run are being left in the temp table after the proc exists ... which should not be possible, but it still sounds like that. Would you by any chance be using a global temp table named ##table rather than #table?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 2, 2021 at 8:59 pm
I'm looking at the details of the stored procedure now. From what I'm seeing, all temp tables created within the stored procedure are dropped at the end.
March 2, 2021 at 9:09 pm
Last few statements in the stored procedure are:
--export results
SELECT * FROM #ReportResults ORDER BY dateStamp
--clean up temp tables
DROP TABLE #ReportResults
If I add option (recompile) to the select, I can get the proper result set executing the procedures back to back. Since the #ReportResults temp table is created dynamically for the procedure, I don't know the names of the columns in the table. That's determined by the parameters the user passed in.
Is that my best shot for a resolution of the issue? If it came down to it, I could generate the column list for the select. It's being done elsewhere in the procedure.
March 2, 2021 at 9:20 pm
Is it possible to exit the proc without falling thru to the DROP TABLE?
What about the start of the proc?
If the temp table already exists, does the proc scratch (drop or truncate) it?
If the temp table really is dropped every time, your issue is something else. Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 2, 2021 at 11:13 pm
Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?
If the temp table really is dropped every time, your issue is something else. Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.
I don't think it matters if you do not drop a temporary table (named with only one #), that has been created inside the stored procedure, at the end of a stored procedure. They are automatically dropped on exiting the procedure.
March 2, 2021 at 11:16 pm
I'm having an issue with a database that I recently moved from SQL 2014 to SQL 2019 (Standard in both cases). The users get data from the database using stored procedures either through the UI or linked to Excel (using an ODBC). The users will execute the same procedure multiple times with different parameters expecting to get different result sets to support daily processes. However, they're not getting the result set they expect.
As an example - we'll call the stored procedure PricingReport.
EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '12345';
EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '56789';If the stored procedure gets executed as above, sometimes the user will get the result set for PricePoint = 12345 twice. If the two procedure calls get executed again immediately after, they might return the results for PricePoint = 56789 twice.
Under the hood, the stored procedure uses the parameters passed to generate dynamic SQL to populate a temp table and then returns the results in the temp table to the user.
I was able to replicate the behavior in SSMS, but I'm having trouble finding the right search phrase to figure what setting I need to change to fix this issue. I've simplified the example code a bit, but I hope it's enough for someone to at least point me in the right direction.
Is it possible for you to provide an overview of exactly how the table is created and populated, can you supply the code even with out the full SQL so we can see the structure of it as it would be a help.
March 3, 2021 at 2:14 am
ScottPletcher wrote:Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?
If the temp table really is dropped every time, your issue is something else. Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.
I don't think it matters if you do not drop a temporary table (named with only one #), that has been created inside the stored procedure, at the end of a stored procedure. They are automatically dropped on exiting the procedure.
THAT is why I asked if the table was always created in the proc.
I have some procs where I can deliberately pass in the temp table table myself; thus, the proc only creates the table and loads it if it doesn't already exist. I'm sure that's not a common thing, but it is possible. I just wanted to be absolutely certain before making a definitive statement.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 3, 2021 at 5:25 am
Did you change the Compatibility Level when upgrading from SQL 2014? (e.g. from 120 to 150)
If so maybe try it back at 120 just as a test for this query?
All Statistics refreshed after the upgrade?
March 3, 2021 at 12:48 pm
Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?
If the temp table really is dropped every time, your issue is something else. Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.
Apparently, I never submitted the post I wrote yesterday...
There is a little bit of error trapping in the procedure. At each of the error traps, the temporary tables are dropped before exiting the procedure. Given that myself and my coworkers are all application developers that had to pick up database development, I can't dismiss the possibility that you could exit the procedure without dropping the temp tables.
There is no check for the existence of the temp table before the create table statement.
March 3, 2021 at 12:50 pm
Jonathan AC Roberts wrote:ScottPletcher wrote:Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does the proc scratch (drop or truncate) it?
If the temp table really is dropped every time, your issue is something else. Somewhere / somehow the app is retaining the prior rows/rowset and sending them again with the next execution.
I don't think it matters if you do not drop a temporary table (named with only one #), that has been created inside the stored procedure, at the end of a stored procedure. They are automatically dropped on exiting the procedure.
THAT is why I asked if the table was always created in the proc.
I have some procs where I can deliberately pass in the temp table table myself; thus, the proc only creates the table and loads it if it doesn't already exist. I'm sure that's not a common thing, but it is possible. I just wanted to be absolutely certain before making a definitive statement.
We've started using that technique here in the last few years. It might be used elsewhere in this database, but not in this procedure. The most common way we accomplish that is by including a flag in the procedure parameters.
March 3, 2021 at 12:54 pm
Did you change the Compatibility Level when upgrading from SQL 2014? (e.g. from 120 to 150)
If so maybe try it back at 120 just as a test for this query?
All Statistics refreshed after the upgrade?
The compatibility level was set to 150 as part of the migration. I did try changing it back to 120 when I was testing things yesterday, but that didn't seem to fix the issue. I also played with the settings for parameters sniffing and the cardinality estimator (grasping at anything I can).
No, the statistics were not refreshed after the upgrade. I started working on that yesterday, but haven't made it through all the tables in the database yet.
March 3, 2021 at 1:19 pm
LightVader wrote:I'm having an issue with a database that I recently moved from SQL 2014 to SQL 2019 (Standard in both cases). The users get data from the database using stored procedures either through the UI or linked to Excel (using an ODBC). The users will execute the same procedure multiple times with different parameters expecting to get different result sets to support daily processes. However, they're not getting the result set they expect.
As an example - we'll call the stored procedure PricingReport.
EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '12345';
EXEC PricingReport @startDate = '2021/02/01', @endDate = '2021/02/28', @pricePoint = '56789';If the stored procedure gets executed as above, sometimes the user will get the result set for PricePoint = 12345 twice. If the two procedure calls get executed again immediately after, they might return the results for PricePoint = 56789 twice.
Under the hood, the stored procedure uses the parameters passed to generate dynamic SQL to populate a temp table and then returns the results in the temp table to the user.
I was able to replicate the behavior in SSMS, but I'm having trouble finding the right search phrase to figure what setting I need to change to fix this issue. I've simplified the example code a bit, but I hope it's enough for someone to at least point me in the right direction.
Is it possible for you to provide an overview of exactly how the table is created and populated, can you supply the code even with out the full SQL so we can see the structure of it as it would be a help.
Here's the majority of the code from the procedure. I've tried to add comments where I've removed business logic. There's two temp tables in this code #Nodes2Process and #ReportResults.
The #Nodes2Process temp table is created with the same columns every time. That table seems fine. The #ReportResults temp table has the same first two columns every time but any columns after that would change depending on the @pricePoint and @priceComponent parameters. Since the columns and data are all added through dynamic SQL, I had add a few print statements yesterday to try to diagnose the issue. All the dynamic SQL matched what I was expecting, but the results from the final select * from #ReportResults had different columns then what was in the dynamic SQL.
CREATE PROCEDURE PricingReport (
@startDate datetime, @endDate datetime,
@pricePoint varchar(1000), --Pipe delimited list
@priceComponent varchar(1000) --Pipe delimited list
) AS
--Variable Declaration
DECLARE @nullNodeID int, @nullFigID int, @distDataInt int, @currNodeID numeric, @currFigID numeric, @nodeCnt int, @hasNode int,
@currTblName varchar(150), @sql2Run nvarchar(4000), @currDataTable varchar(175),
@priMinDate datetime, @priMaxDate datetime, @currDate datetime, @myEndDate datetime,
@dataInterval varchar(10), @currInterval int, @intPerHr int, @intDuration int, @intCnt int, @currColName varchar(256), @hrCnt int,
@errMsg varchar(255), @nonNumCnt int, @isNonNum int, @fvDataType varchar(16), @fvLength int,
@dataDefID numeric, @userHasNodeAccess bit, @colList nvarchar(4000)
--Create a temp table with the nodes & figures to execute
SELECT n.itemNum, n.splitdata as nodeID, f.splitdata as figureID
INTO #Nodes2Process
FROM fnSplitString(@pricePoint, '|') n FULL OUTER JOIN
fnSplitString(@pricePoint, '|') f ON n.itemNum = f.itemNum
--ensure that none of the nodeIDs or figureIDs are in the temp table are not null
--if null, raise error
SELECT @nullNodeID = COUNT(*) FROM #Nodes2Process WHERE nodeID IS NULL
SELECT @nullFigID = COUNT(*) FROM #Nodes2Process WHERE figureID IS NULL
IF @nullNodeID > 0
BEGIN
RAISERROR ('Invalid Number of Node IDs Provided. The number of node IDs must match the number of figure IDs. Report processing halted.', 11, 0, 'x')
DROP TABLE #Nodes2Process
RETURN
END
IF @nullFigID > 0
BEGIN
RAISERROR ('Invalid Number of Figure IDs Provided. The number of figure IDs must match the number of node IDs. Report processing halted.', 11, 0, 'x')
DROP TABLE #Nodes2Process
RETURN
END
/*
Removed some business logic and reference to specific database tables
*/
--Add additional columns to the #Nodes2Process temp table
ALTER TABLE #Nodes2Process ADD dataDefID numeric
ALTER TABLE #Nodes2Process ADD dataDefName varchar(150)
ALTER TABLE #Nodes2Process ADD dataInterval varchar(10)
ALTER TABLE #Nodes2Process ADD tableName varchar(150)
ALTER TABLE #Nodes2Process ADD nodeName varchar(255)
ALTER TABLE #Nodes2Process ADD figureName varchar(75)
ALTER TABLE #Nodes2Process ADD fvDataType varchar(16)
ALTER TABLE #Nodes2Process ADD fvLength int
ALTER TABLE #Nodes2Process ADD isNonNum int
ALTER TABLE #Nodes2Process ADD archTblExists int
ALTER TABLE #Nodes2Process ADD priMinDate datetime
ALTER TABLE #Nodes2Process ADD priMaxDate datetime
ALTER TABLE #Nodes2Process ADD archMinDate datetime
ALTER TABLE #Nodes2Process ADD userHasNodeAccess bit
/*
Removed some business logic and reference to specific database tables to update the columns created above
*/
--get additional archive and date information about the nodes
SET @nodeCnt = 1
SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt
WHILE @hasNode > 0
BEGIN
--get information from table
SELECT @currNodeID = nodeID, @currTblName = tableName, @isNonNum = isNonNum, @dataDefID = dataDefID
FROM #Nodes2Process
WHERE itemNum = @nodeCnt
--create table name
IF @isNonNum = 1
BEGIN
SET @currDataTable = @currTblName + 'FinalStr_' + CAST(@currNodeID AS VARCHAR)
END
ELSE
BEGIN
SET @currDataTable = @currTblName + 'FinalData_' + CAST(@currNodeID AS VARCHAR)
END
--verify that the user has access to the table based on information in the security tables
SET @userHasNodeAccess = VerifyUserAccessToNode(@currNodeID)
--get the date and archive values. if no node access, set everything to null
IF @userHasNodeAccess = 0
BEGIN
SET @priMinDate = null
SET @priMaxDate = null
SET @archMinDate = null
SET @archTblExists = 0
SET @archMinDate = null
END
ELSE
BEGIN
--get primary and archive min dates & max primary date
SET @sql2Run = 'SELECT @priMinDate = MIN(dateStamp) FROM ' + @currDataTable
EXECUTE sp_executesql @sql2Run, N'@priMinDate datetime OUTPUT', @priMinDate = @priMinDate OUTPUT
SET @sql2Run = 'SELECT @priMaxDate = MAX(dateStamp) FROM ' + @currDataTable
EXECUTE sp_executesql @sql2Run, N'@priMaxDate datetime OUTPUT', @priMaxDate = @priMaxDate OUTPUT
END
--assign values to nodes to process table
UPDATE #Nodes2Process
SET priMinDate = @priMinDate, priMaxDate = @priMaxDate
WHERE nodeID = @currNodeID
--iterate to the next counter
SET @nodeCnt = @nodeCnt + 1
SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt
END
--create dates
SELECT TOP 1 @dataInterval = dataInterval FROM #Nodes2Process
SET @currDate = CASE
WHEN @dataInterval IN ('5Min', '10Min', '15Min','20Min', '30Min', 'hour', 'day') THEN Cast(Cast(Month(@startDate) as varchar)+ '/' + Cast(Day(@startDate) as varchar)+ '/' + Cast(Year(@startDate) as varchar) as datetime)
WHEN @dataInterval = 'month' THEN Cast(Cast(Month(@startDate) as varchar)+ '/01/' + Cast(Year(@startDate) as varchar) as datetime)
WHEN @dataInterval = 'quarter' THEN Cast(Cast(((DatePart(qq, @startDate)-1) * 2) + DatePart(qq, @startDate) as varchar)+ '/01/' + Cast(Year(@startDate) as varchar) as datetime)
WHEN @dataInterval = 'year' THEN Cast('01/01/' + Cast(Year(@startDate) as varchar) as datetime)
END
SET @myEndDate = CASE
WHEN @dataInterval IN ('5Min', '10Min', '15Min','20Min', '30Min', 'hour', 'day') THEN DateAdd(dd,1,Cast(Cast(Month(@endDate) as varchar)+ '/' + Cast(Day(@endDate) as varchar)+ '/' + Cast(Year(@endDate) as varchar) as datetime))
WHEN @dataInterval = 'month' THEN DateAdd(mm,1,Cast(Cast(Month(@endDate) as varchar)+ '/01/' + Cast(Year(@endDate) as varchar) as datetime))
WHEN @dataInterval = 'quarter' THEN DateAdd(qq,1,Cast(Cast(((DatePart(qq, @endDate)-1) * 2) + DatePart(qq, @endDate) as varchar)+ '/01/' + Cast(Year(@endDate) as varchar) as datetime))
WHEN @dataInterval = 'year' THEN DateAdd(yy,1,Cast('01/01/' + Cast(Year(@endDate) as varchar) as datetime))
END
--get interval information
SELECT @intPerHr = intervalsPerHr, @intDuration = intervalDuration, @intCnt = intervalCnt FROM intervals WHERE dataInterval = @dataInterval
/*********
This is the table that seems to be causing my problem
*********/
--create initial temp table for report
CREATE TABLE #ReportResults (dateStamp dateTime NOT NULL, dataInterval int)
--populate temp table with dates based on interval rollup
WHILE @currDate < @myEndDate
BEGIN
--create the data interval
SET @currInterval = ((DatePart(hh, @currDate) * @intPerHr) + (DatePart(mi, @currDate) / @intDuration)) + 1
--insert the row
INSERT INTO #ReportResults VALUES
(Cast(Month(@currDate) as varchar)+ '/' + Cast(Day(@currDate) as varchar)+ '/' + Cast(Year(@currDate) as varchar), @currInterval)
--increment the currDate
SET @currDate = CASE @dataInterval
WHEN '5Min' THEN DateAdd(mi, 5, @currDate)
WHEN '10Min' THEN DateAdd(mi, 10, @currDate)
WHEN '15Min' THEN DateAdd(mi, 15, @currDate)
WHEN '20Min' THEN DateAdd(mi, 20, @currDate)
WHEN '30Min' THEN DateAdd(mi, 30, @currDate)
WHEN 'hour' THEN DateAdd(hh, 1, @currDate)
WHEN 'day' THEN DateAdd(dd, 1, @currDate)
WHEN 'month' THEN DateAdd(mm, 1, @currDate)
WHEN 'quarter' THEN DateAdd(qq, 1, @currDate)
WHEN 'year' THEN DateAdd(yy, 1, @currDate)
END
END
--iterate through the node table and add results to final output
SET @nodeCnt = 1
SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt
SET @colList = '';
WHILE @hasNode > 0
BEGIN
--get node-figure pair information
SELECT @currNodeID = nodeID, @currFigID = figureID, @currTblName = tableName, @archTblExists = archTblExists,
@priMinDate = priMinDate, @isNonNum = isNonNum, @fvDataType = fvDataType, @fvLength = fvLength,
@userHasNodeAccess = userHasNodeAccess
FROM #Nodes2Process
WHERE itemNum = @nodeCnt
IF @isNonNum = 1
BEGIN
SET @currDataTable = @currTblName + 'FinalStr_' + CAST(@currNodeID AS VARCHAR)
END
ELSE
BEGIN
SET @currDataTable = @currTblName + 'FinalData_' + CAST(@currNodeID AS VARCHAR)
END
--create the column
SET @currColName = ReportColumnName(@currNodeID, @currFigID, null) --Scaler function to create column name for report data
SET @colList = @colList + ', [' + @currColName + ']';
SET @sql2Run = 'ALTER TABLE #ReportResults ADD [' + @currColName + '] ' + @fvDataType
IF @fvLength IS NOT NULL
BEGIN
SET @sql2Run = @sql2Run + ' (' + CAST(@fvLength AS VARCHAR) + ')'
END
EXECUTE sp_executesql @sql2Run
--populate the column with result data if user has access to the table
IF @userHasNodeAccess = 1
BEGIN
SET @sql2Run = addCurrRptDataQry(@rollupType, '#ReportResults', @intCnt, @currFigID, @currNodeID, @calcCode, @currDataTable, @startDate, @endDate, '', '', @useArch) --Scaler function to create SQL statement that would populate the #ReprotResults table with data
PRINT @sql2Run; --Part of my testing yesterday, the expected SQL Statement is returned
EXECUTE sp_executesql @sql2Run
END
--iterate to the next node
SET @nodeCnt = @nodeCnt + 1
SELECT @hasNode = COUNT(nodeID) FROM #Nodes2Process WHERE itemNum = @nodeCnt
END -- end hasNode while
-- Select the results from the temp table. Adding option (recompile) here seems to resolve the issue
SELECT * FROM #ReportResults ORDER BY dateStamp, dataInterval
--clean up temp tables
DROP TABLE #Nodes2Process
DROP TABLE #ReportResults
March 3, 2021 at 1:44 pm
I can't see how presence of existing TEMP table would cause a problem as
CREATE TABLE #ReportResults (dateStamp dateTime NOT NULL, dataInterval int)
would then fail.
longshot : But ... maybe it is failing, raising an error but somehow?? processing is able to continue.
Belt and Braces only, but might be worth checking (at top of the Sproc) that TEMP TABLE does not exist
IF OBJECT_ID('tempdb..#ReportResults') IS NOT NULL
BEGIN
RAISERROR ('#ReportResults already exsists.', 11, 0, 'x')
RETURN
ENDPerhaps also add
SET XACT_ABORT ON
Doesn't feel like either of those are related to the cause though.
March 3, 2021 at 3:22 pm
I can't see how presence of existing TEMP table would cause a problem as
CREATE TABLE #ReportResults (dateStamp dateTime NOT NULL, dataInterval int)
would then fail.
longshot : But ... maybe it is failing, raising an error but somehow?? processing is able to continue.
Belt and Braces only, but might be worth checking (at top of the Sproc) that TEMP TABLE does not exist
IF OBJECT_ID('tempdb..#ReportResults') IS NOT NULL
BEGIN
RAISERROR ('#ReportResults already exsists.', 11, 0, 'x')
RETURN
ENDPerhaps also add
SET XACT_ABORT ON
Doesn't feel like either of those are related to the cause though.
I added the check to see if the temp table exists, but that didn't change the behavior I'm seeing.
I wasn't sure if the SET XACT_ABORT ON should be added before I run the EXEC statement or within the stored procedure. I put it in the stored procedure and that didn't change anything either.
So far I've only come across 2 things that got me to the behavior I expect:
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply