SQL caching

[mrmookster]

I have been tearing my hair out over the last few hours - can anybody help.

I have a fairly comlicated query which aggregates data from a UNION ALL into a temporary table then returns the result. It was working perfectly but I changed a very minor part and it stopped returning all the rows I expected.

I went back to my original cut and paste into the SP and it still didn't return all the rows I expected. Now the wierd thing ... if I ran the query, the exact same query in QAnalyzer it evaluated as expected BUT the SP returned a different set of results.

I can only think of some sort of caching issue but my DBA skills aren't that hot anyone got any ideas?

 

[SQLDenis]

add with recompile to the proc and see if this helps you

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[mrdenny]

You can also use sp_recompile to mark your procedure as needing to be recompiled.

You can use DBCC DROPCLEANBUFFERS to removes all buffers from the pool.

You can use DBCC FREEPROCCACHE to clear the procedure cache.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com