April 13, 2006 at 5:00 am
Scenario 1 : I use temporary table to run my report. If the report was stopped by user inbetween next time when I run report it gives me error that the table exists. What should i Do for this.
Scenario 2 : I use table variable instead of temporary table. When the row size increase the performance go down and the report never load at all.
Please suggest me a solution
April 13, 2006 at 5:37 am
In the beginning of your procedure code that this report is using, check for the existence of the temporary table that your procedure creates...if it exists, drop it.
You can also use a scheme to generate temp tables and tag the datetime at the end of it to make them unique...if these are local temp tables, then when the session goes away, they will be dropped automatically. Global temp tables go away when all sessions referencing them are gone.
April 14, 2006 at 2:36 am
I use my stored procedure sp__droptable to test existance of a table ( also temp table ) and ropt it.
You can use sp__droptable '#tmp'.
Script to create the procedure ( in master ) :
use master
go
if object_id('dbo.sp__droptable') is not null
begin
print 'drop procedure dbo.sp__droptable'
drop procedure dbo.sp__droptable
end
go
print 'create procedure dbo.sp__droptable'
go
create procedure dbo.sp__droptable
(
@tbl sysname
) as
begin
declare @SQL nvarchar(1024), @FROM nvarchar(255), @WHERE nvarchar(255)
set @SQL = N'drop table ' + @tbl
if @tbl like '#%'
begin
set @tbl = 'tempdb..'+@tbl
set @WHERE = N'TABLE_NAME like ''' + parsename(@tbl,1) + N'[_][_]%' + N''''
end
else
begin
set @WHERE = N'TABLE_NAME = ''' + parsename(@tbl,1) + N''''
end
set @WHERE = @WHERE + N'
AND TABLE_SCHEMA = ''' + isnull(parsename(@tbl,2),user_name()) + N'''
AND TABLE_TYPE = ''BASE TABLE'' )'
set @FROM = isnull(parsename(@tbl,3),db_name()) + N'.INFORMATION_SCHEMA.TABLES'
set @SQL = N'
if exists (
select 1
from ' + @FROM + N'
where ' + @WHERE + N'
begin
print ''' + @SQL + N''' ' + @SQL + N'
end '
if object_id(@tbl) is not null
begin
exec sp_executesql @SQL
end
end
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply