Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: OpenQuery() Help Needed

    Does anyone know how to use the openquery() method with dynamic SQL? I've tried these two different approaches with no success.

    DECLARE @sql nvarchar(4000)
    SET @sql = 'select producer_id from producer where producer_id = ' 'A' ' '

    select producer_id from openquery([sybtest], @sql)

    -------------------------------------------
    DECLARE @producer_id char(1)
    SET @producer_id = 'A'

    select producer_id from openquery([sybtest], 'select producer_id from producer where producer_id = ' ' ' + @producer_id + ' ' ' ' )

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Due to the way that OpenQuery works, you need the BIG ball-pein for this one! Try using:
    Code:
    DECLARE @producer_id char(1)
    SET @producer_id = 'A'
    
    EXECUTE ('select producer_id from openquery('
    +  '[sybtest], ''select producer_id from producer where producer_id = ''' 
    +  @producer_id + ''' )')
    -PatP

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    Thanks Pat.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    This works:

    DECLARE @sql nvarchar(4000)
    SET @sql = 'select count(*) from sysobjects where name like ''''r*'''''
    exec('select * from openquery(linked,'''+@sql+''')')

    Pat, have you tried yours?
    Last edited by snail; 08-25-04 at 17:31.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nope, straight shot from the hip.

    -PatP

Posting Permissions

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