Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server linked server error

Posted on 2012-03-10
5
Medium Priority
?
1,165 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 43

Accepted Solution

by:
pcelba earned 1200 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 800 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 43

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

886 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