Table Query Timeout Problem very specific

msmith

MSSQL Server 2000 SP3 in both houston and memphis

I have a database in houston, lets call it RED. Specific tables from
database RED are copied to database BLUE. Database BLUE is then backed
up, ftp'd to memphis and restored. In memphis there is a single table
in this database that will not open in enterprise manager when you
choose to open all rows you get a generic ODBC timeout error no numbers
just simply "TIMEOUT".

If I log into the servers in houston and open the table from database
RED or BLUE there is no issue. I can however return up to 66,199 rows
without an error. If i choose a number higher than this i get the
timeout error. I discovered I could run a query

Select Distinct * from f0911

and it would display all my data. I am able to export the data from
the table using this query to a csv file and reimport the data into a
table and it works just fine.

I built another sql 2000 server SP4 and there is no issue with the
table in the database when it is restored there. I have also tried
restoring as a different database name on the production (sql 2000 sp3)
server to no avail.

I can't figure out why this single table is not functioning properly in
the one instance of sql 2000.

If anyone has any ideas please share them, I'm running out myself. I'm
obviously very new at sql database administration and would appreciate
any advice.
Also, i don't believe the issue has to do with timeout countdowns.
Everywhere i could change them (Enterprise manager and SQL Server
itself) they are set to unlimited if possible. I also don't see how
the problem could be related to the service pack of the sql server,
seeing as although it works on the sp4 server in my possession it still
works just fine on the sp3 server in houston.

Thanks,

Michael Smith

Aug 23 '06 #1

Subscribe Reply

3478

Erland Sommarskog

(ms****@cryptic edge.net) writes:

I have a database in houston, lets call it RED. Specific tables from
database RED are copied to database BLUE. Database BLUE is then backed
up, ftp'd to memphis and restored. In memphis there is a single table
in this database that will not open in enterprise manager when you
choose to open all rows you get a generic ODBC timeout error no numbers
just simply "TIMEOUT".

How many rows are there in this table? What happens if you run a
SELECT * on this table in Query Analyzer?

My guess would be that there is a blocking issue. You can examine this
by starting the query and while waiting for the results, run sp_who or
sp_who from a query window. Keep an eye on the Blk column. If this
column has a non-zero value, it means that the spid in the Blk is
blocking the spid on this row. (spid = server process.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 26 '06 #2

Similar topics

1541

i need help, innodb blocks insertion into table for a while

by: Bernhard Schmidt | last post by:

------=_NextPart_000_0030_01C34C51.B8F4D1A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hi dear mysql list members =20 i have setup a mysql database 4.013 with innodb enabled. when i set the =

MySQL Database

23626

Large table/slow query/ can performance be improved?

by: Robert | last post by:

I am having performance issues on a SQL query in Access. My query is accessing and joining several tables (one very large one). The tables are linked ODBC. The client submits the query to the server, separated by several states. It appears the query is retrieving gigs of data from the table and processing the joins on the client. Is there away to perform more of the work on the server there by minimizing the amount of extraneous table...

Microsoft SQL Server

5200

setting the query time out....

by: Manuel | last post by:

i am using sqlserver 2000, and i was wondering how do i go about setting the query time out. is there a way to configure the query timeout for a specific user id?

Microsoft SQL Server

5637

Using "SELECT * " is a bad practice even when using a VIEW instead of a table?

by: serge | last post by:

Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I create a single View and specify also in this View the WHERE clause that is common in these stored procedures, I will have the new stored procecures changed to be like:

Microsoft SQL Server

2558

Different query plans

by: traceable1 | last post by:

I have 2 SQL databases which are the same and are giving me different query plans. select s.* from hlresults h inner join specimens s on s.specimen_tk = h.specimen_tk where s.site_tk = 9 and s.location in ('ABC','WIAD') and s.date_collected between '2/1/2003' and '2/3/2006' order by s.location, s.date_collected

Microsoft SQL Server

8288

Check Linked Table Connection

by: Fred Zuckerman | last post by:

I have a A2K database that includes some linked tables (these links are SQL views). Sometimes there are "issues" with the SQL server and the users receive a timeout error whenever they try to access those records. The timeout error occurs after 60 seconds. Many times the user has given up and used ctrl-alt-del to end task thinking their computer has locked up. I suppose I could change the default timeout from 60 seconds down to 30...

Microsoft Access / VBA

1690

Regrouping data on a table (from a table of all transactions to a summary by customer)

by: myemail.an | last post by:

I have a database with information on customers and their transactions. The table with transactions has the following fields: Customer ID , Transaction ID, Transaction Type, Transaction Amount, Transaction Date Each customer may have up to 6 transactions. I'd need to create a table where each record is a customer and which has these fields: Customer ID , 1st Transaction - Transaction Type, 1st Transaction -

Microsoft Access / VBA

1672

How to delete a table with about 2000 rows

by: vanc | last post by:

I'm trying to delete a table with just around 2000 rows. What I got is timeout error. I can't use Truncate Table, which is very quick, because I have Trigger with Delete command. Is there any way to increase timeout "time" in sql server to let my query finish? Or there is better way to do this. Any comment will help. My query is just this Delete From aTable

Microsoft SQL Server

1791

Problem of UNION ALL view & Update of master table row in Paralel Execution

by: oskhan | last post by:

Hello Everyone, I have a little different problem and I though anyone might give me any idea that what is going wrong. I have a view which consists of 3 tables linked by UNION ALL, overall all the three tables contains around 50 million, 2 million & 3 million respectively. Here is the view looks like: Create view test as Select * from tab1 // contains 50 million rows Union all Select * from tab2 // contains 50 million rows

DB2 Database

9568

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9959

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9837

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7381

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6651

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5279

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5423

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3532

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2806

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General