Problems with queries hanging and not completing

[snufse1]

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,
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)
)

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

 

[markros]

To me this looks like you don't need to use temp table at all.

In SQL Server 2005 and up this can be done with CTE, though I don't see why do you need to se temp table here at all?

Why not do a straight select joining with the function (in SQL Server 2005 and up - CROSS APPLY)?

 

[markros]

Also are you sure you always close the connection? Is your front-end C# or VB.NET? Check using keyword in both languages.

 

[snufse1]

Front end is ASP/VB.Net 2005. It is my understanding that when you close a connection, the temp table will be killed, correct? Here is a snippet of the vb code where I close the connection after the try...catch

Try
.........
EquentialCommand.CommandType = Data.CommandType.StoredProcedure
EquentialCommand.CommandText = ("sp_LaborHrsCompareDetail")
EquentialCommand.Connection = EquentialConnection
mySqlDataAdapter.SelectCommand = EquentialCommand
mySqlDataAdapter.Fill(ItemDetailDataTable)
Session("ItemDetailDataTable") = ItemDetailDataTable
Catch ex As Exception
ASPNET_MsgBox("An error has occurred connection to server and loading temp table: " + vbCrLf + ErrorString)
End Try

EquentialCommand.Parameters.Clear()
EquentialConnection.Close()


If the sp never completes, no other usres are able to run the same proc. For some reason it is blocking further access to use the same sp. the status in the activity log says the spid status = runnable and the command = insert. Any suggestions please.

 

[markros]

The Close should in the finally section. Or just do some reading on USING keyword.

 

[markros]

BTW, how big is the result set returned? Storing it in a session may not be a good idea.

Though we did use the same design (storing dataset in a session) and I disliked it, but haven't found a good alternative in our case then.

 

[snufse1]

Normally less than 250 records if date parms are 0, otherwise 1 - 10 records. Not big at all.

Now, is there a way to set a time out on the EXEC and then drop the temp table. I have set the xxxCommand.CommandTimeout = 60. If thos occurs it looks like the sp is still running and trying to do a roll back when the temp table has been dropped. Does this make senese?

 

[markros]

There are different timeout parameters you can set. We discussed this problem on another forum a while back

1. Open SQL Server Enterprise Manager and find the server that contains your database.
2. Right click on the server name.
3. Select properties.
4. Choose the Connections tab.
5. Increase the query timeout value. (The value is in seconds. Set it to 0 for unlimited time)
6. Click OK.

(See <B>Re: Adjustment on the timeout</B> Thread #1302038 Message #1302358 in

http://www.UniversalThread.com

forum - sorry, unfortunately you had to be a member to see threads' content there).

 

[snufse1]

makros, thank you for your assistance. I will try to set the time out for the connection and see what happens. Very grateful for all your help.

 

[SQLSister]

Are you connecting to an AS400 box in the query? Perhaps if all the users hitting it are stalled the connection to the nonSQL Server box is down or very slow.

"NOTHING is more important in a database than integrity." ESquared

 

[snufse1]

Yes, connecting to db2/400 (ie AS/400). At this point there are only 2 users that have access (due to the problem described). In the same queru we are also connecting to sql server as well. I have included the complete sp. Maybe you see something that I do not see.

@JobNumber int,
@JobNumber1 char(12),
@ItemNumber char(20),
@ItemNumber1 char(8),
@DateFrom datetime,
@DateTo datetime,
@JdeDateFrom int,
@JdeDateTo int

as

create table #Equential_LaborHrsDetailTable
( eq_job_date datetime,
eq_cost_code char(10),
eq_pay_type char(50),
eq_pay_text char(50),
eq_equipment_id char(10),
eq_equipment_name char(20),
eq_employee_name char(12),
eq_job_craft char(15),
eq_employee_hours decimal(8,2),
eq_equipment_hours decimal(8,2),
eq_labor_start datetime,
eq_labor_stop datetime,
eq_labor_lunch datetime,
eq_labor_rest datetime,
eq_event_code char(1),
eq_event_name char(20)
)

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),
)

create table #Combined_LaborHrsDetailTable
( jobdate datetime,
costcode nvarchar (10),
paytype char(50),
paytext char(50),
equipmentid char(10),
equipmentname char(20),
employeename char(12),
jobcraft char(15),
eqemployeehours decimal(8,2),
eqequipmenthours decimal(8,2),
eqlaborstart datetime,
eqlaborstop datetime,
eqlaborlunch datetime,
eqlaborrest datetime,
jdeemployeehours decimal(8,2),
jdeequipmenthours decimal(8,2),
differencehrs decimal(8,2),
eqeventcode char(1),
eqeventname char(20)
)

