Solved

SQL Server linked server error

Posted on 2012-03-10
5
1,129 Views
Last Modified: 2012-03-14
I'm trying to set up a linked server within a sql server 2008 r2 express database.
The user for sql service it's not the default one, i'm using, sqlsrv (created within user domain group).
The linked server is created but every query returns this error
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB.1" for linked server "amee_sec" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB.1" for linked server "<linked server name>".
0
Comment
Question by:luciliacoelho
  • 2
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 300 total points
ID: 37705216
If the SQL Server is 64 bit than it cannot communicate with 32 bit VFP OLE DB.
If the SQL Server is 32 bit than you have to grant access to the SQL user for VFP OLE DB provider and also for the data folder of this provider.

The VFP OLE DB provider should be available for everybody so the problem is in the data access most probably. You may log in as the SQL user and try to create or open some file in your VFP data folder. It will show whether the user used for SQL Server service is able to access your VFP data folder.
0
 
LVL 27

Assisted Solution

by:CaptainCyril
CaptainCyril earned 200 total points
ID: 37705492
cSQL = "DRIVER=SQL Native Client"
cSQL = "DRIVER=SQL Server"
cSQL = cSQL + ";SERVER=111.222.333.444"
cSQL = cSQL + ";TRUSTED_CONNECTION=No"
cSQL = cSQL + ";DATABASE=mysteryshopper"
cSQL = cSQL + ";UID=surveyor"
cSQL = cSQL + ";PWD=sax"

I used SQL Native Client and SQL Server drivers and they worked pretty well.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 37705503
Of course, the preferred way is the opposite direction as Captain suggested but I have nothing against Linked server tests, so I've been answering the question at the first place. :-)
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 37705532
:-)
0
 

Author Comment

by:luciliacoelho
ID: 37721889
Thanks for your help.
Actually I solved this issue adding the user for SQL Server service to the admin users group.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now