>Hi Community,
>
>Does anyone know how to write a SQL Server 2008 stored procedure to retrieve data from FoxPro. I have payroll data in visual FoxPro that a third party vendor needs access. The vendor uses SQL Server. I created a staging database on SQL Server. I need to run a stored procedure in SQL Server to get the data after processing has occurred in my FoxPro payroll program. I know how to send data from FoxPro to SQL, but my scenario I will have to provide a store procedure that has to be initiated from SQL Server to get my dbf data. I was unable to find anything on the internet where someone has attempted to retrieve dbf data from SQL Server. Any help, much appreciated. FoxPro still rules.
>
The only time I've ever used SQL Server to pull data from VFP, I used Integration Services.
I don't know for a fact (and I'm sure others who have done it can jump in)...but I would imagine you "might" be able to defined a Linked Server in SQL Server to access the Fox data, and then use OPENQUERY. (There's always the issue of making sure you have a good driver)
Maybe OPENROWSET could be used. Here is a link. Again, I've only used SSIS in recent years, but this might help.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7b66009c-3592-4af2-98bc-b12ea2aab4a6/openrowset-problem-with-foxpro-and-sql-server-2008-on-windows-2008-r2?forum=sqlsecurityI'm sure some others will jump in with some options. If you'd like to know more about the SSIS route, let me know. (It wouldn't involve stored procs).