I have an asp application that connects to a server (2005) and calls a sp. From time to time the queries do not complete and looking at the activity monitor there are several jobs out there running the same query. The only way to get the application working is to restart the server. Since I do not know too much how things works I hope to get some insight to what is going on.
I read an article saying
"When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query." I am not sure what this means.
Also when calling the stored procedure we always use the same log on, user id and password, not usre if this could create an issue.
Here is the sp and if someone could comment as to why several queries are running at the same time and never complete:
declare @JobNumber1 char(12)
declare @ItemNumber1 char(8)
declare @JdeDateFrom int
declare @JdeDateTo int
set @JobNumber1 = ' 3505040'
set @ItemNumber1 = '01010 '
set @JdeDateFrom = 108217
set @JdeDateTo = 108217
If Object_ID('tempdb..#JDE_LaborHrsDetailTable1') Is Not Null Drop Table #JDE_LaborHrsDetailTable1
create table #JDE_LaborHrsDetailTable1
( jde_job_date datetime,
Set NoCount On
insert into #JDE_LaborHrsDetailTable1(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_MSDASQL
select * from #JDE_LaborHrsDetailTable1 order by jde_job_date
drop table #JDE_LaborHrsDetailTable1