Cannot create an instance of OLE DB provider "VFPOLEDB"

Experts,

I have a SQL instance which has a linked server to a Fox Pro instance via OLE.  I can query it within SQL Management Studio without issue.  If I use SQLCMD via a scheduled task to query it, I get the following error:

Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "LINKEDSERVERNAME".

I turned on In Process on the OLE Provider but that didn't make a difference, any ideas?
lm1189Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Does the SQLCMD run as 32 bit application?
Does the user under which the SQLCMD is running has full access rights to VFP OLE DB provider?
0
Olaf DoschkeSoftware DeveloperCommented:
It's sql server, which makes use of the Oledb provider, but when you use SQLCMD.exe, you have to use the 32bit version.

Expand the details section of the download page http://www.microsoft.com/en-US/download/details.aspx?id=36433 you see there is a 32bit (x86) and a 64bit (x64) version.

The prerequisite is the SQL Server ODBC Driver 11: http://www.microsoft.com/en-us/download/details.aspx?id=36434

In contrast to the SQLCMD.EXE (the MsSqlCmdLnUtils.msi) the ODBC driver installation (msodbcsql.msi) has to be the x64 one for a 64bit system, msodbcsql.msi installs both 32bit and 64bit odbc driver, and you find the msodbcsql11.dll in syswow64 and system32 after installing that.

Then installing the x86 version of the MsSqlCmdLnUtils.msi installs the 32bit SQLCMD.EXE in C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE, and that's what you need to run to be able to use the 32bit OLEDB Provider for VFP.

HTH

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware DeveloperCommented:
Any progress?

Several other things, that would be helpful to know:

How do you define your scheduled task, what is the sqlcmd call?
What is the account, under which the scheduled task runs, is it a system or domain account?
Does it run on the same server as the MSSQL Server?
Is the VFP OLEDB Provider installed on the same server as the MSSQL Server?
Is the VFP database located on the same server as the MSSQL Server?

In short: More details about the setup.

Also:
Are you using SQL Server Management Studio from a client or from the server?

Bye, Olaf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.