temp table scope error - 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

SQL Server Development (2000)

temp table scope error

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-04-04 : 16:55:45

This the part of a stored procedure.
The #tmp1 scope is limited to the exec statement. I need a way to over come this. I can't have a create statement in the Sp because I have to use the same for diff tables. And globle table will be last choice. Any better solution will be helpfull.

exec( 'select * into #tmp1
from testDB.dbo.' + @test_template_file )

set @sql_cmd= 'BULK INSERT #tmp1 from ' +
@txt_file_A + char(39) +
' with (FORMATFILE = ' +
@test_format_file + char(39) + ', TABLOCK) '
exec ( @sql_cmd )

thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-04-04 : 19:03:21

Most DBA's will tell you that generic stored procs are not ideal for performance etc, but sometimes they are difficult to get around.

I'd suggest building your entire statement as a string (SELECT INTO and BULK INSERT) and then running it. Then the temp table will be in scope.

HTH,

Tim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 01:38:52

or use Global temp table ##tmp1

Madhivanan

Failing to plan is Planning to fail

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-04-06 : 18:25:36

thanks for replies, I am try to will entire statments as string

create table #tmp_data (prod_no varchar(50))
set @sql_cmd1= (
'select * into #tmp1 from '+ @test_template_file + '
BULK INSERT #tmp1 from ' +
@txt_file_A + char(39) +
' with (FORMATFILE = ' +
@test_format_file + char(39) + ', TABLOCK)
select prod_no into #tmp_data from #tmp1'
)

exec(@sql_cmd1)

I am getting it not a valid indentifier error

thanks in advance

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 18:56:35

Make sure that the variable you are putting your statement into (@sql_cmd1) is declared as a large enough variable to hold the entire statement string.

---------------------------
EmeraldCityDomains.com

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-04-06 : 19:07:12

It is varchar(5000) and tried with varchar(8000) also, but I am getting same error

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-06 : 19:33:10

add a print statement to print out the @sql_cmd1 to verify

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-04-06 : 20:25:50

It is working now, just some change in '' worked.

thanks