Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    1

    Unanswered: OpenQuery with join

    Hi

    I have a SP which queries a linked server using OpenQuery function.
    The remote query includes a join and an IN clause to get the desire result. ( The linked server uses Transoft ODBC driver.)

    The qry looks something like this:

    select * from OpenQuery(SERVER1,
    'select
    distinct C.item, C.operation, C.STD_OPERATION,
    D.operation_desc , C.operation_desc operation_desc
    from TABLE1 C left join
    (select distinct operation, operation_desc from TABLE1
    where operation in (select distinct STD_OPERATION from TABLE1 where
    item = ''9999999'' AND STD_OPERATION <> 0)
    AND item = ''STANDARD'') D
    on D.operation = C.STD_OPERATION where C.item = ''9999999'' ')

    When I run this Qry I get the following error:

    Server: Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
    [OLE/DB provider returned message: [Transoft][TSODBC][usqlsd]')' expected here (DISTINCT)]


    Any help would be greatly appreciated.
    thx

  2. #2
    Join Date
    Apr 2002
    Posts
    3
    Is the problem that Transoft can't handle the distinct keyword? It is SQL-92 compliant, but maybe the driver can't handle it? Have you tried removing distinct and running the query again?

    If this is the problem, you should be able to work around the problem using a group by clause.

    Hth.

    Paul Barbin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •