Drop or dealloacte??

[Delphin]

I have a stored procedure which I have several temp tables declared.

Declare @temptbl table ()

Once i am done using these I have the results input into another temp table. How do I drop the previous temp tables?

I keep getting a some results have been dropped because I am low on resourses.

Billy Ballard

For in the past, Lies the future.

 

[mrdenny]

The message that some results have been dropped is a client message not a server message. It means that your Query Analyser can't handle the number of recordsets that are being returned to it.

It doesn't have anything to do with the declare @table statements.

Return less recordsets to Query Analyzer or run the command in text mode. Displaying the grid takes tons more resources than displaying the same data in text format.

Denny

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

http://www.mrdenny.com

(My very old site)

 

[Delphin]

So as these tables get into the 10 of thousands of rows and not just the 2500 curently there this will not run the system low on resources with multiple requests?

Billy
For in the past, Lies the future.

 

[mrdenny]

Nope, you should be fine. I'd truncate them if you don't need the data within the variable. That will free up some memory until the procedure ends. Once the procedure ends the variable is destroyed.

Denny

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

http://www.mrdenny.com

(My very old site)

 

[RobertT687]

One other thing I usually do is to include the directive SET NOCOUNT ON at the beginning of the procedure. This prevents the rows effected count from being passed back to the calling program along with the actual results. If you are doing a lot of processing in the procedure that would return a lot of result sets.