Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Pass a variable to a linked server (FoxPro) query

    I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.


    Here is the code:
    -------------------------------------
    DECLARE @LastDate datetime
    SELECT @LastDate = MAX(DateChaged)
    FROM tblPersonel
    WHERE ACTIVE IN( 'A', 'T')

    1. I tried:
    SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@LastDate+''')

    This line gives me an error msg:

    Could not execute query against OLE DB provider 'MSDASQL'.
    [OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]


    2. I tried to use CTOD() - FOXPRO function to convert character to date.

    SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@LastDate+'')')

    -this doesn't give any error, but doesn't limit the result set as it should.

    Thanks all.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I am surprised that #2 works, because OPENQUERY does not work with parameter parsing or dynamic SQL tricks that SQL lets you get away with.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    I read an article on MSDN, describing how to pass a variable to linked server query...at the same time I read elsewhere that you can not pass parametrs to OpenQuery...
    Does anybody have any suggestions?
    Thanks all.

Posting Permissions

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