Solved

SQL Server linked server error

Posted on 2012-03-10
5
1,143 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

630 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