Receiving error with UNION query - Unexpected EOF in SQL 1

[rseaton]

I have a union query which combines six select queries. Unfortunately, changing the DB structure is not an option. The query has worked fine with five select queries. The error occurs when adding the sixth query. To verify that there no problem with the sixth query, I replaced the fifth query with the sixth. Runs fine. There seems to be limit when placing the sixth select statement.<br>
<br>
The exact error is:<br>
DB-Library: Unexpected EOF from SQL Server. General network error. Check your documentation.<br>
Net-Library error 10054: ConnectionCheckForData ((null)()).<br>
<br>
DB-Library Process Dead - Connection Broken<br>
<br>
NOTE: The error message text and number do not match. Upon further research, the error number is actually 10037.<br>
<br>
Any ideas?<br>
Thanks.<br>

 

[richardmorrison]

EOF - is end of file. Sounds like you have run out of space somewhere.
Could be a software error as the link to database library has been broken.
general network error seems like a software/hardware error?????

 

[tkav8]

I am getting this same error. When I reduce the number of unioned views to five the procedure runs fine. If anyone has any information it would be greatly appreciated.

 

[bperry]

What version of SQL are you on?

In 6.5, there was a limit of 16 tables in a Select. I have heard that it was raised to 32 in 7.0, but am unsure about that.

I am wondering if each of the 6 individual queries are complex queries that themselves refer to a few or several tables. If so, would they all count toward the limit when you add them all together? Perhaps so.

If that in fact is the issue, then (a) simply the queries or drop out some tables, or (b) change the processing around so that you use a temporary table(s) to do part of the processing, then continue with the remainder by selecting from the temp table(s).

 

[tkav8]

I am running SQL Server 2000. If the total number of tables can cause a problem then this might very well be the case. When combining all of the views in the stored procedures using unions, the total number of tables would most definitely be over 16 in my case.
I will attempt to use the temp tables and see if that will help.
Thanks for the response.

 

[tlbroadbent]

One thing to keep in mind when using UNION. SQL Server will sort the data nad elimnate duplicates. If the values from each select statment are unique, add ALL to the Union statment. This will inhibit the sorting and duplicate elimination. The extra time and resources required for these operations may cause the problems you are experiencing.

Select * From tableA
Union All
Select * From tableB
Union All
.
.
.

Another option you might consider if ALL isn't appropriate for your query is to group the unions with parentheses to allow SQL Server to perform the Union in steps. I'm not sure if thsi will make any difference but it may help.

Select * From
(Select * From tableA
Union
Select * From tableB
Union
Select * From tableC) u1
Union
Select * From
(Select * From tableD
Union
Select * From tableE
Union
Select * From tableF) u2
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[tkav8]

Thanks for the replies.
Terry,
Your suggestion to break up the unions into steps worked perfectly. I appreciate the help. After searching at length for an answer somewhere, you have finally given me the solution. You've made my day!

Trey

 

[bperry]

That's a good trick to remember!

It is strange why it even works. I would call that a bug or deficiency in the query optimizer: it should recognize that it's the same query, with or without the parentheses.