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 advance
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.