>>>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=sqlsecurity>>
>>I'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).
>
>Unfortunately, such strategies rely on the VFPOLE and / or ODBC driver and only work in the 32 bit version of SQL server. Nowerdays most SQL server installations are 64 bit and therefore it will not work.
I tried out the OLEDB source on my local machine using VS2008 BIDs. I used VFPOLE and I saw the data from my .dbf when doing preview in OLE DB source data task flow control. Are you saying that once I deploy (I may be using wrong terminology here) this package to sql server, it will not work because the package will be initiated from 64 bit sql server? Thanks. Nick.
