Pass variable to linked server query

sofya · November 22nd, 2004, 03:56 PM

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

Anantsharma · November 23rd, 2004, 08:30 AM

Hi,

I feel the problem is in Passing Parameter. You have defined @LastDate
as DateTime and trying to pass it in the way of Varchar/Char. You should use CAST / Convert to change the datatype into Varchar.
Here it goes :-

DECLARE @LastDate datetime
SELECT @LastDate = MAX(DateChaged)
FROM tblPersonel
WHERE ACTIVE IN( 'A', 'T')

SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >='+ CONVERT(VARCHAR(10),@LastDate,110)+'''')

Hope this works.

Gud luck

B. Anant

Anantsharma · November 23rd, 2004, 08:37 AM

HI again,

If your second solution works but giving wrong info, try to use CONVERT to remove time part from @LastDate.

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

Gud luck

B. Anant

sofya · November 23rd, 2004, 11:41 AM

It didn't work, for OpenQuery I need to use FoxPro functions, since it runs on linked server.
Thanks for your reply.

happygv · November 23rd, 2004, 02:38 PM

Hi Sofya,

What is the value that comes with @LastDate? You have declared that as datetime, why don't you try declaring it as varchar(10) and pass the value in the right format?

Or try this.

Code:

Declare @strOpenQuery varchar(200)
Select @strOpenQuery = 'SELECT ACTIVE, EmpNumber  FROM tblPersonel WHERE DateChanged >=CTOD(''+ @LastDate + '')'
-- Display @strOpenQuery to see if the value gets substituted fine.
Select @strOpenQuery

SELECT * FROM OPENQUERY(FoxProLink, @strOpenQuery)

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection