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 |
|
matt_the_daddy
Starting Member
4 Posts |
 Posted - 2007-03-29 : 07:41:54
|
| Hi there, have a fasthosts SQL server database, which has been fine until todayBasically I have a VB application that goes throuth the data and selectively creates CSVs based on what's in the tables. Today it didn't work, turns out that I cannot view data in the database using enterprise manager, I can see the tables, views, stored procedures, but just cannot view table rows at all and I get timeout expired errors.This does not happen in query analyzer at all, it takes a short time around 40 seconds but all the data is there and I can see table contents fine.This is made even stranger by the fact that I can do a select count(*) query on any table and it works correctly. I have tried this on 3 machines now and all have the same problemHelp, I've spent 4 hours on this now and it's time I can't afford to spend on it.I have added my msn address matt_the_daddy@ntlworld.com for anyone who may be able to help |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-03-29 : 08:28:02
|
| Sounds like blocking. Use sp_lock or sp_who2 to see what's being locked. Also, what exactly is your app doing? Sounds like something that could be done more efficiently (i.e. using BCP).Mark |
 |
|
|
matt_the_daddy
Starting Member
4 Posts |
Posted - 2007-03-29 : 09:03:50
|
| I've never used sp_lock or sp_who2 before, so will have to bear with me.the app is used to extract data from tables matching parameters given from an external source.can you define blocking for me? |
|
|
|
matt_the_daddy
Starting Member
4 Posts |
Posted - 2007-03-29 : 09:17:19
|
| OK found the stored procedure in the master database on the server, how do I find this SPID to use as a parameter |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-03-29 : 13:37:41
|
| run sp_who2 active in query analyzer. Look through the BlockedBy column. If its anything but a 0, then that spid is blocking the spid in that row. You can do a DBCC INPUTBUFFER(@spid) to see what the blocking spid was doing at that time. You have to be pretty quick as the spids change quickly (depending on how you release the connection to the database).************************Life is short. Enjoy it.************************ |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-03-29 : 13:49:11
|
also, take mwjdavidson's advice and use bcp for this. with the queryout option you can generate a csv from any result set you like, and it's guaranteed to be faster than any VB app.EDIT: here's a link for ya: http://msdn2.microsoft.com/en-us/library/ms162802.aspx
www.elsasoft.org |
|
|
|
matt_the_daddy
Starting Member
4 Posts |
Posted - 2007-03-30 : 01:56:44
|
| OK, tried this from my home PC last night and can access all tables fine just like I could from the office. So any ideas why 4 PCs in an office are unable to view table contents but everyone else can? |
|
|
|
|
|
|
|
|
AMAZINGLY STRANGE PROBLEMS