jeffwest21
IS-IT--Management
I have a stored proc that when I run the code on it's own
Creates a temp table, then when running this
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...'
Code:
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
Code:
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...'