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. 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, so, where is that timeout setting hidden? regards arno
arno, I don't know why OLE DB and ODBC have different timeout tolerance for you. However, the 30 seconds is not a SQL Server setting, but it is a client ADO setting. You can direct ADO to use a different timeout period such as: 0 = never timeout 600 = timeout after 10 minutes From: http://www.davidj.org/docs/ado_iis_timeout.html dim myConn Set myConn = Server.CreateObject("ADODB.Connection") myConn.CommandTimeout = 0 myConn.Open "Provider=MSDASQL;Driver={SQL Server};Server=server;Database=database;UID=uid;PWD=pwd;" RLF "arno" <nospam@invalid> wrote in message news:uWKQ51TXIHA.5880@TK2MSFTNGP04.phx.gbl... > 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. 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, so, where is that timeout setting hidden? > > regards > > arno > > >
Hi Russel, >Set myConn = Server.CreateObject("ADODB.Connection") > myConn.CommandTimeout = 0 that's it! It is a bit confusing that commandtimeout is related to a command opject but used on a connection object. regards arno
arno, Yes, you are absolutely not the first one to be confused. Think of it as setting the timeout for all commands on this connection. RLF "arno" <nospam@invalid> wrote in message news:Ojpfe3lXIHA.4828@TK2MSFTNGP05.phx.gbl... > Hi Russel, > >>Set myConn = Server.CreateObject("ADODB.Connection") >> myConn.CommandTimeout = 0 > > that's it! > > It is a bit confusing that commandtimeout is related to a command opject > but used on a connection object. > > regards > > arno >
