Doing an "Insert... Select" - not sure what is going on

[snufse1]

I have following query snippet:


declare @JobNumber int
declare @JobNumber1 char(12)
declare @ItemNumber char(20)
declare @ItemNumber1 char(8)
declare @DateFrom datetime
declare @DateTo datetime
declare @JdeDateFrom int
declare @JdeDateTo int
set @JobNumber = 3505040
set @JobNumber1 = ' 3505040'
set @ItemNumber = '01010 '
set @ItemNumber1 = '01010 '
set @JdeDateFrom = 0
set @JdeDateTo = 0

create table #JDE_LaborHrsDetailTable
( jde_job_date datetime,
jde_cost_code char(15),
jde_equipment_id char(10),
jde_equipment_name char(20),
jde_employee_name char(15),
jde_employee_hours decimal(8,2),
jde_equipment_hours decimal(8,2)

)

insert into #JDE_LaborHrsDetailTable(jde_job_date, jde_cost_code, jde_equipment_id, jde_equipment_name,
jde_employee_name, jde_employee_hours, jde_equipment_hours)
Exec ('Call QGPL.get_labdet(?,?,?,?)', @JobNumber1, @ItemNumber1, @JdeDateFrom, @JdeDateTo) AT AS400SRV_MSDASQL1

select * from #JDE_LaborHrsDetailTable order by jde_job_date

drop table #JDE_LaborHrsDetailTable


I am doing some testing and using the query analyzer. When I manually cancel the query, the spid status is set to "Killed/Rollback". The rollback never finishes and is sitting out there for hours and days. We have to restart the server. Now If I try to run the same query again (and there is a rollback spid out there waiting to finish) the query never completes. When I run just the "Select" portion it completes fine. Can somebody tell me what is happening and if there is a way to avoid this, do I need to do something to my code?

 

[snufse1]

Could this be the case?

Since I am using a temp table and when I kill the query the temp table is automatically dropped. The roll back is trying to use a table that no longer exists.

 

[Canelas]

Yes... the temp table is probably being dropped.
If you allow the query to finish normally, do you get any error?

Temp tables act as if they were normal tables while they are in memory. But after they get dropped, you can't recover that same table.

 

[snufse1]

If we let the query continue it might run for hours. I think I have the idea what is happening. Thank you.