May 30, 2002 at 4:12 pm
I'm making the big plunge from a reader to asking for help here.
I have a customer that has SQL Server 7 and our vertical market software. He is operating fine locally, but wants me to access his data remotely for some changes and troubleshooting unrelated to the current problem.
The problem is this: Using Enterprise Manager, I can access his server as long as I don't ask for much data. I can get a list of databases, open the list of users or roles, even edit a user. But if I try to pull up the list of tables or views, I get a "Connection Read (recv())" error at my timeout point (60 seconds). I can also use our application to access his data remotely - as long as I don't ask for much data. I can use Query Analyzer and pull one or two rows of data, but not 5. In short, it seems that any query that would take over an instant to answer times out.
I have SysAdmin permissions on this customer's database.
I have tried to access his server from two machines in our office, no differences. I have accessed other customer's machines this afternoon to make sure my machines aren't screwed up, they are working fine. We've rebooted his firewall, DSL Modem, and server. From another article on here, I had the customer remove and reinstall TCP in Client Network Utility, no difference.
Yes, I'm lost.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
May 30, 2002 at 7:59 pm
Perhaps the connection is not reliable? Run tracert a few times to see how it looks? Also maybe check the router/switch statistics.
Andy
May 31, 2002 at 5:48 am
Thanks Andy. I had already used ODBCPING to verify that we were getting through his firewall to the server (although I knew I was since I could access the stuff I mentioned). I get a nice quick response mentioning SQL Server V 7.00.961 running on Windows NT 5.0 Build 2195 SP 2. I tried TRACERT and I'm getting normal looking times through all the hops (21 hops). Normal looking as in very similar to other customers that work just fine (20 to 110ms).
I did not mention initially that I've never had to access this customer before. Could there be a setting of some kind in Win 2000 or SQL Server 7 that limits a remote user's quantity of information pulls???? Sounds ridiculous I know. I have tried at random intervals late last night and early this morning. The results are very much the same each time. I don't believe Internet traffic type issues are the answer.
Hoping for insight from the Gurus on this board,
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
May 31, 2002 at 6:20 am
It's possible to limit bandwidth using a quality of service policy, never heard of one that actually counted the rows - would be interesting! Not impossible I'd say.
Andy
May 31, 2002 at 7:39 am
It's not just rowcount though. It's more like a certain byte count of data. I can pull about 5 rows on a small table (Select * from Defaults where SerNum between 311 and 314) but I can pull a lot more rows when I return less columns (Select SerNum, ChangeNum from Defaults where SerNum between 311 and 340) (This leaves out a varchar(255) field). It really seems to be the quantity of bytes returned that kills me.
P.S. In Query Analyzer, the message is not Read (Recv()), it's "General Network Error. Check your network documentation. Connection Broken" I just love that specific advice - especially "Check your network documentation".
Student of SQL and Golf, Master of Neither
Edited by - BobatDBS on 05/31/2002 07:44:00 AM
Student of SQL and Golf, Master of Neither
May 31, 2002 at 8:52 am
Sounds like an unreliable network connection. Can you make a larger file transfer (>1MB) and see what happens?
Steve Jones
May 31, 2002 at 8:59 am
Anything over a few hundred bytes fails using Enterprise Manager, Query Analyzer, and our software. The client does not have FTP or web services. Did you have a method of transfering a larger pile of data in mind?
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
May 31, 2002 at 9:31 am
Try using ping like so
ping 0.0.0.0 -t -l 1400
ANd see how many failures you get, if a lot then tracert to the server and see if you can find what may be the problem. The are tools to check this further but I cannot think of the names offhand. Remember replace 0.0.0.0 with the ip of the remote machine. Also you may need to make 1400 smaller depending on the link you have, but if too small then there is a problem.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 11:05 am
Thanks for the Ping idea, here are the results:
Like most folks, customer has "Discard Pings" on his router. Helped him find and disable that. Ran the Ping test as you suggested. At 1400, never lost one, average round trip time was 196ms. Tried other values like 1450 and 1500 (I have no idea what should work). 1450 lost one, 1500 lost all. Every size 1400 and smaller never failed.
Checked that my symptoms had not changed after allowing pings, all still works the same way. I can retrieve a little data, but not a lot. I'd swear it's in his SQL or Server configuration, this same client can connect to our SQL Server flawlessly. I'd blame the firewall, but why does it let me connect at all?
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
May 31, 2002 at 11:26 am
Have the customer launch Profiler and trace your acitivty to see if anything unusuall is going on, this seems like you are being disconnected from their end but I cannot think of anything right off that might be the cause.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 11:40 am
I appreciate the suggestion about Profiler, but the customer is not that savvy. What in particular would I be asking him to look for?
Any other ideas? I'm getting the very uneasy "Plane Ticket coming" feeling.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
June 3, 2002 at 6:11 pm
I don't have profiler here in front of me. But I would audit logic and logouts (connect, existing connection, disconnect with 7), error messages, and sql stmt begining and completed. These should give you a ruff idea of what is happening in the SQL connection and if something odd is happening with direct regard to the query.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/03/2002 6:11:57 PM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply