none
Querying a linked server having a LIKE operator RRS feed

  • Question

  • Hi All,

    I am executing a query on a linked server with the following syntax

    =select * from OPENQUERY(linkedserver,'select * from Person where addressline1 like 'Mount%' and city like '%Irvine%'')

    When I try to run the query it throws an error at 'Mount%'.

    I have also tried keeping the select statement in the variable and pass the variable but still not working.

    Could someone please help me how to handle this kind of situation.

    Thank you 


    VSP

    Thursday, August 23, 2012 11:54 PM

Answers

  • Try

    =select * from OPENQUERY(linkedserver,'select * from Person where addressline1 like ''Mount%'' and city like ''%Irvine%''')


    Many Thanks & Best Regards, Hua Min

    Friday, August 24, 2012 1:09 AM
  • It is OK for doing that.

    You can do like this

    select a.*, b.*

    from (select * from OPENQUERY(linkedserver,'select * from Person where addressline1 like ''Mount%'' and city like ''%Irvine%''')) a,

    local_tab1 b

    where ...;


    Many Thanks & Best Regards, Hua Min

    Friday, August 24, 2012 5:35 AM

All replies

  • Syntax mistake in code.

    Try this :

    select * from OPENQUERY(linkedserver,'select * from Person where addressline1 like ''Mount%'' and city like ''%Irvine%''')


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Friday, August 24, 2012 12:12 AM
  • Hi Rohit,

    Thank you for your prompt response I have modified my query and kept double quote instead of single quotes. I got the following error 

    OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" returned message "Deferred prepare could not be completed.".

    Thank you,


    VSP

    Friday, August 24, 2012 1:02 AM
  • Try

    =select * from OPENQUERY(linkedserver,'select * from Person where addressline1 like ''Mount%'' and city like ''%Irvine%''')


    Many Thanks & Best Regards, Hua Min

    Friday, August 24, 2012 1:09 AM
  • thank you very much for the reply, can I use joins in the Linked servers, I have a join condition where the host table is in the actual server and the another table is in the linked server. 

    Thank you,


    VSP

    Friday, August 24, 2012 4:22 AM
  • It is OK for doing that.

    You can do like this

    select a.*, b.*

    from (select * from OPENQUERY(linkedserver,'select * from Person where addressline1 like ''Mount%'' and city like ''%Irvine%''')) a,

    local_tab1 b

    where ...;


    Many Thanks & Best Regards, Hua Min

    Friday, August 24, 2012 5:35 AM
  • I know this is an old post but when I google i see this to be the first post .So though Below fix might help other .

    In the where clause use addressline1 = ''*Mount*''

    * is what i used instead of like %.

    -Raj

    Tuesday, June 2, 2020 5:26 AM