Workaround for SQL Server 2012 64-bit connection to 32-bit VFPOLEDB – Stored Procedure and SQL Agent
13-Dec-1212 Leave a comment
In SQL Server 2005 32-bit > Linked Servers > Providers > VFPOLEDB was present, (with in process ticked)
This provider was not available in SQL Server 2012 64-bit
Workaround: Running a stored procedure from SQL 2012 back to to SQL 2005
- Create a linked server to the SQL Server 2005 32-bit. RPC = Remote Procedure Call. You need to set RPC Out to True. Possibly RPC as well.
- Then use/move/write queries in SQL 2005 and call them from SQL 2012 procedures using:
- EXECUTE [<SQL2005 server name>].[Database name].[Schema].[procedure]
- May need to add login to SQL 2005 and grant permission
Workaround: SQL Agent jobs that call procedures still not working
Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider “VFPOLEDB” for linked server “”. [SQLSTATE 42000] (Error 7303) OLE DB provider “VFPOLEDB” for linked server “” returned message “Invalid path or file name.”. [SQLSTATE 01000] (Error 7412). The step failed.
- Change the job type from Transact SQL to Operating System (CmdExec)
- Command = osql -E -Q “EXEC <sql 2012 db name>.dbo.<sp name>”