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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment