February 2, 2009 at 9:41 am
Hi all,
hoping someone can shed a little fresh light on a problem I have been scratching my head over for hours!
From my machine running SSMS (SQL 2005 SP2) I submit a trivial query to the db server (SQL 2005 Std SP2)
select * from mytable where id = 17514
I get 40 rows returned in just under 1 sec.
id is not the PK but a FK to another table - it is indexed
Mytable has 500,000 rows
Then I try
select * from mytable where id = 17515
subtle difference, next id up, but I know from the data that it should return same sort of result set, 40 rows.
after several minutes I get about 15 lines of data and
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Remote desktop onto the server, start SSMS and run the same 2 queries - both run fine and return 40 rows each in under 1 sec.
Check the execution plans, both the same.
Start profiler on the server and trace the 2 calls from my machine - both complete without error, but still the second one refuses to display all the results.
What could be different about the query or result set that could affect the network connection?
Any pointers most welcome.......
Kev
February 2, 2009 at 9:53 am
I don't think it's a problem with SQL Server. I think it's a problem with your network. Looks like something might be up with you DNS. I could be wrong, but that's what it looks like to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 9:58 am
I'll second what GSquared has said. Not a problem with the SQL Server itself, but somewhere on the network. Could be DNS or even, and I've seen this recently, bad network cable.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 2, 2009 at 10:00 am
Is there a VPN in between you and your sqlserver ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2009 at 10:14 am
If this happens every time you run with one id, but not the other, then I'd start to suspect something like client side anti-virus/IPS doing content inspection. Could be something in the second result set that triggers it to drop the connection.
February 2, 2009 at 10:17 am
Why/how would DNS affect this?
(not saying I don't believe you I just want to know more!!)
ALZDBA - The server is hosted elsewhere, but it's not accessed via a VPN - it's just accessed over the internet via TCP/IP
Kev
February 2, 2009 at 10:23 am
Todd,
it is happening everytime with one id and not with another - but I've checked anti-virus and that isn't kicking in.
Kev
February 2, 2009 at 10:36 am
I'm going to try it from home tonight to see if a different client (aside from the work network) has the same results and to see if the DB server is the common denominator.
I also have a maintenance window tonight, so a reboot certainly won't harm anything too! May not explain the issue, but might fix it! (fingers crossed)
Kev
February 2, 2009 at 1:54 pm
Something is KILLing your connection!
* Noel
February 2, 2009 at 2:53 pm
In the case where it's one parameter works and the other fails, and that's consistent, then it's gotta be something like the size of the result set. Or latency. If the query takes too long to run at the server, it's possible something is resetting the connection.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 7:23 pm
kevriley (2/2/2009)
Todd,it is happening everytime with one id and not with another - but I've checked anti-virus and that isn't kicking in.
Kev
I can't remember exactly what the phrase in the returned data was, but I've had this happen before. Turned out that one of the "switches" on the network was looking for something that the returned data just happened to return.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2009 at 9:44 pm
Certainly, the error is related to your connection.
The problem can be one of the followings:
1. The connection to the server is broken (disconnected)
2. The packets returned from the database were altered somehow
3. The packets is corrupt but TCP layer could not detect it ("Reliable" TCP is not really reliable since it only has a 16-bit checksum to determine if the packet was altered or not).
If you are having similar problem in your production environment, you can check out "DB-WAN Accel" from SpeedyDB, the product will enable you maintain reliable connections to your database servers through unreliable physical connections.
Charles Zhang
February 3, 2009 at 12:22 am
Are you fetching XML data to a grid ?
I've experienced SSMS with results to grid generating our client antivirus to interfere (not with every select, but every time with the same select(s)).
The AV generated a popup stating it intercepted an intrusion.
Switching to results in text was a work around.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 3, 2009 at 2:18 am
Jeff Moden (2/2/2009)
I can't remember exactly what the phrase in the returned data was, but I've had this happen before. Turned out that one of the "switches" on the network was looking for something that the returned data just happened to return.
Jeff, this certainly is what it 'feels' like
I tried from home last night and everything was fine!
Tried from another connection (our backup ADSL) at work this morning - and everything is fine!
So I'm certain now that I've eliminated the DB server and narrowed it down to the main internet connection - I've got our network guy checking the routers now.
Kev
February 3, 2009 at 2:20 am
ALZDBA (2/3/2009)
Are you fetching XML data to a grid ?I've experienced SSMS with results to grid generating our client antivirus to interfere (not with every select, but every time with the same select(s)).
The AV generated a popup stating it intercepted an intrusion.
Switching to results in text was a work around.
No its standard data - just some integers and some decimals.
I know the issue you are describing - I've had it before when selecting XML to Grid - my McAfee pops up to stop it - I have to run to text too to get the results.
Kev
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply