OPENQUERY end-of-file error

I am trying to shorten an query string I am using in an OPENQUERY, to get it less than 4k. In order to do that, I have tried to put some repeating logic into a subquery factoring clause (starting a subquery with a WITH clause). I cannot post the exact query as it has some business sensitive information, but the basic structure is

SELECT * FROM OPENQUERY( server, '

SELECT

*

FROM

(

WITH a AS

(

SELECT

a,

b,

c

FROM

table1

)

SELECT

x,

y,

z

FROM

a a1

INNER JOIN

table2 t2

ON a1.a = t2.a

)

')

When I do this, I keeping getting an error from the OLE DB provider saying 'End-of-file on communication channel'. This problem only seems to occur when I put a WITH clause in my query. Has anyone else ever had a similar problem, and has anyone found a way to deal with the problem?

Have you tried to execute the statement directly in osql, sqlcmd, or Sql Management Studio? It might be that the with clause is not terminated correctly. It could just be a syntax error, and the message ends before the server expects to see it end.

I would also suggest that, if you are sending very long batch queries, you might get more performance out of creating stored procedures on the server and calling those from the client. You will send less data per query and it only costs a one-time setup step that can be written into a batch file and run at setup time. That would likely give a better effect than the one you are trying to reach through refactoring without requiring the refactoring step.

Hope that helps,

John

|||Is this an Oracle provider you use? Is it by any chance ORA-03113 error you are getting?

|||

Yes, it is an Oracle provider, and yes, the error is an ORA-03113 error.

|||

Did this link offer you any help?

http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm

I just searched for this error and found a bevy of information online. Has that stuff not helped you yet? What is unique about your scenario that isn't covered by the online documentation on this error? If you can specify that more accurately, we can avoid going through the process of offering up suggestions you have already seen and tried.

Thanks,

John