Saturday, February 25, 2012

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.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.|||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.

No comments:

Post a Comment