Is there a way to run Visual FoxPro code from a SQL Server CLR or CMD?

Question

Apologies for the strange question here.

Long story short is that we have a Visual FoxPro .prg that will execute a query using SQLEXEC(), and then uses AFIELDS() to get Field Name, Field Type, Field Width (length), and Decimal Places of the resultset columns to store into a SQL table (even if selected from a temp table or dynamic SQL).

What I'm looking for it to be able to run this .prg from a SQL Server CLR, or even using xp_cmdshell without having to manually open a Visual FoxPro Application.

I've already attempted to recreate this functionality in a CLR alone but kept running into issues with nested INSERT INTO statements when using it as a CLR Stored Procedure.

Additionally, I've attempted to create as a CLR SQL Function, but it would attempt to execute our custom hooks and throw "Procedure does not exist" errors, even when surrounded with IF Object_ID() IS NOT NULL.

Also, as some queries may be results of a temp table, I am unable to use OPENQUERY or OPENROWSET.

The end goal here is to move away from our Visual FoxPro client entirely, but the ability to get the column metadata of anything thrown at it is holding us back.

Answer 1

(Not an answer really, writing here as in a comment it would be a mess) If I understood you right, the table in question is a VFP table and have some stored procedure functions (maybe for insert\update\delete triggers, or validation check).

If that is the case, I am afraid you are out of luck. Your best bet might be having a VFP COM object in between or a VFP SP using SetResultSet() - that might fail if there are unsupported VFP commands.

On the other hand you are saying it starts with an SqlExec(), then likely it is not a VFP table. Then likely you could create a CLR function. Would it be possible for you to share more details, along with the VFP and C# codes.