Trying to link FoxPro files in MS SQL Server 2008 via Linked Server

I am trying to link FoxPro files in MS SQL Server 2008 via Linked Server.  However, while doing so, Im getting the following error:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" has not been registered.

I have also installed Microsoft Visual FoxPro OLE DB.  I thought by installing it, it will register it but it did not.
exec sp_addlinkedserver @server = 'temp',
@srvproduct = 'VFP',
@provider = 'VFPOLEDB',
@datasrc='"C:\DbfRestore"'
GO
SELECT * FROM temp...sys1map
GO
sp_dropserver @server = 'temp'
GO

Open in new window

tgatif2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
Are you able to use VFP OLE DB connection from another application, e.g. VB.NET?
Do you see the VFP OLE DB provider in the list of available providers in SSMS?
Also, what is your SQL Server version? 64 bit versions do not support 32 bit VFP OLE DB providers.

Another problem could be SQL Server service user account. Is the installed provider accessible by this user account?

Here is similar problem described: http://forum.lessthandot.com/viewtopic.php?f=17&t=5902
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
tgatif2000Author Commented:
I do not Visual Studio installed on the machine.

How can I determine that VFP OLE DB proider is in the list of available providers in SSMS.

How can I find out what is the version of SQL Server?

How can I make the installed provider accessible by this user account?
0
tgatif2000Author Commented:
SQL Server 2008 is 32 bit
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

pcelbaCommented:
1) SSMS is SQL Server Management Studio. Available providers are listed in the Server Objects section.

2) SELECT @@version

3) You have to look in Windows services what user account is used for SQL Server Service. Then you have to look if this account has given access rights to VFP OLE DB provider DLL. You'll find it on the place where you've installed it.
0
tgatif2000Author Commented:
VFPOLEDB.1 is not in listed providers.  The listed providers are being shown in the attachment.

The version is Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (VM).

I have attached the image showing the login for sql server.  However, when I login to a specific database, i used different user/password.
listed-providers.JPG
sql-server-login.JPG
0
pcelbaCommented:
OK, it means the VFP OLE DB installation did not pass all necessary steps. You may try to uninstall it and install again. The latest version is available here: http://www.microsoft.com/downloads/details.aspx?familyid=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

If your SQL Server is running under System account then it should not have problems accessing VFP OLE DB provider.
0
tgatif2000Author Commented:
I installed it but it is still not appearing in the providers list.
0
pcelbaCommented:
Could you please check owner of the vfpoledb.dll? It should be installed in C:\Program Files\Common Files\System\Ole DB\
0
tgatif2000Author Commented:
Administrator installed VFP OLE DB and it worked all fine.
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 Applications

From novice to tech pro — start learning today.