With clause does not work linked server

Question

Hi all,

I've been connecting to an oracle 10g server using ms sql servers linked server feature and for some reason i am unable to use the with clause. Does this clause only work in in the pl/sql console or is there something i can do to make it work with linked servers as well?

The error i am getting is

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "

Edit: Just thought i'd add that i'm using openquery to query the linked server

SELECT *
FROM OPENQUERY(LINKED_SERVER_ORACLE, 
'
    WITH assignment_t AS (
        SELECT ''1x'' ID, 2 type_id, 554 assign_id FROM dual UNION ALL
        SELECT ''1x'', 3, 664 FROM dual UNION ALL
        SELECT ''2x'', 2, 919 FROM dual UNION ALL
        SELECT ''2x'', 4, 514 FROM dual
     ), type_t AS (
        SELECT 1 type_id, DATE ''2009-01-01'' create_date FROM dual UNION ALL
        SELECT 2, DATE ''2009-01-01'' FROM dual UNION ALL
      SELECT 3, DATE ''2009-01-03'' FROM dual UNION ALL
       SELECT 4, DATE ''2009-01-04'' FROM dual
      )
     SELECT DISTINCT a.*
     FROM assignment_t a
     JOIN type_t t ON (a.type_id = t.type_id)
    '
    ) AS QUERY

Thanks

Answer 1

The WITH keyword is ANSI-reserved. In SQL Server, the WITH keyword is used for Common Table Expressions. I am not familiar with PL/SQL and its exact syntax around the WITH keyword. My only suggestion is to ensure that the statements that you're sending in the OpenQuery method are valid statements to the PL/SQL parser/compiler.

The tricky thing about the query you're sending is the escaping of the single quotes while aliasing the columns. Perhaps that's one spot to investigate.

Answer 2

According to technet.microsoft.com, the target of OPENQUERY must be an OLE DB data source; "This is subject to the capabilities of the OLE DB provider."

My guess is that the OLE DB provider does not know how to process the WITH clause, in spite of the fact that the Oracle database itself can (at least, since 9ir2).