I use sp_addlinkedserver create a link to Foxpro files by using the lastest VFPOLEDB driver. If I use QA to query the table, it works fine. But if I put that query into scheduled job in SQL Server Agent, I get this error message:
Executed as user: domainName\domainAccount. Could not initialize data source object of OLE DB provider 'VFPOLEDB'. [SQLSTATE 42000] (Error 7303) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize returned 0x80040e21]. [SQLSTATE 01000] (Error 7300). The step failed.
I guess it's the problem of security. But I used the same account to log into QA, to start SQL Server Agent, to own the job and that account is the Admin on both SQL Server box and the server foxpro file exists.
I am using Windows 2003 Standard Edition with SQL Server 2000 SP3