drop ##temp

due to unavoidable reasons i had to use a ## temp table in a SP,

ie i had to dynamically create a table whose (number of)columns i come to know at runtime..

if i do thi ::set @.sql = 'create #table....select some columns _ append varchar(10)'

then insert into #temp....temp is not valid here..so i used ##temp

now i need to explicitly drop it...also in catch block , i need to make a provision for droping it incase of an error in runnin proc...some kind os IF EXISTS drop ##temp.... as i dont know if it'll be created by that time or not..how do i do it..there is ofcource no entry in sysobjects....where is the entry for temp tables...tempdb dosent have system tables..!!

Can you provide SQl of SP...

~Mandip

|||

Nitin:

Since in this case it is a global temp table -- that is, it starts with ##, it will explicitly appear in sysobjects in tempdb:

create table dbo.##temp
( what varchar (5)
)

select uid, id, left (name, 20) as name from tempdb.dbo.sysobjects where name = '##temp'

if exists
( select 0 from tempdb.dbo.sysobjects
where name = '##temp'
and type = 'U'
)
begin
print 'Dropping the table.'
print ' '
drop table ##temp
end

select id, left (name, 20) as name from tempdb.dbo.sysobjects where name = '##temp'

-- -
-- O U T P U T :
-- -

-- uid id name
-- -- --
-- 1 853242293 ##temp
--
-- (1 row(s) affected)

-- Dropping the table.
--
-- id name
-- -- --

-- (0 row(s) affected)

Dave

|||

Thanks Dave.....

actually i was tryin this...

select * from sysobjects where name like '##temp'

hence the question.....now as u told it wors fine while i do this :: select * from tempdb.dbo.sysobjects where name like '##temp'

