Problem with Linked Server (FoxPro tables)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Problem with Linked Server (FoxPro tables)

Author

Topic

ArviL
Starting Member

25 Posts

Posted - 2010-09-10 : 04:56:39

Hi

I need to read data from dbf-tables in certain location. I created a linked server:
--
USE MASTER
EXEC sp_addlinkedserver @server = 'GetDbf', @srvproduct = 'Visual FoxPro',
@provider = 'VFPOLEDB', @datasrc = '\\Dataserver\Datafolder'
--

I can query dbf tables in Datafolder -unless the table is used. When the table I'm trying to query is in use, the error is returned:
[Msg 7314, Level 16, State 1, Line 19
The OLE DB provider "VFPOLEDB" for linked server "GetDbf" does not contain the table "MyTable". The table either does not exist or the current user does not have permissions on that table.]

I think the problem occurs as by default FoxPro driver assumes, that the query must be in Exclusive mode (the table will be locked). Is there some way to force the linked server into shared mode: Exclusive=No

Thanks in advance!
Arvi Laanemets

ArviL
Starting Member

25 Posts

Posted - 2010-09-13 : 08:53:46

Working solution:

I had to use MSDASQL instead of VFPOLEDB, and openrowset() instead of linked server. It allowed me to apply additional parameters, EXCLUSIVE=NO included.

Something like this:
--
USE MyDatabase

SELECT * FROM OPENROWSET(
'MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\SourceServer\SourcePath\;
SourceType=DBF;
Exclusive = No;
Collate=Machine;
NULL = NO;
DELETED = YES;
BACKGROUNDFETCH=NO',
'SELECT * FROM MyTable')

Arvi Laanemets

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources