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.

ODBC Locking

We have an application using ODBC to SQL Server 2000. It
does not appear to release locks properly. From speaking
with the developer, I think the driver may be adding to
the problem by adding "set implicit_transactions on".
Could this be? Has anyone else seen this problem?
Thanks,
JoelTransactions are not handled the way you would think in ADO. The following
KB article describes the issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;198024
For a single statement there is no noticible difference. When you start
nesting commands and walking through cursors it can rapidly expand a
transaction far past what you think it does.
Enjoy,
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Joel" <joel.griffin@.teijinmonofil.com> wrote in message
news:418601c3560d$607faed0$a001280a@.phx.gbl...
> We have an application using ODBC to SQL Server 2000. It
> does not appear to release locks properly. From speaking
> with the developer, I think the driver may be adding to
> the problem by adding "set implicit_transactions on".
> Could this be? Has anyone else seen this problem?
> Thanks,
> Joel