Problem creating Temporary Table

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)

Problem creating Temporary Table

Author

Topic

augustin_p
Starting Member

21 Posts

Posted - 2002-04-11 : 04:48:54

-- CODE STARTS HERE --

declare @FactoryUnitId varchar(15)
declare @strSql varchar(1000)
declare @tmpSql varchar(400)

set @FactoryUnitId = '%'

drop table #MaterialType
drop table #MaterialReceipts

Create Table #MaterialType
(
RowId int identity,
MaterialTypeCGNNO varchar(15),
MaterialName varchar(20)
)

insert into #MaterialType
(
MaterialtypeCGNNO,
MaterialName
)
select
MT.CGNNO,
MT.MaterialName
from
Material_Type MT

set @strSql = 'CREATE #TABLE MaterialReceipts (RowId int identity, FactoryUnitName varchar(10), DepartmentName varchar(20), '

set @tmpSql = Null
select @tmpSql = Coalesce(@tmpSql + ' numeric(18,3),','') + tmp.MaterialtypeCGNNO
from #MaterialType tmp

select @strSql = @strSql + @tmpSql + ' numeric(18,3), '

select @strSql = @strSql + ' Total numeric(18,3))'

print @strSql

exec(@strSql)

select * from #MaterialReceipts

-- CODE ENDS HERE --

When the above Sql Statement is executed, it gives an 'Invalid Object Name (#MaterialReceipts)' as the termporary is not created after executing the Sql Statement contained in @strSql variable. But When i execute the Statement (got by printing @strSql variable), it creates the table successfully. Is it possible to create a temporary table by executing @stSql. Using the same way, if i create a permanent table or a global temporary table, it is succesfully created.

Im trying to build a temporary table (for a report) by building columns dynamically based on records from other table. Any other better ways of achieving this is also welcome

Thanks,
prasanna

jongregg
Starting Member

31 Posts

Posted - 2002-04-11 : 05:30:16

The problem stems from the fact that tem,porary tables are only available inside the session you are running.

I don't know for certain but my theory on why this happens is - When you create dynamic SQL and run the EXEC command, it spawns a different process so the temporary table is only available on the spawned process as opposed to the original process so it will not be seen.

We've had the same problem here and the only way around it was to create a physical table as opposed to a temporary table and then drop the table once you've finished with it.

Hope this helps

Jon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources