SQLTeam.com | Weblogs | Forums

EXEC inside of a WHILE LOOP

#1

I am using a EXEC inside of a WHILE LOOP. It works fine until looping up to 10x. After that, I was able to track down that it stalls at EXEC. Is this a buffer issue and how would I clear it?

Thanks

#2

You would have to post the code. There are many reason why it might "stall".

#3

Hi, here is a simplified version of the code. It is stalling on EXEC statement after it loops up to 11x. There is a total of 21 counts in the @tblSQL table.

WHILE EXISTS (SELECT 1 FROM @tblSQL WHERE ISNULL(Execstatus, 0) = 0)
BEGIN
	SELECT TOP 1 
		@tmpTblname = ObjectName
	  , @strSQL_NVAR_MAX = SQLStatement
	FROM @tblSQL 
	WHERE ISNULL(Execstatus, 0) = 0

	UPDATE @tblSQL
	SET Execstatus = 1
	WHERE ObjectName = @tmpTblname

	BEGIN
		------------------------------------------------------------
		IF OBJECT_ID('tempdb..##GlobalTEMPTable') IS NOT NULL
		--IF OBJECT_ID('SearchTMP','U') IS NOT NULL
		BEGIN
			DROP TABLE ##GlobalTEMPTable
			--DROP TABLE SearchTMP
		END
		------------------------------------------------------------
		--PRINT @strSQL_NVAR_MAX	
		EXEC(@strSQL_NVAR_MAX)

		IF EXISTS(SELECT 1 FROM ##GlobalTEMPTable)
		--IF EXISTS(SELECT 1 FROM SearchTMP)
		BEGIN
			SELECT ObjectName=@tmpTblname, * FROM ##GlobalTEMPTable
			--SELECT ObjectName=@tmpTblname,* FROM SearchTMP
			SELECT @blnMatchFound = 1
		END
		DROP TABLE ##GlobalTEMPTable
	END
END
#4

And what values are in @strSQL_NVAR_MAX that are being executed?

#5

sorry. :slight_smile: It is like:

SELECT * INTO ##GlobalTEMPTable FROM Dynamic_Table_N

#6

I have also tried it with
OPTION (OPTIMIZE FOR UNKNOWN)
OPTION (RECOMPILE)

at the end of

SELECT * INTO ##GlobalTEMPTable FROM Dynamic_Table_N

#7

First thing I might try is wrapping all of the insert/updates including the exec in begin transaction / commit transaction

#8

so what is the story behind this ? What are you attempting to do in plain English without code?

#9

BTrimPop, yes, I have tried that too.

Yosiasz, in plain English, I am doing a search for a value on all columns per table (loop). There are a total of 21 tables and it works as plan up to looping 11 times.

I am assuming it has to be a cache issue or some sort.

The same code worked fine on another database but for this database, it is bigger.

#10

in one database?

#11

Correct! One database, not at server level of all databases.

#12

how about this using sys objects

use yourDatabase

go

select 'select * from ' + t.name + ' where ' +  c.name + ' like ''%chicken%''' 
from sys.tables t join sys.columns c on t.object_id = c.object_id 

then use the result of the query result first column

#13

Yes, I did something like that but I had to loop through each table with a where clause. For that reason, I had to use the EXEC in a loop.

I did add these two and it seems to get me a bit further in the loop.

DBCC DropCleanBuffers
DBCC FREEPROCCACHE

Still not complete and very slow after loop #12.

#14

what I posted has a where clause. Is this something you run often or is this a one time thing?

Try this then

select 'select ''' + t.name + ''' from ' + t.name + ' where ' + c.name + ' like ''%chicken%'' union all'
from sys.tables t join sys.columns c on t.object_id = c.object_id

#15

Yosiasz, this is an on-going run.

with your above statement, do I still use it with EXEC?

Also, it seems it might be the database. I used my code in several databases now and it is running fine. Maybe it is an index thing on one of the tables.