insert into #Equential_LaborHrsDetailTable(eq_job_date, eq_cost_code, eq_pay_type, eq_pay_text,
eq_equipment_id, eq_equipment_name, eq_employee_name,
eq_job_craft, eq_employee_hours, eq_equipment_hours,
eq_labor_start, eq_labor_stop, eq_labor_lunch,
eq_labor_rest, eq_event_code, eq_event_name)
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_LaborDetailHoursCompare
@JobNumber, @ItemNumber, @DateFrom, @DateTo


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_MSDASQL


insert into #Combined_LaborHrsDetailTable(jobdate, costcode, paytype, paytext, equipmentid,
equipmentname, employeename, jobcraft, eqemployeehours,
eqequipmenthours, eqlaborstart, eqlaborstop, eqlaborlunch,
eqlaborrest, eqeventcode, eqeventname)
select eq_job_date, eq_cost_code, eq_pay_type, eq_pay_text, eq_equipment_id, eq_equipment_name,
eq_employee_name, eq_job_craft, eq_employee_hours, eq_equipment_hours, eq_labor_start,
eq_labor_stop, eq_labor_lunch, eq_labor_rest, eq_event_code, eq_event_name
from #Equential_LaborHrsDetailTable

update #Combined_LaborHrsDetailTable
set jdeemployeehours = #JDE_LaborHrsDetailTable.jde_employee_hours
from #Combined_LaborHrsDetailTable, #JDE_LaborHrsDetailTable
where #Combined_LaborHrsDetailTable.jobdate = #JDE_LaborHrsDetailTable.jde_job_date and
#Combined_LaborHrsDetailTable.employeename = #JDE_LaborHrsDetailTable.jde_employee_name and
#Combined_LaborHrsDetailTable.eqemployeehours = #JDE_LaborHrsDetailTable.jde_employee_hours and
#Combined_LaborHrsDetailTable.equipmentid = ''

update #Combined_LaborHrsDetailTable
set jdeequipmenthours = #JDE_LaborHrsDetailTable.jde_Equipment_hours
from #Combined_LaborHrsDetailTable, #JDE_LaborHrsDetailTable
where #Combined_LaborHrsDetailTable.jobdate = #JDE_LaborHrsDetailTable.jde_job_date and
#Combined_LaborHrsDetailTable.employeename = #JDE_LaborHrsDetailTable.jde_employee_name and
#Combined_LaborHrsDetailTable.eqequipmenthours = #JDE_LaborHrsDetailTable.jde_equipment_hours and
#Combined_LaborHrsDetailTable.equipmentid = #JDE_LaborHrsDetailTable.jde_equipment_id


insert into #Combined_LaborHrsDetailTable (jobdate, costcode, employeename, jdeemployeehours)
select t2.jde_job_date, t2.jde_cost_code, t2.jde_employee_name, t2.jde_employee_hours
from #JDE_LaborHrsDetailTable AS t2
where not exists (select * from #Combined_LaborHrsDetailTable AS t1 where t1.jobdate = t2.jde_job_date and
t1.employeename = t2.jde_employee_name and
t1.jdeemployeehours = t2.jde_employee_hours
)
and
t2.jde_employee_hours > 0


insert into #Combined_LaborHrsDetailTable (jobdate, costcode, employeename, equipmentid, equipmentname, jdeequipmenthours)
select t2.jde_job_date, t2.jde_cost_code, t2.jde_employee_name, t2.jde_equipment_id, t2.jde_equipment_name,
t2.jde_equipment_hours
from #JDE_LaborHrsDetailTable AS t2
where not exists (select * from #Combined_LaborHrsDetailTable AS t1 where t1.jobdate = t2.jde_job_date and
t1.equipmentid = t2.jde_equipment_id and
t1.employeename = t2.jde_employee_name and
t1.eqequipmenthours = t2.jde_equipment_hours
)
and
t2.jde_equipment_hours > 0



select *
from #Combined_laborHrsDetailTable
order by jobdate, costcode

drop table #Equential_LaborHrsDetailTable
drop table #JDE_LaborHrsDetailTable
drop table #Combined_LaborHrsDetailTable