transport-level error when specifying SELECT *

Question

The following query does not work, and produces an error:

A transport-level error has occurred when receiving results from the server

SELECT 
    * 
FROM 
    table1 a, 
    table2 b, 
    table3 c 
WHERE 
    a.location = b.location AND 
    b.location = c.location AND 
    a.id = c.id AND 
    a.entry = ''34690''

Although this query works:

SELECT 
    a.location, 
    a.id, 
    a.entry, 
    c.desc, 
    b.name 
FROM 
    table1 a, 
    table2 b, 
    table3 c 
WHERE 
    a.location = b.location AND 
    b.location = c.location AND 
    a.id = c.id AND 
    a.entry = ''34690''

I need to select pretty much everything from all tables (about 100 items), but do I really need to specify each column that I need in the resultset?

Answer 1

The error you receive is all about network connectivity, rather than the statement being executed.

Please update the question with the FULL error message. It'll help track down the root of the problem.

Can you confirm that the server wasn't down at the time? Is the error predictable 100% of the time when using the SELECT * syntax?

Answer 2

Even though it's generally a good practice to avoid using the * qualifier when writing client SELECT queries, there could be a few reason why it might not be working:

• the SQL server product you are using, or the driver you are using does not support retrieving that many (100) columns in a single query.
• the total size of all the columns you are trying to get back is larger than the maximum supported (most likely by the driver) - this could occur if you some of the columns are of large data types and contain a lot of data such as many VARCHAR(MAX) or TEXT columns
• or there may be actually be a poor network connection between your server and the client - you should verify this one by executing the query locally on the server.

EDIT

OK, so the problem seems to be that one of the columns contains text amounting to about 1000 characters. Any way around that one??

Have you tried running a query to return just the one column that causes the problem? If you have and that worked by itself but causes the full query to fail you could try to run two queries. One to get the rest of the columns, and one to get the remaining one column. Then in your client, re-assemble the information.. If you do go this route, you will have to make sure you include whatever unique identifier you are using to identify each record in both queries so that you can map back the long-text-column to the rest of the information for each record.

Answer 3

Try below query

SELECT a. * b. * c.* FROM table1 a, table2 b, table3 c WHERE a.location = b.location AND b.location = c.location AND a.id = c.id AND a.entry = '34690'

Answer 4

Yes you should always specify the columns you want in production code (it's ok in ad hoc queries run once). To fail to do so is a SQL antipattern. In your case you have joins, which means at a minumum the join fields are repeated which is a waste of server and database resources. It isn't hard to specify the fields, you can drag all the columns over from the object browser, so there is no excuse to not do so. You can break alot of things and make maintenance much worse using select *. Do not ever use it in production code.

Incidentally you should not use implicit joins. They are subject to accidental cross joins, they are harder to maintain (espcially when you need to add a left join later), they DO NOT work correctly if you use the syntax for left or right joins (which is deprecated too) and they were replaced 18 years aog with a better syntax.

I would expect SQL Server to be able to send 100 fields with one larger than 1000 characters without breaking a sweat. Not sure what is causing the transport error but the error message usually happens when the connection is cut to the database.