Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-10 : 09:29:06
|
| Adam writes "Hello,I hope you can help me with my query. It's a bit confusing. I'll include as many details as possible, but I'll bet it's something simple.We have a recently rebuilt Win2KSP2 server that only hosts MS SQL Server 7SP3. Lately client applications (written by ourselves) have been reporting SQL Command Timeouts intermittantly, but regularly. These timeouts occur even when the client being timed out is the only client to the SQL server at that time.Here are the SQL statements that make up the command in which this timeout occurs, which always (if the timeout does not occur) runs in under 3 seconds.if exists (select * from sysobjects where id = object_id(N'[dbo].[tempbatch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tempbatch]if exists (select * from sysobjects where id = object_id(N'[dbo].[tempdoc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tempdoc]select * into tempbatch from batch where weeknum = xxselect count(*) as NumDocs, document.BatchNameinto tempdoc from document inner join tempbatch ontempbatch.batchname = document.batchnamegroup by document.BatchNameSo it just checks if two temporary tables exist - if they do it drops them, and then creates them with two "select into" statements. The first select statement returns around 300 records from around 12,000 records. The second generally returns the same no of records as the first. The 'document' table has around 1 million records.The server is relatively fast, has 512MB of memory, with plenty of hard drive space and is on a fast network.The client application in question is written using VB6 using MS ADODB2.6. A new connection is created for each of the commands issued, but it is not a connection timeout that occurs.Once a timeout occurs, these commands will continue to timeout until the SQL Server Service (mssqlserver) is restarted, when everything starts to work again.There are other commands regularly being issued via other connections on this server, but the timeouts only occur in this particular application with these particular commands.I hope you can help.Thanks a million,Adam Byrne" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-10 : 09:30:45
|
| My first guess is that in rebuilding the Win2K server, an older version of MDAC was installed (2.6 was not available when Win2K was released). Unless you specifically installed MDAC 2.6 on the server afterwards, then that is most likely the problem. You can get the MDAC drivers here:http://www.microsoft.com/data/download.htm |
 |
|
|
|
|
|
|
|
SQL Server Command Timeouts - hard question?