0

I'm constructing a query that joined a lot of tables. When I execute the query by select all columns, SQL Server throws an error

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

When I replace the * with a few columns, it works fine. So I assume the problem is due to the result has exceeded the allowable size per row.

Now I'm wondering will it be ok to construct such a stored procedure and implement it in my WPF application?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SuicideSheep
  • 5,260
  • 19
  • 64
  • 117
  • What database are using MySQL or MS SQL Server? Can you edit your tags accordingly? – Leo Jan 06 '14 at 09:00
  • It sounds unlikely to me that this scenario would present as a "specified network name is no longer available" exception - are you sure that isn't coincidental? Also: since `*` can only **increase** the number of columns returned, it also seems unlikely to me that this would fix it. Based on the initial report, I don't think the error is what you think it is. – Marc Gravell Jan 06 '14 at 09:01
  • This could potentially be a connection pool issue? Did you by any chance restart your webserver when you specified the column names? Try restarting and run your * query again. The one in your task bar (cassini) or IIS, whichever your app uses. – Paul Zahra Jan 06 '14 at 09:01
  • I was executing the same query to test out. `Select * from blabla inner join blabla.....inner join blabla.......` will throw me the error I stated. While `Select id from blabla inner join blabla.....inner join blabla......` is fine. The hitting error rate was 100% – SuicideSheep Jan 06 '14 at 09:05
  • @IsaacLem are you running those two tests in the same app instance? without restarting (for example via two test buttons in the same app). If you restart between tests, the test is meaningless. – Marc Gravell Jan 06 '14 at 09:13
  • Seems like you are having a timeout problem. Have you seen this [post](http://social.msdn.microsoft.com/Forums/en-US/bd07b354-e142-4788-9cf9-15eebbb439cd/a-transportlevel-error-has-occurred-when-receiving-results-from-the-server)? – c0deMonk3y Jan 06 '14 at 09:04
  • Should be a comment, http://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers – Philip Gullick Jan 06 '14 at 09:06
  • Thanks that was what i initially tried to do. However, i do not have privilages to write comments to the question :( – c0deMonk3y Jan 06 '14 at 09:10
  • @c0deMonk3y: The error appear after 6-10 secs of execution time. And the number of rows is only 60k. Hence I don't think is timeout problem – SuicideSheep Jan 06 '14 at 09:12
  • Ok, then I would advise at the very least to gather the useful information out of that extract and use it in your answer, whilst still referring to the link as reference. This is just so if/when the link dies, the answer is still useful. – Philip Gullick Jan 06 '14 at 09:12
  • @MarcGravell: The test was running on `SQL Server Management Studio 2008 R2` same query editor – SuicideSheep Jan 06 '14 at 09:17
  • The point is, when your * query fails, do you restart your app server and run your test with columns specified? – Paul Zahra Jan 06 '14 at 09:18
  • No I didn't restart any app server. Basically I executed both query in the same query editor at the same time. – SuicideSheep Jan 06 '14 at 09:23
  • @IsaacLem are you using linked servers or anything like that? Also: if this is SSMS, why tag WPF / C#? That suggests technologies that *don't apply*... – Marc Gravell Jan 06 '14 at 09:25
  • My question was, `Now I'm wondering will it be ok to construct such a stored procedure and implement it in my WPF application?` Since I suspect is because of the size issue, I was thinking maybe WPF can handle bigger size which means it's fine to have such query eventhou SSMS is throwing size error? – SuicideSheep Jan 06 '14 at 09:28
  • Even if you run it in WPF the connection is .NET and that would be a more appropriate tag. And I don't think you can conclude it is a size thing. Specified network name is not available sounds more like a name resolution. And a * on a join is just plain sloppy - you get every join column twice. Try a top 1. – paparazzo Jan 06 '14 at 16:07
  • @Blam: Top 1 is fine, but that can't conclude anything because the first row might contain a smaller size of data. Ok let's put it this way, since top 1 is fine, meaning column size has no issue. And Select one column is fine, meaning number of row has no problem. Then what can be the possible root cause? – SuicideSheep Jan 07 '14 at 06:30

1 Answers1

0

Seems like you are having a timeout problem. Have you seen this post?

In the link i added someone is claiming that the solution was to increase the "connect timeout" (Probably the select statement is taking too long).

If this does not solve your problem then you can try diagnosing it by:

1) Assume you were making remote connection, ping , telnet , or net view \ or see firewall setting on the remote server to check whether the network is still good to make sure remote server is still reachable, and contact your network administrator to fix those problems.

2) You can give a retry by running your client app see whether the problem went away.

3) If 1) and 2) passed, you might open sql profile to nail down which client operation to cause sql server terminate connection, and check server errorlog or application event log find out any clue.

If you were making local connection, it is probably reason 3).

Note: this is copied from the attached link.

Regarding whether you shold use a Stored Procedure check this post.

Community
  • 1
  • 1
c0deMonk3y
  • 486
  • 3
  • 7
  • Should be a comment, http://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers – Philip Gullick Jan 06 '14 at 09:06
  • Thanks that was what i initially tried to do. However, i do not have privilages to write comments to the question :( – c0deMonk3y Jan 06 '14 at 09:10
  • @c0deMonk3y: The error appear after 6-10 secs of execution time. And the number of rows is only 60k. Hence I don't think is timeout problem – SuicideSheep Jan 06 '14 at 09:12
  • Ok, then I would advise at the very least to gather the useful information out of that extract and use it in your answer, whilst still referring to the link as reference. This is just so if/when the link dies, the answer is still useful. – Philip Gullick Jan 06 '14 at 09:12