I am wanting to know why I keep getting a 'Timeout Expired' error when I run my application. I am calling a 'LogInProd' procedure that sets the connection string of the connection, the connection timeout is set to 0 and the commandtimeout is set to 0. My query takes about eight to nine minutes to run in QA. (I know that's a lot of time, but the dba's can't put indexes on for me.) I keep getting the timeout error when the timeout should be infinite since I set the command timeout to zero and the connection timeout to zer. Why won't this work? Can anyone help?

I assume that you are saying that you have some sort of web application that connects to the sql server, set your command time out and conneciton time out to something like 1200 (20Mins) and see if that corrects your problem.

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

I have set both time outs to as high as 2000 and still get the error after 30 seconds. Could the prepared statements in my application have anything to do with this? I have read a few forums that mention this and some that have said re-start the SQL server. Not sure what to do.

The only time i have ever had this problem was when i was working with a MySQL server and passing in massive amounts of data; i have never had this problem with MS SQL server so I am not quite sure? =
--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

I guess I am a little confused as to "calling a 'LogInProd' procedure that sets the connection string, ...".

You need a connection to call the proc anyway so your connection is already established. The other thing that came to mind is the commandtimeout. If you set the commandtimeout, then close/dispose of the command, your timeout needs to be reset when you create a new command, unless you have a global command which isn't really a good idea.


Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

My 'LogInProd' procedure is called from FormLoad and sets the connection and opens it. After it is opened, I create my parameters for the query. If I didn't get a timeout error, the query would run and then close the connections after the recordset was received. I set 'cnServer As ADODB.Connection and rsServer as ADODB.Recordset as globals, then set their connection strings, etc in the procedure. The connection and recordset are not closed until I run the query in a different function after the parameters have been set. Does this help?

Do you mean to say that you are passing around an ADODB.Connection and Recordset in session?! Having a global connectionstring is ok, but you should never never never pass around database objects in session. (It seriously degrades your preformance)

Can you execute other commands? Is this a 30 second time out ALL the time or just with this particular query?


--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

Yeah, I agree with dparsons. Database objects should be opened only when needed. For example:

Sub DoSomething()

  OPen connection
  create command
  SET TIMEOUT
  execute command
  dispose command
  close connection

end sub



Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

I have changed my code so that the connections are set and open in the same function that I run my query in. After my query is supposed to run, the connections are closed. It still times out after 30 seconds. I have looked at our other applications and they seem very close and they do not time out. I am querying about one million rows in the database and summing the last three digits in a number for about 60,000 rows. I'm thinking it should work now since everything is together but it still times out! I appreciate the comments and help. Any other ideas?

Can you send a sample of your code? It's not that we don't trust you, but maybe a second (third, fourth, etc) set of eyes might help.

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html