0

I have the following Recursive CTE which is used in an OpenQuery function in SQL. I use OpenQuery to query a SQL Linked Server.

(DOES NOT WORK)

SELECT * FROM OPENQUERY([MyLinkedServerDB], 'WITH SPChainCTE (NAME, INHERIT_FROM) 
AS
(
    Select sp.NAME, sp.INHERIT_FROM from PRODUCT AS sp
    where NAME = ''ProductName''
    UNION ALL
    Select sp.NAME, sp.INHERIT_FROM from PRODUCT AS sp
    INNER JOIN SPChainCTE AS ch on sp.NAME = ch.INHERIT_FROM
)
Select NAME, INHERIT_FROM from SPChainCTE;
');

The Above CTE does not work.

However if I execute a non recursive CTE using OpenQuery it works fine. (This works)

SELECT * FROM OPENQUERY([FP], 'WITH SPChainCTE (NAME, INHERIT_FROM) 
AS
(
    Select sp.NAME, sp.INHERIT_FROM from ENG.FOCALPOINT.SW_PRODUCT AS sp
    where sp.NAME = ''ProductName''

)
Select NAME, INHERIT_FROM from SPChainCTE;
');

Any suggestions? Am I missing something here?

Opal
  • 81,889
  • 28
  • 189
  • 210
user3605100
  • 33
  • 2
  • 8
  • How is it not working? BTW, the code sample (curly braces) are your friend... – Andrew Jun 18 '15 at 01:50
  • Not sure why it is not working. I was wondering if there was any dependency between Open Query and Recursive CTE's. – user3605100 Jun 18 '15 at 17:41
  • Two questions: 1. Does the recursive CTE work when run directly on the target of [MyLinkedServerDB]? 2. What differences are there between the linked server targets [MyLinkedServerDB] and [FP]? – Brian Aug 17 '17 at 18:42

0 Answers0