VB & Database, Timeout expired, [Microsoft OLE DB Provider for SQL Server]

Timeout expired, [Microsoft OLE DB Provider for SQL Server]

Author	Message
Abraha #1 / 5	Timeout expired, [Microsoft OLE DB Provider for SQL Server] Hi, I'm getting below error when sending the query -- select * from #Tab where id in (3,4,....) -- from VB code. ( #Tab is a temp table ) This happens only when there are so many records in the in clause . This error comes after 10-15 minutes , On that 10-15 minutes SQL Server is very slow /not accessable even through Query Analyser. How can I avoid this . Timeout expired, [Microsoft OLE DB Provider for SQL Server] Error # -2147217871 [HEX:80040E31] Thanx
Sat, 26 Nov 2005 02:30:33 GMT

Erland Sommarsko #2 / 5	Timeout expired, [Microsoft OLE DB Provider for SQL Server] [posted and mailed, please reply in news] Quote: > I'm getting below error when sending the query -- select * from #Tab > where id in (3,4,....) -- from VB code. > ( #Tab is a temp table ) > This happens only when there are so many records in the in clause . > This error comes after 10-15 minutes , On that 10-15 minutes SQL Server is > very slow /not accessable even through Query Analyser. > How can I avoid this . > Timeout expired, [Microsoft OLE DB Provider for SQL Server] Error > # -2147217871 [HEX:80040E31] SELECT * FROM IN (...) with very many values in the IN clause can be extremely slow. It appears that it takes very long time for SQL Server to build the query plan, since if exactly the same queries is issued a second time, response time is very good. However, I don't really see why SQL Server should be completely inaccessible during this time. And you would need to have something like three or four thousand list elements for execution time to exceed the default timeout of 30 seconds. There are other, even more ineffecient list-finding methods, which indeed can clog up the server severly. If you have that many list elements, you should look at some more effecient method. I have a lengthy article about this on http://www.algonet.se/~sommar/arrays-in-sql.html. What I present as the iterative method in the article, should be good enough for you. -- Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Sat, 26 Nov 2005 06:17:44 GMT

Vinod #3 / 5	Timeout expired, [Microsoft OLE DB Provider for SQL Server] Quote: > However, I don't really see why SQL Server should be completely inaccessible > during this time. Erland when we do a Select with an INTO clause the TempDB is locked for the preiod of creation of the #table ... On the contrary when we use a Create table #table and then do a Insert into then the tempDB is locked for the period of creation of the table only ... Hence any operation that requires TempDB's service becomes inaccessible during this period of time ... Correct me if Iam wrong here ... I do accept on the other points discussed by you ... -- HTH, Vinod Kumar MCSE, DBA, MCAD SCT Software Solutions Quote: > [posted and mailed, please reply in news] > > I'm getting below error when sending the query -- select * from #Tab > > where id in (3,4,....) -- from VB code. > > ( #Tab is a temp table ) > > This happens only when there are so many records in the in clause . > > This error comes after 10-15 minutes , On that 10-15 minutes SQL Server is > > very slow /not accessable even through Query Analyser. > > How can I avoid this . > > Timeout expired, [Microsoft OLE DB Provider for SQL Server] Error > > # -2147217871 [HEX:80040E31] > SELECT * FROM IN (...) with very many values in the IN clause can be > extremely slow. It appears that it takes very long time for SQL Server > to build the query plan, since if exactly the same queries is issued a > second time, response time is very good. > However, I don't really see why SQL Server should be completely inaccessible > during this time. And you would need to have something like three or four > thousand list elements for execution time to exceed the default timeout of > 30 seconds. > There are other, even more ineffecient list-finding methods, which indeed > can clog up the server severly. > If you have that many list elements, you should look at some more > effecient method. I have a lengthy article about this on > http://www.algonet.se/~sommar/arrays-in-sql.html. What I present > as the iterative method in the article, should be good enough for you. > -- > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Sun, 27 Nov 2005 11:18:16 GMT

Erland Sommarsko #4 / 5	Timeout expired, [Microsoft OLE DB Provider for SQL Server] Quote: > Erland when we do a Select with an INTO clause the TempDB is locked for > the preiod of creation of the #table ... On the contrary when we use a > Create table #table and then do a Insert into then the tempDB is locked > for the period of creation of the table only ... Hence any operation > that requires TempDB's service becomes inaccessible during this period > of time ... Correct me if Iam wrong here ... Are you running SQL Server 6.5? The symptom you describe is well-known for 6.5. The reason is that as long as the SELECT INTO statement runs, it holds locks in sysobjects and syscolumns. On 6.5 which only has page locks this effectively prevents other processes from creating temp tables, which may seem that the server is completely inaccessible. You cannot connect from Enterprise Manager because it creates temp tables. ISQL/w will do, though. On SQL7 and SQL2000, the difference is that since the locks are row locks, other processes can still create temp tables, and the problem is usually not that severe. There is a trace flag for 6.5 which changes the behaviour, so that when the flag is in effect, the locks are released once the table has been created, so if the query is long-running, other processes are not affected. Unfortunately, I don't remember the number of the trace flag, and it is not in Books Online, because it was added in SP1 or SP2 to SQL 6.5. -- Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Sun, 27 Nov 2005 15:39:27 GMT

SQL Gu #5 / 5	Timeout expired, [Microsoft OLE DB Provider for SQL Server] How many rows in your #Tab? Maybe you shouldn't use temp table. Or if you believe temp table is the best way, add an index in id column. All your descriptions point to one thing: your query makes SQL too busy to do a large table scan. Gary Quote: > Hi, > I'm getting below error when sending the query -- select * from #Tab where > id in (3,4,....) -- from VB code. > ( #Tab is a temp table ) > This happens only when there are so many records in the in clause . > This error comes after 10-15 minutes , On that 10-15 minutes SQL Server is > very slow /not accessable even through Query Analyser. > How can I avoid this . > Timeout expired, [Microsoft OLE DB Provider for SQL Server] Error > # -2147217871 [HEX:80040E31] > Thanx
Mon, 28 Nov 2005 16:17:35 GMT

Page 1 of 1

[ 5 post ]