tell me if 2 ppl r running this sp simultaneously , will 1 of them get an error (##temp already exist..) or he'll have to wait for a tempdb lock.. ?

|||

Nitin:

Yes, because you are using a GLOBAL temp table this is a potential problem. However, since you are in a stored procedure, the procedure will automatically drop the temp table when it goes out of context should you use a non-global temp table -- one that starts with # instead of ##. I don't see why this would not be acceptable. Are you doing something in which you potentially need the temp table to have persistence beyond the scope of the procedure?

Dave

|||

dave :

i am creating my temp table by creating a sql string for it and then executing it..as num of columns is decided on the runtime..below is the query..

declare @.mytable varchar(500)

set @.mytable = 'CREATE TABLE ##temp (UserId int,'

select @.mytable = @.mytable + 'Plan_' + cast(PlanCode AS nvarchar(10)) + ' varchar(5),'

FROM (SELECT distinct PaymentPlanPlanTypeCode FROM #sometable) p

SET @.mytable = LEFT(@.mytable, LEN(@.mytable) - 1)

SET @.mytable = @.mytable + ')'

print @.mytable

exec (@.mytable)

i need this temp table in this 1 SP only but when i replace the ##temp with , #temp , the table is not getting created... i dont know why..probably some scope problem... also i ran out of the option of using table datatype as my proc further refers this table and i cant declare it inside a string..

see this as well :

--1

declare @.sql varchar(100)

set @.sql='create table ##tem (a int)'

exec (@.sql)

select * from #tem

error : invalid object #tem

--2

create table #tem (a int)

select * from #tem

gives the result.

|||

Nitin:

You are right, you do have a scope problem. I have a rather"dirty" idea, but I will have to test it out and unfortunately I won't have any time for at least an hour or so. Hopefully, you can get a better idea from somebody else can get you a better idea than what I have. I will check back in a while and if nobody else has come up with something I will test out my "dirty trick."

Dave

|||

Nitin:

One more question: I am assuming that this is not a "performance critical" procedure and that even though it is possible for multiple users to execute this procedure at the same time it is not likely. Is that assumption correct or is it rather likely that multiple users will execute your procedure concurrently?

Dave

|||

hi ,

this proc is for some mis report...though data will be large, its unlikely that more then a few users will use it... but agn..can be more then 1 at a time...

so i guess ur assumption can hold..

|||

Nitin:

This is my "dirty" suggestion. I tried it out and it seems to work. If you get another idea it will probably be better. Good luck.

Dave

-- --
-- First, I think I might have used the TABLOCK optimizer hint less than a handful
-- of times over my entire carreer. I don't think I've ever used TABLOCKX other
-- than in demo code. So to begin with I am iffy on the code that follows.
--
-- With that said, understand that what this code tries to use the execute string.
-- to build the format of the target table into a global temp table. Once that is
-- done a SELECT INTO is used to grab that format and use it to create the
-- intended local temp table. Once that is done the global temp table can be
-- dropped. This TABLOCKX optimizer hint means that this portion of the code
-- is single-threaded and is definite bottleneck. If this is not an intensely
-- used query this might be sufficient.
--
-- Unfortunately, there are additional bottlenecking problems. As I suspected,
-- The "select into" portion of the query puts exclusive locks on keys (1)
-- SYSOBJECTS, (2) SYSINDEXES and (3) SYSCOLUMNS of the tempdb database. Also,
-- exclusive intent locks are put on all three of these tables plus
-- (1) SYSCOMMENTS, (2) SYSDEPENDS, and (3) SYSPERMISSIONS and (4) SYSPROPERTIES.
--
-- If you wish to test this out, just comment out the COMMIT TRAN command,
-- run the procedure and then run SP_LOCK. To release the locks exeucte the
-- COMMIT TRAN statement.
--
-- Therefore, it is critical that if this kind of code is used in production that
-- the construction of the TEMP table take place OUTSIDE of the actual processing
-- transaction so that the code below executes in as few microseconds as possible
-- and reduces the profile of the bottleneck.
--
-- I also tested this with no transaction enclosures and exeucted the WAITFOR so
-- that I could see how it locked outside of any transaction enclosures. I
-- commented out my BEGIN TRAN and END TRAN satements, ran with the WAITFOR in
-- affect and did an SP_LOCK with an outside connection. No locks were retained.
-- What to understand with all of this is that (1) the TABLOCKX hint will single
-- thread this and that is not particular good; however, (2) SQL Server issues
-- locks that might otherwise single-thread you briefly anyway; so this might not
-- be TOO bad. (3) Always be careful how you use SELECT INTO syntax; it can
-- also bite you.
--
-- I really don't like this code; but if you don't get any other suggestion, it
-- might be worth a try.
--
-- Dave
--
-- --
--begin tran doWhat

exec ( 'create table ##what (what varchar (20) ) ' )

select * into #what from ##what (TABLOCKX) where 1=0

if exists
( select 0 from tempdb.dbo.sysobjects
where type = 'U'
and name = '##what'
)
drop table ##what

select * from #what

--waitfor delay '0:01:00.000'

drop table #what

--commit tran

go

-- -
--
-- -

-- spid dbid ObjId IndId Type Resource Mode Status
-- -- - - --
-- 55 2 6 0 TAB IX GRANT
-- 55 2 1 0 TAB IX GRANT
-- 55 2 2 0 TAB IX GRANT
-- 55 2 12 0 TAB IX GRANT
-- 55 2 9 0 TAB IX GRANT
-- 55 2 11 0 TAB IX GRANT
-- 55 2 1529959413 0 TAB Sch-M GRANT
-- 55 2 3 2 KEY (9401125ee398) X GRANT
-- 55 2 1 3 KEY (f50024d97b7f) X GRANT
-- 55 2 1 2 KEY (fc00a92d9bd9) X GRANT
-- 55 2 2 1 KEY (bc009dbece03) X GRANT
-- 55 2 1 1 KEY (bc004820585b) X GRANT
-- 55 2 3 1 KEY (bd00770e8a50) X GRANT
-- 55 2 1513959356 0 TAB Sch-M GRANT
-- 55 2 2 1 KEY (f500783daf5c) X GRANT
-- 55 2 3 1 KEY (f60025843207) X GRANT
-- 55 2 1 3 KEY (bc003d203e1f) X GRANT
-- 55 2 1 1 KEY (f50051d91d3b) X GRANT
-- 55 2 1 2 KEY (9b16f50455d2) X GRANT
-- 55 2 3 2 KEY (cd019d01c693) X GRANT
-- 55 33 0 0 DB S GRANT
-- 55 2 3 0 TAB IX GRANT

|||

thanks a lot for ur time dave....im tempted to use this..i'll do some testing and go for it... ya i cant run away from select into...infact that was the thing i was trying earlier while generating the query string...so im kinda ready for that...

cheers

nitin

|||

You can do below:

create table #tbl( /* put fixed columns here. those that are not decided dynamically )

-- use ALTER TABLE to add the columns dynamically

exec('alter table "#tbl" add c1 int, c2 int...')

select ...

Note, however that this has performance implications due to the excessive amount of recompilations triggered due to the schema changes. In above case, pretty much every line will trigger recompile of the entire SP (SQL2000) or statement (SQL2005).