Temp Tables Used within Procedure giving error in one 2019 sql server instance

Question

Post reply

Temp Tables Used within Procedure giving error in one 2019 sql server instance

sachinleo78

SSC Enthusiast

Points: 195
More actions
December 27, 2023 at 4:45 pm

#4353162

I have Three stored Procedures A,B,C C is a common procedure which derives data into a common table depending on parameters from respective procedures A or B and returns to a or b for further processing on one sql 2019 environment its working normally but on another if Proc A is executed first then later if procedure B is executed procedure b would return error after lot of debugging finally i tried calling proc C from A or B with recompile option then its working fine need a solution other than recompile any help appreciated. Please guide if any Sql Server settings can resolve as it works on sql 2019 server but on two different environments but failing on one.
Procedure C basically is a collection of dynamic sql which returns table required with data according to the caller, like If A calls C with Parameter ColumnName Distance C will process and return a table with Say DynamicMaster with column Distance added along with respective Data,and if B calls C with Parameter cloumn Time, C will process Data and Return a common table only with Column Time Instead Of Distance which is called from A but whats happening is if A is called earlier it returns DynamicMaster Table with Column Distance ,but after that if B procedure is executed expecting DynamicMaster Table with Column Time it gives error Column Time does not exist the DynamicMaster is created in Proc C and data collated in C itself this is working fine with one environment but failing in another environment of sql 2019

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What is the text of the error you are receiving?

Is the data exactly the same across the different environments?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sachinleo78 SSC Enthusiast Points: 195 More actions · Answer 2

while i execute the Procedure A which internally calls procedure C in which a dynamic table called DynamicMaster is generated with a dynamic column added to it Called Distance the procedure returns desired result,but if i execute proc b which allso calls proc C internally but this time dynamically another column called Groundtime is added inplace of Distance but the procedure gives an error called Invalid Column GroundTime but incase i restart the sql agent and first exec Procedure B it returns expected data and later if i execute proc A now it gives error Invalid column Distance looks like temp db cache is not flushing or something

sachinleo78 SSC Enthusiast Points: 195 More actions · Answer 3

sachinleo78

SSC Enthusiast

Points: 195

December 27, 2023 at 5:42 pm

#4353176

ya same data on both environments

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

To repeat the question that Phil asked but in different words....

You say something would "return error". Please copy and post that error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

>> will process and return a table with Say DynamicMaster with column Distance added along with respective Data <<

How, specifically, does it return a table? Does it create a temp table and return that?

If so, as you said, temp table caching/reuse in stored procs might cause problems for you. You might want to consider generating a unique temp table name for each caller if you can safely do that. You could add a parameter that would become a suffix on the created temp table name, for example.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sachinleo78 SSC Enthusiast Points: 195 More actions · Answer 6

Yes the Dynamic Table is created and processed with required data and returned

sachinleo78 SSC Enthusiast Points: 195 More actions · Answer 7

This issue happening in prod environment ,but not in staging and local development environment, is there any setting for clearing tempdb cache of sql server

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

sachinleo78 wrote:

This issue happening in prod environment ,but not in staging and local development environment, is there any setting for clearing tempdb cache of sql server

Possibly because the prod environment is busier than staging/local.

Scott's suggestion should alleviate, if not completely fix, the problem.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

Are you creating a temp table i.e. one that begins with a hash (#) or are you using a permanent table name?

sachinleo78 SSC Enthusiast Points: 195 More actions · Answer 10

sachinleo78

SSC Enthusiast

Points: 195

December 28, 2023 at 9:44 am

#4353499

Creating a Temptable called #DynamicMaster

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 11

Instead of applying the recompile at the procedure level, you can apply it at the query level within the stored procedure using the OPTION (RECOMPILE) hint. This way, only specific queries within the procedure will be recompiled, which might be more targeted than recompiling the entire procedure.