calling foxpro stored procedure from sql using oledb

[Trixol]

I'm trying to call a stored procedure that is integrated in a FoxPro .dbc
file, and all this done from within SQL Server 2005. What I've allready found
is an updated driver for FVP oledb, and the use of linked servers. Finally I
seem to get some result but still no succes. But can't figure out why it's
having problems using just the parameter.

VFP sp:
PROCEDURE TstOrder(tcOrder as string)
lcRetVal = 'Order nummer ' + tcOrder
return lcRetVal
ENDPROC

SQL-transact:
exec sp_dropserver 'FoxLink'
exec sp_addlinkedserver @server = 'FoxLink', @srvproduct = 'FoxPro',
@provider = 'vfpoledb', @datasrc = 'Y:\..wms_comm.dbc'
EXEC master.dbo.sp_serveroption @server=N'FoxLink', @optname=N'rpc',
@optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'FoxLink', @optname=N'rpc out',
@optvalue=N'true'
GO
declare @returnvalue nvarchar(50);
set @returnvalue = NULL;
exec ('@returnvalue = TstOrder("1234")') at FoxLink;
results into :

OLE DB provider "vfpoledb" for linked server "FoxLink" returned message
"Missing comma (,).".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'FoxLink'.


Can anyone give me some suggestions on what's going wrong ?

[Trixol]

Allready solved it using different command. Instead of using the exec I tried
doing it using a simple openquery. Works fine now.

so:
exec ('@returnvalue = TstOrder("Mike")') at FoxLink;
was replaced by:
select * from OPENQUERY (FoxLink,'TstOrder("0800001")');
what I now can use in the rest of the needed stored procedure in SQL.

regards,
Trixol.

Post by Trixol
I'm trying to call a stored procedure that is integrated in a FoxPro .dbc
file, and all this done from within SQL Server 2005. What I've allready found
is an updated driver for FVP oledb, and the use of linked servers. Finally I
seem to get some result but still no succes. But can't figure out why it's
having problems using just the parameter.
PROCEDURE TstOrder(tcOrder as string)
lcRetVal = 'Order nummer ' + tcOrder
return lcRetVal
ENDPROC
exec sp_dropserver 'FoxLink'
@provider = 'vfpoledb', @datasrc = 'Y:\..wms_comm.dbc'
@optvalue=N'true'
@optvalue=N'true'
GO
OLE DB provider "vfpoledb" for linked server "FoxLink" returned message
"Missing comma (,).".
Msg 7215, Level 17, State 1, Line 1
Could not execute statement on remote server 'FoxLink'.
Can anyone give me some suggestions on what's going wrong ?