Can anyone plz. let me know what the following err

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can anyone plz. let me know what the following err

Author

Topic

dataforums
Starting Member

14 Posts

Posted - 2005-05-04 : 17:06:28

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_%''.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 17:09:54

Use @STR1 instead of a global.

EXEC (@STR1)

Tara

dataforums
Starting Member

14 Posts

Posted - 2005-05-04 : 17:46:49

Thanks Tara. But still it gives the same error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 17:53:40

What is it that you are trying to do? Look at the error message. It is mentioning SYNC_DATAFORUMSERVER. This column doesn't exist in INFORMATION_SCHEMA.TABLES so how is this even going to work?

Tara

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-05 : 08:06:49

It looks like he simply dropped one of the '@' from his exec statement.

You need to do just like Tara said, and EXEC(@STR1) <<---- notice the parens

Execute it without the parens and you get the "stored proc does not exist" error.

dataforums
Starting Member

14 Posts

Posted - 2005-05-05 : 09:14:32

Thanks, guys. it works!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources