urgent help please - SQL Server Forums

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)

urgent help please

Author

Topic

cbeganesh
Posting Yak Master

105 Posts

Posted - 2005-07-06 : 13:25:02

i have a procedure which has the folloing code, I was able to register the procedure , but when i execute the procedure it says "INVALID OBJECT tempfilter, Is there any other way to capture data from a dynamic sql to a table and use it

set @sql = 'select * from into tempfilter from '@cms+'.dbo.tblfilter where fkGdrsJobid = '+cast(@jobid as varchar)
print @sql
EXEC(@SQL)

while (select count(*) from tempfilter) > 0
begin
--processing steps

end

Kristen
Test

22859 Posts

Posted - 2005-07-06 : 13:30:11

set @sql = 'select * ~~from~~ into tempfilter from '+@cms+'.dbo.tblfilter where fkGdrsJobid = '+cast(@jobid as varchar)

EDIT: Note that the table "tempfilter" must not pre-exist

Kristen

cbeganesh
Posting Yak Master

105 Posts

Posted - 2005-07-06 : 13:32:49

yes , i Typed it wrong when posting but it is not there in the actual procedure

Kristen
Test

22859 Posts

Posted - 2005-07-06 : 13:37:30

Does the person executing the Procedure (NOT the person that created the procedure) have permission to create the table "tempfilter"?

What exactly does your PRINT statement, before the EXEC, output?

Is there any other way to capture data from a dynamic sql to a table and use it

Pre-creating the table (i.e. with a CREATE TABLE statement in the SProc) and then doing an
INSERT INTO MyTable SELECT * FROM MyOtherTable
would be preferable - that would show up better if the table create was failing. Also, you could put PK/indexes etc. on the table (and I believe SQL would perform more efficiently)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources