September 5, 2008 at 2:14 pm
I created a linked server “PP” to a visual fox pro database, through OLE DB. I installed the latest VFPOLEDB driver on the sql server.
When I run the query on the sql server with the SQL server management studio
Select * from openquery (PP,
Select * from invlot
‘)
I have no problems.
When I try to run the same query from my XP station with the SQL server management studio I get the error:
OLE DB provider "VFPOLEDB" for linked server "PP" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "PP".
September 5, 2008 at 2:25 pm
Did you use "drive:\folder" in the linked server? If you did you probably want to share the folder and use a UNC path (\\server\share).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 6:29 am
I am using unc path
\\ppserver\c$\cn_pp\pp2000\data
the cn_pp is set as the shared folder.
again it will work only from the sql server but not from other workstations.
September 8, 2008 at 6:55 am
Gad Friedman (9/8/2008)
I am using unc path\\ppserver\c$\cn_pp\pp2000\data
the cn_pp is set as the shared folder.
again it will work only from the sql server but not from other workstations.
I notice you are using the C$ administrative share, odds are the users from the other workstations do not have access to that share.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 7:03 am
I changed the path and took out the c$ and got the same error
I used to run the same setup on a sql 2000 with no problems
and now on the 2005 I can not run the query.
September 8, 2008 at 7:14 am
Hey,
Have you tested using a mapped drive to path (e.g. net use t: \\path)? Also, can you connect to share from your workstation?
Thanks,
Phillip Cox
September 8, 2008 at 7:36 am
I can connect from my workstation and changing the path did not help
September 8, 2008 at 7:58 am
And what security settings are you using on the linked server?
September 8, 2008 at 9:25 am
if I select the properties for the linked server
the security setup
for the local server login to repote server login mapping
I did not enter any information
for the option
Forc a login not defined in the list abouve - connections will be made without using a security context
the server roles for the user login is sysadmin
September 8, 2008 at 9:47 am
Try changing the login option to "Be made using the login's current security context". I tried this using a basic SQL login that is only in the public server role.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 10:28 am
will it be possible for you to send me
how did you set up your linked server
I am overlooking something
September 8, 2008 at 10:48 am
Mine will likely be slightly different as the only Foxpro data I have access to is in free tables so that's all I know. Here is the linked server create script:
/****** Object: LinkedServer [MISS] Script Date: 09/08/2008 12:46:18 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MISS', @srvproduct=N'VFPOLEDB', @provider=N'VFPOLEDB', @datasrc=N'\\server\share'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MISS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MISS', @optname=N'use remote collation', @optvalue=N'true'
The only thing I changed before posting was the @datasrc value.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 1:04 pm
No good
are you login in to the database engine with windows or sql authentication.
my fox pro in on a different server then the sql server
and it is also a free table directory.
I am running sql sp2
do you think I should upgrade to SP3?
September 8, 2008 at 2:38 pm
I am logging in as a SQL User as if I log in using Windows I am a sysadmin and I know I have rights to the directory.
I am on SP2 as well. What do you get when you run this:
Select serverproperty('ProductLevel'), ServerProperty('ResourceVersion')
I get:
SP2 9.00.3068
I think the issue is the Account the sql server service is running under does not have rights to the share. I actually had my sql server service running under my domain account which had rights to the share. When I changed that I could not longer connect.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 2:44 pm
SP29.00.3042
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply