Stored proc not creating temp table

[jeffwest21]

I have a stored proc that when I run the code on it's own

create table #Campaign_Hours (
Dateperiod varchar(20),
USerID varchar(20),
total_dialler_secs int,
logged_on_time_secs int,
busy_secs int,
notready_secs int,
preview_secs int,
ready_secs int,
wrapup_secs int,
waittime_secs int,
avg_ready_secs float,
avg_preview_secs float,
avg_busy_secs float,
avg_wrapup_secs float,
logged_off_secs int,
avg_logged_off_secs float,
total_dialler_secs_minus_idl int
)

Creates a temp table, then when running this

Insert into #Campaign_Hours
select 
'Day',--@Event_ID Dateperiod,
--Reportdate,
--campaign,
user_id,
sum(total_dialler_secs) total_dialler_secs,
sum(total_login_secs) logged_on_time_secs,
sum(busy_secs) busy_secs,
sum(notready_secs) notready_secs,
sum(preview_secs) preview_secs,
sum(ready_secs) ready_secs,
sum(wrapup_secs) wrapup_secs,
sum(preview_secs)+sum(ready_secs)  waittime_secs,
sum(avg_ready_secs) avg_ready_secs,
sum(avg_preview_secs) avg_preview_secs,
sum(avg_busy_secs) avg_busy_secs,
sum(avg_wrapup_secs) avg_wrapup_secs,
sum(logged_off_secs) logged_off_secs,
sum(avg_logged_off_secs) avg_logged_off_secs,
sum(total_dialler_secs_minus_idle) total_dialler_secs_minus_idle
--into #Campaign_Hours
from
(select 
user_id,
case when ltrim(rtrim(agent_state)) = 'Busy' then sum(duration) else 0 end busy_secs,  
case when ltrim(rtrim(agent_state)) = 'Not Ready' then sum(duration) else 0 end  notready_secs,
case when ltrim(rtrim(agent_state)) = 'Preview' then sum(duration) else 0 end preview_secs,
case when ltrim(rtrim(agent_state)) = 'Ready' then sum(duration) else 0 end ready_secs,
case when ltrim(rtrim(agent_state)) = 'Wrapup' then sum(duration) else 0 end wrapup_secs,
case when ltrim(rtrim(agent_state)) in ('Busy','Preview','Ready','Wrapup') then sum(duration) else 0 end total_login_secs,
case when ltrim(rtrim(agent_state)) is not null then sum(duration) else 0 end total_dialler_secs,
case when ltrim(rtrim(agent_state)) = 'Busy' then avg(cast(duration as float)) else 0 end avg_busy_secs,  
case when ltrim(rtrim(agent_state)) = 'Not Ready' then avg(cast(duration as float))else 0 end  avg_notready_secs,
case when ltrim(rtrim(agent_state)) = 'Preview' then avg(cast(duration as float)) else 0 end avg_preview_secs,
case when ltrim(rtrim(agent_state)) = 'Ready' then avg(cast(duration as float)) else 0 end avg_ready_secs,
case when ltrim(rtrim(agent_state)) = 'Wrapup' then avg(cast(duration as float)) else 0 end avg_wrapup_secs ,
case when ltrim(rtrim(agent_state)) = 'Logged Off' then sum(duration) else 0 end logged_off_secs,
case when ltrim(rtrim(agent_state)) = 'Logged Off' then avg(cast(duration as float)) else 0 end avg_logged_off_secs ,
case when ltrim(rtrim(agent_state)) <> 'Break' then sum(cast(duration as float)) else 0 end total_dialler_secs_minus_idle 
from  dialler_hours
where reportdate between @startdate and @endate
--and user_id not in ('Jeffw1','AndrewW','BenM','janeT','JeffW','AndrewWh','BenJi','JeffW2')
--and campaign = @campaign_ID
and campaign ='BIS'
group by agent_state,user_id)w
group by user_id

Enters data into the table.

However, when I run the code together as a stored proc I get the error
'Msg 208, Level 16, State 0, Procedure sp_bis_base_table, Line 531
Invalid object name '#Campaign_Hours'.

I don't understand how it can run the code independently yet not in the SP.

I have tried running this under three different permissions, each higher than the one before yet it still won't run, anybody come across this??

'Clever boy...'

 

[gmmastros]

This should work. Is there any conditional logic in the stored procedure that could cause this? I notice that the error occurs on line 531 so there's a lot of code that could get executed before this.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jeffwest21]

Thanks, kind of what I thought, it should work, and does on a dev box but not a live one, both are set up exactly the same

Getting the code re-written at the moment now to see if that makes a difference.

'Clever boy...'

 

[genomon]

 select
'Day',--@Event_ID Dateperiod,
--Reportdate,
--campaign,

....
 --into #Campaign_Hours

....
 where reportdate between @startdate and @endate

Just curious why the INTO clause is commented out? Cannot do an insert into something that isn't there.
Also Reportdate is commented out, but used in the WHERE clause.

Beir bua agus beannacht!

 

[jeffwest21]

Than ks for this, managed to sort this out another way in the end, the code was commented out as I was testing and I just posted the wrong code snippet.

'Clever boy...'