ODBC Timeout Error

Hi All,

I have one problem in connecting to SQL Server DSN thru Microsoft ODBC. The program was working properly and suddenly from few days, getting "-21472117871 [Microsoft][ODBC SQL Server Driver] Timeout Expired". And no new MS patches or SP installed recently.

I am using this in vbscript with the database connection with execute statement. Sometimes it takes 20-40 secs to get the resultset or sometimes timesout. What could be reason ? Is the database size, memory, transaction log size ?

For Example : Set adoAcctsRst = comDatabaseConnection.Execute("Services.dbo.AISSP_GetAccts '" & strUser & "' " )

Services is the Database, and AISSP_ is the stored procedure to execute with the input parameter as UserName. comDataBaseconnection is the command to connect to DSN.

Its getting timedout in this statement. What could be the solution ? Is it at the SQL Database or network connection ?

Appreciate for ur immedaite reply as this is urgent !!
Thanks in advanceWell it could be that the load on the DB is too much, it could be that the table needs a decent index on for the search criteria it could be load on the network.

You could also include your timeout on your connection when you connect to the database...

I would try the others first though,.. starting with the index...|||Do you have a baseline of what this stored procedure should take to run ? Have you run the stored procedure in query analyzer (how long does it take) ? If it taks a long time, copy the stored procedure content in qa and see if the duration is the same. If you had a recent jump/decline in the number of records that this stored procedure normally handles, this could also cause these problems.