"-2147217871, [Microsoft][ODBC SQL Server Driver]T

TimeOut expired
I'm running sql server 2000 and I'm getting this error inconsistancy.
I change the timeout to 0 and nothing
any ideas?

The error is clear enough in itself, but it seems to be generic - it doesn't say what is causing the time-outs, so you have to look at what exactly it is that you're doing.

What kind of statement you are executing? Is it just one statement that's timing out? Are all connections to the server timing out?

etc. etc.

It's a insert statement into a huge table with millions of rows...
I'm trying right now with the rowlock option
let me know thanks

i think that means there is a table lock on the table,
look for this
specifying a rowlock on the insert does not help if the table has a tablock on it

there is No TabLock on that table and it happens sometimes only like 9:30AM and 4PM
mostily

Hi,
I have the following code, which is giving the timeout error. The SP being called in calculation intensive but I have tried increasing all possible values. Please suggest


*************************************************************
On Error GoTo errHandler

' Create the ADO objects
Dim RS As ADODB.Recordset
Dim cmd As ADODB.Command

Set RS = New ADODB.Recordset
Set cmd = New ADODB.Command

'Init the ADO objects
cmd.ActiveConnection = gsConnectionString
cmd.CommandTimeout = 0
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc

'Create the stored proc parameters
CreateParams cmd, varParams

' Execute the query for readonly
RS.CursorLocation = adUseClient
RS.CacheSize = 500
RS.Open cmd, , adOpenForwardOnly, adLockReadOnly

' Disconnect the recordset
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set RS.ActiveConnection = Nothing

' Return the disconnected recordset
Set gdbrsRunSPReturnRS = RS
Set RS = Nothing
Exit Function

errHandler:
Select Case Err.Number
Case -2147217871 'Timeout Expired - error number
Resume
End Select
*************************************************************

Run the sql trace to find long running queries/procedures and make sure you add all error events.

Check any procedure takes more than your timeout settings and tune them. And also find the procedures called from timeout page to narrow the sql trace filter.

Mohammed.

I am posting the stored proc. for your referece...


ALTER Procedure dbo.usp_Opr_Productivity_Fetch
@PUserIdvarchar(30),
@PFromDatedatetime,
@PToDatedatetime
as
Begin
IF @PFromDate is null
Set @PFromDate = dateadd(day,-1,getdate())

IF @PToDate is null
Set @PToDate = getdate()
select count(al_id) No_of_Forms,al_formgroup FormGroup,AL_DE1Processing Processing_Flag,al_action Operator_Action,AL_OPERATORID UserID, UL_UserName FullName
, sum(isnull(OE_ErrorCount,0)) error_Count
from userlogin u,auditlog a left outer join operatorError o
on o.oe_batchname = al_functionname and o.oe_operatorid =AL_OPERATORID and o.oe_de1processingflag =al_de1processing and oe_datachangedto not like 'Other'
where al_operatorid = ul_userid and al_action in ('dataentry','hold') and
al_createdon > @PFromDate and al_Createdon <= @PToDate
group by al_formgroup,AL_DE1Processing,al_action,AL_OPERATORID,UL_UserName
order by al_operatorid,al_formgroup,AL_ACTION
End


****
The execution plan shows the cost of 60% for accessing table AuditLog

I already have Index defined for the table.

However I have recompiled the SP and will seek your help if the problem is still not resolved.

Thanks

quote:Originally posted by satya

...try to reindex that specified index too in this case.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Yeah its also a fine way..
Because i did the same thing when i got that error..

Regards,
Shiva
(+91-98948-45445)
[Looking For a Job in India]