Can anyone plz. let me know what the following error means?

[dataforums]

I get an error message for the foll. dynamic sql code:

********************************************************
Declare temp_cur CURSOR FOR
select table_name from INFORMATION_SCHEMA.TABLES where table_name like 'SAMP_TMP_%'

OPEN temp_cur
FETCH NEXT FROM temp_cur into @temp_tab

PRINT 'Record Status' + CAST(@@FETCH_STATUS as varchar)
WHILE @@FETCH_STATUS <> -1

BEGIN
set @orig_tab = replace(@temp_tab,'_TMP','')
set @str='SAMP_TMP_%'
select @@STR1='select '+@orig_tab+' from INFORMATION_SCHEMA.TABLES
where table_name like '+char(39)+''+@str+''+char(39)+''

exec @@STR1
END

*******************************************************

ERROR:

Server: Msg 2812, Level 16, State 62, Line 23
Could not find stored procedure 'select SYNC_DATAFORUMSERVER from INFORMATION_SCHEMA.TABLES
where table_name like 'SAMP_TMP_%''.

*******************************************************

 

[TJRTech]

Yes, you need an extra set of single quotes around the 'SAMP_TMP_%' string.

I suspect it is getting formated in the @@STR1 as something like:

select FOO from INFORMATION_SCHEMA.TABLES
where table_name like SAMP_TMP_%

when what you want is:

select FOO from INFORMATION_SCHEMA.TABLES
where table_name like 'SAMP_TMP_%'

TJR

 

[JamesLean]

The actual reason for that error is that when you use EXEC to execute a dynamic SQL string you need to surround the string in parentheses:

EXEC(@sql)

However, that code sample contains many more errors than just that (but I'll assume it's not your actual statement...) ;-)

--James