Hi Rex,

Here is the syntax to create a stored procedure in MS SQL Server. I've deleted portions of the script to make it smaller. The query selects all fields and records for one company code and one date. (199706)


CREATE PROCEDURE SP_MTS_REPORT_1
@EFFECTIVE_PERIOD INT ,
@WEI_CODE SMALLINT
AS BEGIN

SELECT * FROM dbo.WESTCST_199805_COS Westcst_199805_cos;
WHERE Westcst_199805_cos.GMS_COMPAN = @WEI_CODE;
AND Westcos.billing_period = @EFFECTIVE_PERIOD
ORDER BY Westcst_199805_cos.TO_NODE

END



The stored procedure is created in MS SQL Server and the execute permission is granted to users.

In VFP

x = sqlconnect()

y = sqlexec(x,"sp_mts_report_1 199807 444","result_set_cursor")

"result_set_cursor" is the name of the VFP cursor.

199807 is the date and 444 is the company code.

If y < 0, use aerror() to the error message from the server database.

Good luck,

Paul de Niverville


>Paul,
>
>I checked the settings and all looks fine.
>
>Can you show me an example of a simple SQL in a stored procedure which can be called via VFP? I am confused on the correct syntax.
>
>TIA
>
>Rex
>
>>Hi Rex,
>>
>>If your stored procedure asks for multiple rows, you should get multiple rows in your VFP cursor. If you are not getting all the rows, check your settings in VFP menu tools/options/remote data.
>>
>>Paul
>>
>>>Paul,
>>>
>>>Thanks for your reply. Another question: What do I need to do in my stored procedure to return multiple rows to the VFP app?
>>>
>>>