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 - 2001-12-09 : 12:37:55
|
| isaac writes "Our company has migrated the production server from SQL 7 running on WinNT to SQL 2000 on W2K with database compatibility is set to 7.0. The users are connection to the server using desktop application (in-house development using VB), and generally satisfied wth the speed compared to the old server. The problem occurs when a client is creating report. while the app getting the next record (movenext) the connection is suddenly disconnected by the server, resulting error connection(recv()) 0x80004005. Sometimes the report is finished successfully. The reporting process is by executing a stored procedure/query returning recordset which is then fetched into MS Excel worksheet. We have looked into the server's log, SQL's log, but we do not find any suspect events recorded.We also install other server with SQL2000/W2K and restore the production DB, and the problem occurs. When we run the Profiler, there is no error ocurred before disconnection, and the disconnection event itself just like when a client disconnect normally.We have created a small app that mimics the reporting process, and come into conclusion that when while reading the record and there is a process in client side (i.e. fetch into Excel worksheet) that takes a little longer than expected, the connection is disconnected.FYI, the cursor is put at the server side. When we are using client side it runs perfectly but burdening the client resources. The report app runs perfectly when using SQL7/WinNTWe need a solution or any hints because there is no turning back ... Thank's in advance" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-09 : 13:18:13
|
| How is Excel pulling the data? If you are using a loop (Do While not rs.EOF...rs.MoveNext...Loop), have you considered using GetRows or Getstring, or the CopyFromRecordset method? These are highly optimized and very fast for retrieving data from an ADO recordset. The will retrieve the data faster than a loop, and allow you to do it in one operation. You can then close the connection and process the data at your leisure.www.learnasp.com and www.asp101.com have excellent articles on GetRows and GetString. You can find information on CopyFromRecordset in the Excel VB help file. Even if you need to do elaborate formatting, you are better off grabbing the data as quickly as possible and then processing it later.Edited by - robvolk on 12/09/2001 13:18:40 |
 |
|
|
|
|
|
|
|
error on generating report after migration