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.
Do you mind sharing more specific details, such as what action is happening in proc C that causes the error, and what the error message is?
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
another observation if sql server is restarted and Procedure B is executed first and then try to execute Procedure A now Procedure B is working fine and procedure A returns error saying Distance Column not exist
Also the Table DynamicMaster is a temporary Table #DynamicMaster
You might be having temp table cache contention issues.
Ok thanks for the input will go through it .
It's not necessarily cache contention you're running into, I think, but cache... collision? I'm not sure what to call it, but it seems like on repeated executions, SQL Server is using the temp table cache when it shouldn't be.
- Do not explicitly drop temp tables at the end of a stored procedure, they will get cleaned up when the session that created them ends.
- Do not alter temp tables after they have been created.
- Do not truncate temp tables
- Move index creation statements on temp tables to the new inline index creation syntax that was introduced in SQL Server 2014.
is there any settings for this cache contention or collision because same scenario works on different sql 2019 environments
I don't know, but you might consider searching for scripts that identify differences in configurations between instances/databases.
Perhaps you could compare output from Get-DbaSpConfigure in dbatools.