trying to drop a temporary table SQL Server 2005 1

[Marckas]

I am trying to drop a temporary table but I get this error message. This same script used to run in SQL Server 2000 and now we upgraded to 2005 and its not working. I am not sure if it is a permission issue or not.

ERROR MESSAGE
Cannot drop the table '#outline_codes', because it does not exist or you do not have permission.

SCRIPT:
--drop outline codes table before running
drop table #outline_codes
(
select *
into #outline_codes
from MSP_outline_codes
where proj_id in
(select PROJ_ID from MSP_PROJECTS where PROJ_TYPE = 2)
and code_uid > 0
)
go

--drop #funded_portfolio_projects table

drop table #funded_portfolio_projects
--table to identify funded portfolio projects
(
select mcf.proj_id
into #funded_portfolio_projects
from #outline_codes oc, msp_code_fields mcf
where oc.code_uid = mcf.code_uid
and (((oc_field_id = 188744593 and oc_name like 'yes')
or (oc_field_id = 188744592 and oc_name like 'application portfolio')))
or mcf.proj_id = 283
group by mcf.proj_id
having count(oc_field_id) > 1
)

go

--drop the milestone table

drop table #dashboard_milestones

--table to dashboard milestone information
(
select p.proj_id,
p.proj_name,
t.task_uid,
t.task_name,
oc_name,
t.TASK_START_DATE,
t.TASK_FINISH_DATE,
t.TASK_ACT_START,
t.TASK_ACT_FINISH,
t.task_base_start,
t.task_base_finish,
t.[TASK_WORK]/60000 as work,
t.task_act_work/60000 as act_work,
t.task_base_work/60000 as base_work,
t.TASK_PHY_PCT_COMP,
t.Task_Base_cost*.01 as base_cost,
t.Task_cost *.01 as cost,
t.Task_act_cost*.01 as act_cost,
t.Task_pct_comp as PCT_COMP,
t.Task_pct_work_comp as PCT_work_COMP,
t.Task_BCWS*.01 as BCWS
into #dashboard_milestones
from #outline_codes toc, msp_code_fields mcf, MSP_projects p, msp_tasks t, #funded_portfolio_projects fp
where toc.code_uid = mcf.code_uid
and p.proj_id = mcf.proj_id
and p.proj_id = t.proj_id
and p.proj_id = fp.proj_id
and mcf.code_ref_uid = t.task_uid
and (oc_name like 'accepted'
or oc_name like 'tentative')
and p.proj_version = 'published'
)
go


--add highest summary level tasks from each project to #dashboard_milestones table

insert into #dashboard_milestones
(
proj_id,
proj_name,
t.task_uid,
task_name,
oc_name,
TASK_START_DATE,
TASK_FINISH_DATE,
TASK_ACT_START,
TASK_ACT_FINISH,
TASK_BASE_START,
TASK_BASE_FINISH,
work,
act_work,
BASE_WORK,
TASK_PHY_PCT_COMP,
base_cost,
cost,
act_cost,
PCT_COMP,
PCT_work_COMP,
BCWS
)
select p.proj_id,
p.proj_name,
t.task_uid,
t.task_name,
null as oc_name,
t.TASK_START_DATE,
t.TASK_FINISH_DATE,
t.TASK_ACT_START,
t.TASK_ACT_FINISH,
T.TASK_BASE_START,
T.TASK_BASE_FINISH,
t.[TASK_WORK]/60000 as work,
t.task_act_work/60000 as act_work,
T.TASK_BASE_WORK/60000 AS BASE_WORK,
t.TASK_PHY_PCT_COMP,
t.Task_Base_cost *.01 as base_cost,
t.Task_cost *.01 as cost,
t.Task_act_cost *.01 as act_cost,
t.Task_pct_comp as PCT_COMP,
t.Task_pct_work_comp as PCT_work_COMP,
t.Task_BCWS*.01 as BCWS
from MSP_projects p, msp_tasks t, #funded_portfolio_projects fp
where p.proj_id = t.proj_id
and p.proj_id = fp.proj_id
and t.task_uid = 0
and p.proj_version = 'published'

 

[gmmastros]

You need to check to see if the temp table exists before dropping it.

http://wiki.lessthandot.com/index.php/Check_If_Temporary_Table_Exists

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

And to expand a little, local temp tables go out of scope and are dropped by the system when the session is closed, so unless you run this procedure multiple times in a session, there's no reason to drop the table. If so, then just follow Denis Gobo's tutorial.

Now George, another question I'd like to pose is: why did this code "work" for him in SQL 2000 and not in SQL 2K5? That seems a bit strange.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[gmmastros]

another question I'd like to pose is: why did this code "work" for him in SQL 2000 and not in SQL 2K5?

I have no idea. Without more details it would be hard to explain. It's probably like you mentioned already, the session did not go out of scope so the temp tables exist. Or some other (not shown) code.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

I am guessing that it failed the first time he ran it in SS2005. Reason: the table hadn't been created yet. I've run into this issue and felt dumb when I realized the issue, that's why all my scripts that drop a table check to see if it exists first. Several times I've promoted a script from development to QA or Production and had it fail because the table wasn't created at least once before the script tried to drop it.

Marckas....either change the drops to IF EXIST..DROP statements or comment out the drops the first time you run it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Marckas]

Thanks a lot guys for your answers. It was really helpful...

 

[philhege]

Bill, I wish they'd put that in as a script generation option. I get tired of the ol' cut and paste, but then I'm getting lazy in my old age.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[markros]

What is your SQL Server version? You can activate this option from the Tools/Options menu and there SQL Server Object / Scripting / Include IF NOT EXISTS clause.

 

[SQLBill]

Markros,

I tried your suggestion and it works - mostly. Doesn't include a DROP statement, but it will cause it to not try and create an object if it already exists.

Phil H,

SQL Server 2008 allows you to right click on an object and script it as Create, Drop, or Drop/Create.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875