locked
30 sec Timeout RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

     

    I am using SQL-Server 2000 SP4 (on Windows 2003 SBS SP2 - not R2!).

     

    I use MS Excel with ADO to connect to the SQL-Server. When running a complicated query I get a timeout after
    30 sec with this connection "Microsoft OLE DB Provider for SQL Server".
    However, I do not get a timeout when using "SQL Server with ODBC".

     

    What would be the preferred method to query an SQL Server?

     

    1) SQL native Client (ODBC)
    2) SQL Server (ODBC)
    3) Microsoft OLE DB Provider for SQL Server
    4) .Net Providers (but I could not find a free one)

     

    Can you tell me why there is no timeout when using ODBC but there is one
    when using OLE DB Provider for SQL? Where can I set that timeout? There are
    some possible settings in SQL Server but none of them is used (= no timeout)
    or it is 600 sec. Pls. help to find the setting for timeout.

     

    regards

     

    arno

    Wednesday, January 23, 2008 8:42 AM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Are you using a Command object to execute your queries? If yes, the command object has a timeout property. The default value is 30 seconds. You should set it to a higher value.
    Wednesday, January 23, 2008 9:25 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

     

    this is in my code:

    Code Snippet

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    rs.Open sqlstring.Value, cn, adOpenStatic, adLockReadOnly

     

     

    I do not use something like mentioned in online help to ADO 2.5 (default would be 30sec for CommandTimeout)

     

    Code Snippet
    Set x = New ADODB.Command
    Set x.ActiveConnection = Cnxn
    x.CommandTimeout = 15

     

     

    However, I use the same code to query, I just change the connection string - one "works" one does not. In my code I open a recordset and paste it to excel, I do not create records, alter data etc.

     

    I would guess that I cannot read the CommandTimeout property as I do not have a command object. However, I can set cn.commandtimeout = 99 where cn is my connection. But there is no timout, I'd guess because there is no command object. This is confusing.

     

    There is a ConnectionTimeout Property for the Connection opject with a default timeout of 15 sec. I read this property and it shows 15 sec.

     

    arno

     

    EDIT: sorry for beeing confused.

    This works!

    Code Snippet

    Set cn = New ADODB.Connection
    cn.CommandTimeout = 1

     

     

    Wednesday, January 23, 2008 11:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Connection object has a property "CommandTimeout". Try making that a higher value. here is some documentation : http://msdn2.microsoft.com/en-us/library/ms807027.aspx

     

     

    Wednesday, January 23, 2008 12:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you!

     

    Thursday, January 24, 2008 7:57 AM