Solved

SQL Server linked server error

Posted on 2012-03-10
5
1,136 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 42

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 42

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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