Linked Server to Foxpro: February 2012

Monday, February 20, 2012

Linked Server to MYSQL using OLEDB Provider for MYSQL cherry

Good Morning

Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.

I can not get it to work.

I've created a UDL which tests fine. it looks like this

[oledb]

; Everything after this line is an OLE DB initstring

Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;

Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";

Initial Catalog=riverford_rhdx_20060822

Can any on help me convert this to corrrect syntax for sql stored procedure

sp_addlinkedserver

I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.

I used SQL server management studio to create the linked server then just scripted this out below.

I seem to be missing the user ID, but don't know where to put it in.

EXEC master.dbo.sp_addlinkedserver @.server = N'DATABRIDGE_OLEDB', @.srvproduct=N'mysql', @.provider=N'OleMySql.MySqlSource', @.datasrc=N'databridge', @.catalog=N'riverford_rhdx_20060822'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation compatible', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'data access', @.optvalue=N'true'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'dist', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'pub', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'rpc out', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'sub', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'connect timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'collation name', @.optvalue=null

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'lazy schema validation', @.optvalue=N'false'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'query timeout', @.optvalue=N'0'

EXEC master.dbo.sp_serveroption @.server=N'DATABRIDGE_OLEDB', @.optname=N'use remote collation', @.optvalue=N'false'

Many Thanks

David Hills

Have you tried to include password to initstring?|||No I have not, as there is no password set for testuser in the mysql database.|||

Have you tried to include user id like this @.UID='<my id>'? It shall work with MySQL OLE DB Provider|||

I got a reply from the software provider "cherry" they told me it won't work with

sqlserver 2005 as a linked server.

It should be quite straight forward to write a .net vb applet that uses the .net provider for mysql to

get the data out of mysql server, then use ado.net to write it into sqlserver 2005.

But what I wanted to do is to contain the code within sqlserver management studio so I don't

have external code.

Anyone know if I can write a VB.net or C## .net applet from with sqlserver 2005. It seems it's

the sort of intergrated solution that would be convient to be able to do?

|||I've setup a MySQL linked server in SQL 2005 using the ODBC driver for MySQL, and then using the OLEDB Provider for ODBC. Would you be apposed to doing it that way?

linked server to Mysql

I want to import some mysql data to SQL Server.
How can I go about the same as far as setting up linked server,etc.?
ThanksDear Hassan,
This link will help you.
http://www.microsoft.com/technet/pr...ploy/mysql.mspx
Regards,
Mohammad Daoud | Technical Development Manager | Mobile +962 79 999 65 85
Tel. +962 6 554 3721 | daoudm@.greatpbs.com
http://www.greatpbs.com| http://www.facebook.com/group.php?gid=18895609248/
"Hassan" wrote:

> I want to import some mysql data to SQL Server.
> How can I go about the same as far as setting up linked server,etc.?
> Thanks
>
>

Linked Server to MySQL

We are trying to do a linked server to MySQL from MS SQL2k. We downloaded MyODBC drivers, setup the system dsn successfully but then SQL errors out using both the GUI and the stored proc to add the linked server to mysql. Does anyone have a good site to reference or any words of advice. An hr or so of google didn't really give up any helpfully information.

Thanks,
DMWAre you setting up a linked server in SQL Server to connect to MySQL?

"and the stored proc to add the linked server to mysql."

If you are trying to connect to SQL Server from MySQL I would try the MySQL forum.

Also, it would help if you provided the error messages.

If you are trying to setup the linked in SQL Server to access MySQL you should have to execute a sp in MySQL.

Also if you reply to this, post your code for adding the linked server.|||should "not"|||I had it working both ways, MySQL to connect to SQL and have a read-only access to a specific view, as well as SQL to have MySQL as a linked server. The only difference I see is I was doing it from MSDE on XP to MySQL on 2003. But the engine is the same for standard/enterprise and MSDE, so I don't see where you can have issues there. Try to post your error, maybe that would clear the mud ;)

linked server to Mysql

I want to import some MYSQL data to SQL Server.
How can I go about the same as far as setting up linked server,etc.?
Thanks
Dear Hassan,
This link will help you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
Regards,
Mohammad Daoud | Technical Development Manager | Mobile +962 79 999 65 85
Tel. +962 6 554 3721 | daoudm@.greatpbs.com
http://www.greatpbs.com| http://www.facebook.com/group.php?gid=18895609248/
"Hassan" wrote:

> I want to import some MYSQL data to SQL Server.
> How can I go about the same as far as setting up linked server,etc.?
> Thanks
>
>

linked server to Mysql

I want to import some MYSQL data to SQL Server.
How can I go about the same as far as setting up linked server,etc.?
ThanksDear Hassan,
This link will help you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
Regards,
Mohammad Daoud | Technical Development Manager | Mobile +962 79 999 65 85
Tel. +962 6 554 3721 | daoudm@.greatpbs.com
http://www.greatpbs.com|http://www.facebook.com/group.php?gid=18895609248/
"Hassan" wrote:
> I want to import some MYSQL data to SQL Server.
> How can I go about the same as far as setting up linked server,etc.?
> Thanks
>
>

linked server to jet database

Hi,
I have a problem using an jet database as a linked server.
when there is no database password on the mdb database everything is working
fine.
When I put a password an the mdb database I receive the error:
Msg 7399, Level 16, State 1, Line 1The OLE DB provider "MSDASQL" for linked
server "ExpertM" reported an error. The provider did not give any information
about the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data
source object of OLE DB provider "MSDASQL" for linked server "ExpertM".
The database password is correct because when I enter a fault password I
receive the expected error that the password is incorrect.
Any idea?
Thanks
Bart
Hello Bart,
I reproduce this issue.
My suggestion is that you could create a DSN for the access mdb database
and use the Microsoft OLEDB Provider for ODBC Drivers to link this DSN in
the SQL Server as a workaround.
Please let me know if this is OK for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

linked server to jet database

Hi,
I have a problem using an jet database as a linked server.
when there is no database password on the mdb database everything is working
fine.
When I put a password an the mdb database I receive the error:
Msg 7399, Level 16, State 1, Line 1The OLE DB provider "MSDASQL" for linked
server "ExpertM" reported an error. The provider did not give any informatio
n
about the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the dat
a
source object of OLE DB provider "MSDASQL" for linked server "ExpertM".
The database password is correct because when I enter a fault password I
receive the expected error that the password is incorrect.
Any idea?
Thanks
BartHello Bart,
I reproduce this issue.
My suggestion is that you could create a DSN for the access mdb database
and use the Microsoft OLEDB Provider for ODBC Drivers to link this DSN in
the SQL Server as a workaround.
Please let me know if this is OK for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Linked server to Interbase

Hi all.
Have a problem with the fact that i dont seem to have an appropriate OLEDB
driver to use when i want to setup a linked server to an Interbase database
from Borland.
I am using SQL Server 2000.
Have anyone here done this successfully and how?
/RisunHi
I have not done this myself, but I think you have to use the ODBC one.
John
"Risun" wrote:

> Hi all.
> Have a problem with the fact that i dont seem to have an appropriate OLEDB
> driver to use when i want to setup a linked server to an Interbase databas
e
> from Borland.
> I am using SQL Server 2000.
> Have anyone here done this successfully and how?
> /Risun

Linked server to Interbase

Hi all.
Have a problem with the fact that i dont seem to have an appropriate OLEDB
driver to use when i want to setup a linked server to an Interbase database
from Borland.
I am using SQL Server 2000.
Have anyone here done this successfully and how?
/Risun
Hi
I have not done this myself, but I think you have to use the ODBC one.
John
"Risun" wrote:

> Hi all.
> Have a problem with the fact that i dont seem to have an appropriate OLEDB
> driver to use when i want to setup a linked server to an Interbase database
> from Borland.
> I am using SQL Server 2000.
> Have anyone here done this successfully and how?
> /Risun

Linked server to Interbase

Hi all.
Have a problem with the fact that i dont seem to have an appropriate OLEDB
driver to use when i want to setup a linked server to an Interbase database
from Borland.
I am using SQL Server 2000.
Have anyone here done this successfully and how?
/RisunHi
I have not done this myself, but I think you have to use the ODBC one.
John
"Risun" wrote:
> Hi all.
> Have a problem with the fact that i dont seem to have an appropriate OLEDB
> driver to use when i want to setup a linked server to an Interbase database
> from Borland.
> I am using SQL Server 2000.
> Have anyone here done this successfully and how?
> /Risun

Linked Server to Informix

I have set up linked servers before but not with INFORMIX. I was wondering what criteria goes into the product name and data source fields in the new linked server window when connecting to an INFORMIX server. I have SQL Server 2005 installed on my machine and have also installed the informix drivers on my machine. Has anyone had to do this before? Any help is appreciated.

Thanks,

Matt

Not sure if you got an answer, but....

The provider name does not seem to matter, but I put the name of the Informix OLEDB driver, ifxoledbc

The data source is database@.InformixServer.

Hope that helps.

Shannon

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379008&SiteID=1

HTH

|||

Linked Server to Informix

Thanks,

Matt

Not sure if you got an answer, but....

The provider name does not seem to matter, but I put the name of the Informix OLEDB driver, ifxoledbc

The data source is database@.InformixServer.

Hope that helps.

Shannon

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379008&SiteID=1

HTH

|||

Linked Server to Indexing Service

Hello,

I have a problem... I need to create a linked server to an indexing service. I am trying to create the linked server through Enterprise Manager for SQL Server. The indexing service that I am trying to link to is located on a different server than the SQL Server database that will contain the linked server.

I don't know anything about creating a linked server. All my attempts have resulted in errors. I don't know the Provider Name, Product Name, Data Source, Provider String, Location, Catalog that needs to be used. I also don't have any clue as to setting the Security Options or the Server Options for the linked server.

Another thing, where is an explanation of how to set up a linked server. I tried SQL Server Books Online but I can't make heads or tails of it.

Thank you,
HeatherLook up sp_addlinkedserver in Books Online. There is an example of an Indexing Service linked server.|||Thanks. I've looked at the SQL Server Books Online and have done what is suggested. I still could not get any success. I think it's because the database server and the index server are on completely different machines.

Heather|||Be sure the account your SQL Server is running under has permission to connect to the other machine. The SYSTEM account does not have permission to see the network, so if you are using that, you have little chance of success. You can try changing it to run using your domain account to see if it will fix the problem.

Linked Server to IBM AS400

I need to create a linked server to communicate with an AS400 server by using
Client Access ODBC and then launch some query on particular files (inside
libraries).
How can I solve this issue?
I haven't founded any documentation about this subject.
Many thanks for your suggestsInstall Client Access on the SQL Server box and make sure
you have the client updated with whatever service pack level
based on updates to your AS400. Then configure the linked
server as described in books online under Linked Servers,
configuring.
For data source, use the IP address of the AS400.
For provider string, you need to include the library you are
using, connect timeout setting and code page. There is some
documentation for the settings in the Client Access help
files.
You'd set the provider string somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
<Pasquale@.discussions.microsoft.com> wrote:
>I need to create a linked server to communicate with an AS400 server by using
>Client Access ODBC and then launch some query on particular files (inside
>libraries).
>How can I solve this issue?
>I haven't founded any documentation about this subject.
>Many thanks for your suggests|||Oh thanks!
I have tried to create a Client Access ODBC link, then I create a linked
server by setting only the data source with the Client Access ODBC data
source and user id + pwd. Then I have executed a SELECT by indicating the
linkedserver.linkedserver.libraryname.filename and I have the correct results.
Many thanks
"Sue Hoegemeier" wrote:
> Install Client Access on the SQL Server box and make sure
> you have the client updated with whatever service pack level
> based on updates to your AS400. Then configure the linked
> server as described in books online under Linked Servers,
> configuring.
> For data source, use the IP address of the AS400.
> For provider string, you need to include the library you are
> using, connect timeout setting and code page. There is some
> documentation for the settings in the Client Access help
> files.
> You'd set the provider string somewhat like:
> InitCat=YourLibrary;CCSID=37;PCCodePage=1252;
> Data Source=xxx.xxx.xxx.xxx
> Settings will depend on how your AS400 is configured. Again,
> the Client Access help files have information on the
> necessary connection string settings.
> -Sue
> On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
> <Pasquale@.discussions.microsoft.com> wrote:
> >I need to create a linked server to communicate with an AS400 server by using
> >Client Access ODBC and then launch some query on particular files (inside
> >libraries).
> >How can I solve this issue?
> >I haven't founded any documentation about this subject.
> >
> >Many thanks for your suggests
>

Linked Server to IBM AS400

I need to create a linked server to communicate with an AS400 server by usin
g
Client Access ODBC and then launch some query on particular files (inside
libraries).
How can I solve this issue?
I haven't founded any documentation about this subject.
Many thanks for your suggestsInstall Client Access on the SQL Server box and make sure
you have the client updated with whatever service pack level
based on updates to your AS400. Then configure the linked
server as described in books online under Linked Servers,
configuring.
For data source, use the IP address of the AS400.
For provider string, you need to include the library you are
using, connect timeout setting and code page. There is some
documentation for the settings in the Client Access help
files.
You'd set the provider string somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
<Pasquale@.discussions.microsoft.com> wrote:

>I need to create a linked server to communicate with an AS400 server by usi
ng
>Client Access ODBC and then launch some query on particular files (inside
>libraries).
>How can I solve this issue?
>I haven't founded any documentation about this subject.
>Many thanks for your suggests|||Oh thanks!
I have tried to create a Client Access ODBC link, then I create a linked
server by setting only the data source with the Client Access ODBC data
source and user id + pwd. Then I have executed a SELECT by indicating the
linkedserver.linkedserver.libraryname.filename and I have the correct result
s.
Many thanks
"Sue Hoegemeier" wrote:

> Install Client Access on the SQL Server box and make sure
> you have the client updated with whatever service pack level
> based on updates to your AS400. Then configure the linked
> server as described in books online under Linked Servers,
> configuring.
> For data source, use the IP address of the AS400.
> For provider string, you need to include the library you are
> using, connect timeout setting and code page. There is some
> documentation for the settings in the Client Access help
> files.
> You'd set the provider string somewhat like:
> InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
> Data Source=xxx.xxx.xxx.xxx
> Settings will depend on how your AS400 is configured. Again,
> the Client Access help files have information on the
> necessary connection string settings.
> -Sue
> On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
> <Pasquale@.discussions.microsoft.com> wrote:
>
>

Linked server to FoxPro - permissions

I set up a linked server and it is working great.....when i'm logged in
under the same account that SQL runs under (also the account that i set up
the linked server as, if it matters). However, if i'm logged in as anyone
else i get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'VFPOLEDB' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
It even does this with my account (full sql and domain admin). Help!you'd want to specify the remote login and how local login is mapped.
--Everyone connects via Admin db user
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:AF38060C-1BCB-4A83-86BE-A04068521894@.microsoft.com...
>I set up a linked server and it is working great.....when i'm logged in
> under the same account that SQL runs under (also the account that i set up
> the linked server as, if it matters). However, if i'm logged in as anyone
> else i get the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'VFPOLEDB' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IUnknown::QueryInterface
> returned 0x80070005: Access denied.].
> It even does this with my account (full sql and domain admin). Help!|||doesn't that map logins from sql server to remote(foxpro) logins? I don't
believe there is any sort of security on the foxpro database (not even sure
that they're capable of that).
If I'm wrong about any of that, please let me know.|||ha can help me out in my prob ...? check out Ravi --> primary Key falling
short sorry for this ..!|||most jet engine has an 'admin' login.
either:
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
or
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
should work for vfp. The key is in the 'false' parameter. It tells sql not
use the current executing user credential for accessing vfp.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:6D4C43C2-8EFA-4109-BF77-4D1ADE7746D6@.microsoft.com...
> doesn't that map logins from sql server to remote(foxpro) logins? I don't
> believe there is any sort of security on the foxpro database (not even
> sure
> that they're capable of that).
> If I'm wrong about any of that, please let me know.|||Tried both, to no avail.........any other ideas?
Thanks!
"oj" wrote:
> most jet engine has an 'admin' login.
> either:
> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
> or
> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
> should work for vfp. The key is in the 'false' parameter. It tells sql not
> use the current executing user credential for accessing vfp.
>
> --
> -oj|||you're not doing this, i hope.
http://support.microsoft.com/kb/241267
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:2B4D4370-6256-4A77-B713-D0EC77326898@.microsoft.com...
> Tried both, to no avail.........any other ideas?
> Thanks!
> "oj" wrote:
>> most jet engine has an 'admin' login.
>> either:
>> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
>> or
>> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
>> should work for vfp. The key is in the 'false' parameter. It tells sql
>> not
>> use the current executing user credential for accessing vfp.
>>
>> --
>> -oj
>|||Nope. The DBC is on the SQL server.
"oj" wrote:
> you're not doing this, i hope.
> http://support.microsoft.com/kb/241267
> --
> -oj
>|||Is the folder where VFP resides shared to _everyone_.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:5F20399B-6EE4-43E3-A277-919F2B04C563@.microsoft.com...
> Nope. The DBC is on the SQL server.
> "oj" wrote:
>> you're not doing this, i hope.
>> http://support.microsoft.com/kb/241267
>> --
>> -oj
>>|||Yes, I gave everyone full control down to the folder. It is not shared
however.
"oj" wrote:
> Is the folder where VFP resides shared to _everyone_.
>
> --
> -oj
>|||please post your addlinkedserver query. perhaps, you want to update your
driver or use different provider.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:AD263E8B-498C-4D0F-90C6-25E919EFD2DF@.microsoft.com...
> Yes, I gave everyone full control down to the folder. It is not shared
> however.
> "oj" wrote:
>> Is the folder where VFP resides shared to _everyone_.
>>
>> --
>> -oj|||I'm actually using the gui to do it, but i'll list the info:
General Tab:
Provider:MS OLE DB Provider for Visual FoxPro
Data source: d:\geoffTest\test.dbc
Provider string: VFPOLEDB.1
On the security tab:
No mappings defined, all will be made without using a security context
Server Options tab:
Data Access is checked
Use Remote Collation is checked
The VFP driver on the machine is current, I just updated it last week or so.
Thanks for keeping with this!
"oj" wrote:
> please post your addlinkedserver query. perhaps, you want to update your
> driver or use different provider.
>
> --
> -oj
>|||try using version independent vfp provider (i.e. no .1).
Provider string: VFPOLEDB
also, specify "remote user" as "admin"
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
> I'm actually using the gui to do it, but i'll list the info:
> General Tab:
> Provider:MS OLE DB Provider for Visual FoxPro
> Data source: d:\geoffTest\test.dbc
> Provider string: VFPOLEDB.1
> On the security tab:
> No mappings defined, all will be made without using a security context
> Server Options tab:
> Data Access is checked
> Use Remote Collation is checked
>
> The VFP driver on the machine is current, I just updated it last week or
> so.
> Thanks for keeping with this!
>
> "oj" wrote:
>> please post your addlinkedserver query. perhaps, you want to update your
>> driver or use different provider.
>>
>> --
>> -oj|||I tried variations of both, but nothing seemed to change. I tried using the
mapping local logins to the remote login of admin, and also mapping undefined
logins to admin.
Changing the driver didn't seem to affect anything, it still queries ok when
logged in under the sql account
"oj" wrote:
> try using version independent vfp provider (i.e. no .1).
> Provider string: VFPOLEDB
> also, specify "remote user" as "admin"
> --
> -oj
>
> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
> news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
> > I'm actually using the gui to do it, but i'll list the info:
> >
> > General Tab:
> > Provider:MS OLE DB Provider for Visual FoxPro
> > Data source: d:\geoffTest\test.dbc
> > Provider string: VFPOLEDB.1
> >
> > On the security tab:
> > No mappings defined, all will be made without using a security context
> >
> > Server Options tab:
> > Data Access is checked
> > Use Remote Collation is checked
> >
> >
> > The VFP driver on the machine is current, I just updated it last week or
> > so.
> >
> > Thanks for keeping with this!
> >
> >
> > "oj" wrote:
> >
> >> please post your addlinkedserver query. perhaps, you want to update your
> >> driver or use different provider.
> >>
> >>
> >> --
> >> -oj
> >>
>
>|||i have no problem running this.
exec sp_addlinkedserver
'myVFP',
'',
'MSDASQL',
null,
null,
'Driver=Microsoft Visual Foxpro Driver;
DBQ=;
SourceType=DBC;
SourceDB=d:\geoffTest\test.dbc'
exec sp_addlinkedsrvlogin 'myVFP',false,null,'Admin',null
select * from openquery(myVFP,'select * from your_dbf')x
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:EB3FD65A-162D-46D0-8CD5-8AB1CC7708B5@.microsoft.com...
>I tried variations of both, but nothing seemed to change. I tried using
>the
> mapping local logins to the remote login of admin, and also mapping
> undefined
> logins to admin.
> Changing the driver didn't seem to affect anything, it still queries ok
> when
> logged in under the sql account
> "oj" wrote:
>> try using version independent vfp provider (i.e. no .1).
>> Provider string: VFPOLEDB
>> also, specify "remote user" as "admin"
>> --
>> -oj
>>
>> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
>> news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
>> > I'm actually using the gui to do it, but i'll list the info:
>> >
>> > General Tab:
>> > Provider:MS OLE DB Provider for Visual FoxPro
>> > Data source: d:\geoffTest\test.dbc
>> > Provider string: VFPOLEDB.1
>> >
>> > On the security tab:
>> > No mappings defined, all will be made without using a security context
>> >
>> > Server Options tab:
>> > Data Access is checked
>> > Use Remote Collation is checked
>> >
>> >
>> > The VFP driver on the machine is current, I just updated it last week
>> > or
>> > so.
>> >
>> > Thanks for keeping with this!
>> >
>> >
>> > "oj" wrote:
>> >
>> >> please post your addlinkedserver query. perhaps, you want to update
>> >> your
>> >> driver or use different provider.
>> >>
>> >>
>> >> --
>> >> -oj
>> >>
>>|||Awesome, using that syntax to create the linked server gave me a different
error message (never had to run the addlinkedserverlogin sproc):
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
Since I am using different query syntax(... instead of openquery) i played
around and came up with this query, which works:
select top 10 * from geoff.test..test1
before i only had the linked server name, but i needed to add the DBC name
in there as the catalog, and now it works! Out of curiosity, is this using
the foxpro driver or the ODBC driver? Either way, thank you so much for the
help!
"oj" wrote:
> i have no problem running this.
>
> exec sp_addlinkedserver
> 'myVFP',
> '',
> 'MSDASQL',
> null,
> null,
> 'Driver=Microsoft Visual Foxpro Driver;
> DBQ=;
> SourceType=DBC;
> SourceDB=d:\geoffTest\test.dbc'
> exec sp_addlinkedsrvlogin 'myVFP',false,null,'Admin',null
> select * from openquery(myVFP,'select * from your_dbf')x
>
>
> --
> -oj
>
> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
> news:EB3FD65A-162D-46D0-8CD5-8AB1CC7708B5@.microsoft.com...
> >I tried variations of both, but nothing seemed to change. I tried using
> >the
> > mapping local logins to the remote login of admin, and also mapping
> > undefined
> > logins to admin.
> >
> > Changing the driver didn't seem to affect anything, it still queries ok
> > when
> > logged in under the sql account
> >
> > "oj" wrote:
> >
> >> try using version independent vfp provider (i.e. no .1).
> >>
> >> Provider string: VFPOLEDB
> >>
> >> also, specify "remote user" as "admin"
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "ddavis" <ddavis@.discussions.microsoft.com> wrote in message
> >> news:7DB51115-381F-452A-BEC9-C51FB30605ED@.microsoft.com...
> >> > I'm actually using the gui to do it, but i'll list the info:
> >> >
> >> > General Tab:
> >> > Provider:MS OLE DB Provider for Visual FoxPro
> >> > Data source: d:\geoffTest\test.dbc
> >> > Provider string: VFPOLEDB.1
> >> >
> >> > On the security tab:
> >> > No mappings defined, all will be made without using a security context
> >> >
> >> > Server Options tab:
> >> > Data Access is checked
> >> > Use Remote Collation is checked
> >> >
> >> >
> >> > The VFP driver on the machine is current, I just updated it last week
> >> > or
> >> > so.
> >> >
> >> > Thanks for keeping with this!
> >> >
> >> >
> >> > "oj" wrote:
> >> >
> >> >> please post your addlinkedserver query. perhaps, you want to update
> >> >> your
> >> >> driver or use different provider.
> >> >>
> >> >>
> >> >> --
> >> >> -oj
> >> >>
> >>
> >>
> >>
>
>|||glad to hear it works for you. it's using fox driver.
--
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:CD8A20E1-5B31-477A-9CC7-03DB819A556E@.microsoft.com...
> Awesome, using that syntax to create the linked server gave me a different
> error message (never had to run the addlinkedserverlogin sproc):
> Server: Msg 7313, Level 16, State 1, Line 1
> Invalid schema or catalog specified for provider 'MSDASQL'.
> OLE DB error trace [Non-interface error: Invalid schema or catalog
> specified for the provider.].
> Since I am using different query syntax(... instead of openquery) i played
> around and came up with this query, which works:
> select top 10 * from geoff.test..test1
> before i only had the linked server name, but i needed to add the DBC name
> in there as the catalog, and now it works! Out of curiosity, is this
> using
> the foxpro driver or the ODBC driver? Either way, thank you so much for
> the
> help!
>

Linked server to FoxPro - permissions

you'd want to specify the remote login and how local login is mapped.
--Everyone connects via Admin db user
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:AF38060C-1BCB-4A83-86BE-A04068521894@.microsoft.com...
>I set up a linked server and it is working great.....when i'm logged in
> under the same account that SQL runs under (also the account that i set up
> the linked server as, if it matters). However, if i'm logged in as anyone
> else i get the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'VFPOLEDB' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IUnknown::QueryInterfac
e
> returned 0x80070005: Access denied.].
> It even does this with my account (full sql and domain admin). Help!I set up a linked server and it is working great.....when i'm logged in
under the same account that SQL runs under (also the account that i set up
the linked server as, if it matters). However, if i'm logged in as anyone
else i get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'VFPOLEDB' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
It even does this with my account (full sql and domain admin). Help!|||you'd want to specify the remote login and how local login is mapped.
--Everyone connects via Admin db user
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:AF38060C-1BCB-4A83-86BE-A04068521894@.microsoft.com...
>I set up a linked server and it is working great.....when i'm logged in
> under the same account that SQL runs under (also the account that i set up
> the linked server as, if it matters). However, if i'm logged in as anyone
> else i get the error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'VFPOLEDB' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IUnknown::QueryInterfac
e
> returned 0x80070005: Access denied.].
> It even does this with my account (full sql and domain admin). Help!|||doesn't that map logins from sql server to remote(foxpro) logins? I don't
believe there is any sort of security on the foxpro database (not even sure
that they're capable of that).
If I'm wrong about any of that, please let me know.|||doesn't that map logins from sql server to remote(foxpro) logins? I don't
believe there is any sort of security on the foxpro database (not even sure
that they're capable of that).
If I'm wrong about any of that, please let me know.|||ha can help me out in my prob ...? check out Ravi --> primary Key falling
short sorry for this ..!|||ha can help me out in my prob ...? check out Ravi --> primary Key falling
short sorry for this ..!|||most jet engine has an 'admin' login.
either:
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
or
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
should work for vfp. The key is in the 'false' parameter. It tells sql not
use the current executing user credential for accessing vfp.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:6D4C43C2-8EFA-4109-BF77-4D1ADE7746D6@.microsoft.com...
> doesn't that map logins from sql server to remote(foxpro) logins? I don't
> believe there is any sort of security on the foxpro database (not even
> sure
> that they're capable of that).
> If I'm wrong about any of that, please let me know.|||most jet engine has an 'admin' login.
either:
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
or
EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
should work for vfp. The key is in the 'false' parameter. It tells sql not
use the current executing user credential for accessing vfp.
-oj
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:6D4C43C2-8EFA-4109-BF77-4D1ADE7746D6@.microsoft.com...
> doesn't that map logins from sql server to remote(foxpro) logins? I don't
> believe there is any sort of security on the foxpro database (not even
> sure
> that they're capable of that).
> If I'm wrong about any of that, please let me know.|||Tried both, to no avail.........any other ideas?
Thanks!
"oj" wrote:

> most jet engine has an 'admin' login.
> either:
> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, 'Admin', NULL
> or
> EXEC sp_addlinkedsrvlogin 'linked_server', 'false', NULL, NULL, NULL
> should work for vfp. The key is in the 'false' parameter. It tells sql not
> use the current executing user credential for accessing vfp.
>
> --
> -oj

Linked server to foxpro - order by causing error

We're playing with linked servers to hopefully help us ease the migration
from fox to sql. The first problem i've run into is with the "order by"
clause. When running it against our test table, i get the following error:
An error occurred while preparing a query for execution against OLE DB
provider 'VFPOLEDB'.
[OLE/DB provider returned message: Syntax error.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandPrepare: [Ton
gue]
repare returned 0x80040e14].
query:
select [year],uniqueid as b from geoff...test1
where uniqueid > 340000
order by b
uniqueid is just an identity (or equivalent). I've tried several variations,
ordered on different columns, not used an alias, etc.
This works:
select [year] from geoff...test1
where uniqueid between 340000 and 400000
group by [year]
order by [year] desc
Anyone have any ideas?
Thanks in advanced for any replies, and let me know if there is a better
group for me to post this in.Hi D,
First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
I created a table in the VFP Northwind database called TestYear (UniqueID
Integer AutoInc, Year Integer) and added a few rows. I issued the following
query with no problems at all:
Select [Year], UniqueID As B From VFP_Northwind...TestYear
Where UniqueID > 3
Order By B
I was also successful when the brackets around Year were removed. VFP is
rather forgiving when reserved words are used.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:70406F79-1ACA-4CD6-98C5-AD31173EB4E1@.microsoft.com...
> We're playing with linked servers to hopefully help us ease the migration
> from fox to sql. The first problem i've run into is with the "order by"
> clause. When running it against our test table, i get the following error:
> An error occurred while preparing a query for execution against OLE DB
> provider 'VFPOLEDB'.
> [OLE/DB provider returned message: Syntax error.]
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandPrepare: [T
ongue]
> repare returned 0x80040e14].
>
> query:
> select [year],uniqueid as b from geoff...test1
> where uniqueid > 340000
> order by b
> uniqueid is just an identity (or equivalent). I've tried several
> variations,
> ordered on different columns, not used an alias, etc.
> This works:
> select [year] from geoff...test1
> where uniqueid between 340000 and 400000
> group by [year]
> order by [year] desc|||Hi D,
First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
I created a table in the VFP Northwind database called TestYear (UniqueID
Integer AutoInc, Year Integer) and added a few rows. I issued the following
query with no problems at all:
Select [Year], UniqueID As B From VFP_Northwind...TestYear
Where UniqueID > 3
Order By B
I was also successful when the brackets around Year were removed. VFP is
rather forgiving when reserved words are used.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"ddavis" <ddavis@.discussions.microsoft.com> wrote in message
news:70406F79-1ACA-4CD6-98C5-AD31173EB4E1@.microsoft.com...
> We're playing with linked servers to hopefully help us ease the migration
> from fox to sql. The first problem i've run into is with the "order by"
> clause. When running it against our test table, i get the following error:
> An error occurred while preparing a query for execution against OLE DB
> provider 'VFPOLEDB'.
> [OLE/DB provider returned message: Syntax error.]
> OLE DB error trace [OLE/DB Provider 'VFPOLEDB' ICommandPrepare: [T
ongue]
> repare returned 0x80040e14].
>
> query:
> select [year],uniqueid as b from geoff...test1
> where uniqueid > 340000
> order by b
> uniqueid is just an identity (or equivalent). I've tried several
> variations,
> ordered on different columns, not used an alias, etc.
> This works:
> select [year] from geoff...test1
> where uniqueid between 340000 and 400000
> group by [year]
> order by [year] desc

Linked server to foxpro - order by causing error

Linked Server to Foxpro

I am trying to setup a linked server from server1 (SQL Server) to Foxpro DBC
files on server2 and I get the following error:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have checked that the account that SQL Server runs as has full permissions
on the folder where the DBC file lives.
I can also setup an ODBC connection and use MS Query to access the data but
SQL will not see it or give me any meaningful error!
If I copy the Foxpro files locally onto the SQL server then it is fine.
Once I have this working then I need to figure out how to create the
triggers to populate the Foxpro DBs!!
Any advice would be much appreciated.
Regards
James
Hi James,
When there are problems on a remote machine that don't show on the SQL
Server it's usually a problem of permissions. You said below that the SQL
Server has permissions on the remote machine, but maybe it's not working as
you expect.
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"JamesA" <JamesA@.discussions.microsoft.com> wrote in message
news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> I have checked that the account that SQL Server runs as has full
> permissions
> on the folder where the DBC file lives.
> I can also setup an ODBC connection and use MS Query to access the data
> but
> SQL will not see it or give me any meaningful error!
> If I copy the Foxpro files locally onto the SQL server then it is fine.
|||Hi Cindy,
I looked at the SQL Server service on my server and it is running as
DOMAIN\SQLAdmin and on the folder where the Foxpro files live the account
DOMAIN\SQLAdmin has full permissions and even looking at the effective
permisions on the DBC file, this account has full permissions. Any
suggestions how I can test to see if this is a permissions problem?
Regards
James
"Cindy Winegarden" wrote:

> Hi James,
> When there are problems on a remote machine that don't show on the SQL
> Server it's usually a problem of permissions. You said below that the SQL
> Server has permissions on the remote machine, but maybe it's not working as
> you expect.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
> "JamesA" <JamesA@.discussions.microsoft.com> wrote in message
> news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
>
>

Linked Server to Foxpro

I am trying to setup a linked server from server1 (SQL Server) to Foxpro DBC
files on server2 and I get the following error:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have checked that the account that SQL Server runs as has full permissions
on the folder where the DBC file lives.
I can also setup an ODBC connection and use MS Query to access the data but
SQL will not see it or give me any meaningful error!
If I copy the Foxpro files locally onto the SQL server then it is fine.
Once I have this working then I need to figure out how to create the
triggers to populate the Foxpro DBs!!
Any advice would be much appreciated.
Regards
JamesHi James,
When there are problems on a remote machine that don't show on the SQL
Server it's usually a problem of permissions. You said below that the SQL
Server has permissions on the remote machine, but maybe it's not working as
you expect.
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"JamesA" <JamesA@.discussions.microsoft.com> wrote in message
news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> I have checked that the account that SQL Server runs as has full
> permissions
> on the folder where the DBC file lives.
> I can also setup an ODBC connection and use MS Query to access the data
> but
> SQL will not see it or give me any meaningful error!
> If I copy the Foxpro files locally onto the SQL server then it is fine.|||Hi Cindy,
I looked at the SQL Server service on my server and it is running as
DOMAIN\SQLAdmin and on the folder where the Foxpro files live the account
DOMAIN\SQLAdmin has full permissions and even looking at the effective
permisions on the DBC file, this account has full permissions. Any
suggestions how I can test to see if this is a permissions problem?
Regards
James
"Cindy Winegarden" wrote:
> Hi James,
> When there are problems on a remote machine that don't show on the SQL
> Server it's usually a problem of permissions. You said below that the SQL
> Server has permissions on the remote machine, but maybe it's not working as
> you expect.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
> "JamesA" <JamesA@.discussions.microsoft.com> wrote in message
> news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> > I have checked that the account that SQL Server runs as has full
> > permissions
> > on the folder where the DBC file lives.
> >
> > I can also setup an ODBC connection and use MS Query to access the data
> > but
> > SQL will not see it or give me any meaningful error!
> >
> > If I copy the Foxpro files locally onto the SQL server then it is fine.
>
>

Linked Server to Foxpro

I am trying to setup a linked server from server1 (SQL Server) to Foxpro DBC
files on server2 and I get the following error:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have checked that the account that SQL Server runs as has full permissions
on the folder where the DBC file lives.
I can also setup an ODBC connection and use MS Query to access the data but
SQL will not see it or give me any meaningful error!
If I copy the Foxpro files locally onto the SQL server then it is fine.
Once I have this working then I need to figure out how to create the
triggers to populate the Foxpro DBs!!
Any advice would be much appreciated.
Regards
JamesHi James,
When there are problems on a remote machine that don't show on the SQL
Server it's usually a problem of permissions. You said below that the SQL
Server has permissions on the remote machine, but maybe it's not working as
you expect.
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"JamesA" <JamesA@.discussions.microsoft.com> wrote in message
news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> I have checked that the account that SQL Server runs as has full
> permissions
> on the folder where the DBC file lives.
> I can also setup an ODBC connection and use MS Query to access the data
> but
> SQL will not see it or give me any meaningful error!
> If I copy the Foxpro files locally onto the SQL server then it is fine.|||Hi Cindy,
I looked at the SQL Server service on my server and it is running as
DOMAIN\SQLAdmin and on the folder where the Foxpro files live the account
DOMAIN\SQLAdmin has full permissions and even looking at the effective
permisions on the DBC file, this account has full permissions. Any
suggestions how I can test to see if this is a permissions problem?
Regards
James
"Cindy Winegarden" wrote:

> Hi James,
> When there are problems on a remote machine that don't show on the SQL
> Server it's usually a problem of permissions. You said below that the SQL
> Server has permissions on the remote machine, but maybe it's not working a
s
> you expect.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
> "JamesA" <JamesA@.discussions.microsoft.com> wrote in message
> news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
>
>

Linked Server to FileMaker

I've developed a number of databases in FileMaker for a client, and am now in the process of converting them over to SQL Server. During the transition, there will be duplicates of tables in both platforms, so I must design a method of data synchronization, so that data maintenance need only be done in one database, not both. I've done it with DTS, but that's certainly not the most efficient. Instead, I'm trying to implement Linked Servers within SQL Server so that data can be updated dynamically with SQL statements. Has anyone ever had the pleasure of trying to do this? If so, would you mind sharing with me how you completed the Linked Server dialogue box (see attached) to connect to FileMaker.What is filemaker, and is it ODBC Compliant?

Does it have a system catalog?|||I have found the answer. In case you ever have to connect to FileMaker via a Linked Server, here's what you need:

1. The FileMaker file(s) must be opened, and set to Multi-User status and configured to have the Remote Data Access Companion enabled.
2. Set up a System DSN for the FileMaker Pro ODBC driver.
3. In the General tab of the ODBC FileMaker Pro Driver setup, ensure that the "Use Remote Connection" checkbox is checked, and the Server Address field is completed correctly (with either the IP address or the host name).
4. The Linked Server Properties dialog box needs only two entries: Provider Name to be "Microsoft OLE DB Provider for ODBC Drivers" (from the drop-down list), and Data Source to be your FileMaker System DSN as per item 2 above.

It was item 3 above that was causing all my problems. I was running FileMaker on the same (local) machine, and didn't think it would be considered a remote connection.

...Dave Ryan

Linked Server to Excel Issue

I am using a linked server to connect SQL Server to an Excel Spreadsheet on
the network. If I'm physically on the SQL Server box I can open the linked
server and see the named range on the desired spreadsheet. Also If I'm on
the SQL Server box I can query the spreadsheet using OPENQUERY and everythin
g
comes across beautifully.
However, if I connect to the SQL Server box from a different location (via
Enterprise Manager) and try to run a query using the OPENQUERY and linked
server in question. I keep getting the dreaded "error 7399: blah, blah,
blah".
Any thoughts?
Thanks in advance,
RaulYour proxy account does not have access to network resources.
See this article for more info:
http://msdn.microsoft.com/library/d.../>
sz_8sdm.asp
-oj
"Raul" <raul@.nothere.com> wrote in message
news:6AD242AB-BD43-49B2-AC3D-119EC788CF91@.microsoft.com...
>I am using a linked server to connect SQL Server to an Excel Spreadsheet on
> the network. If I'm physically on the SQL Server box I can open the
> linked
> server and see the named range on the desired spreadsheet. Also If I'm on
> the SQL Server box I can query the spreadsheet using OPENQUERY and
> everything
> comes across beautifully.
> However, if I connect to the SQL Server box from a different location (via
> Enterprise Manager) and try to run a query using the OPENQUERY and linked
> server in question. I keep getting the dreaded "error 7399: blah, blah,
> blah".
> Any thoughts?
> Thanks in advance,
> Raul|||Thanks,
Raul
"oj" wrote:

> Your proxy account does not have access to network resources.
> See this article for more info:
> http://msdn.microsoft.com/library/d...
a-sz_8sdm.asp
>
> --
> -oj
>
> "Raul" <raul@.nothere.com> wrote in message
> news:6AD242AB-BD43-49B2-AC3D-119EC788CF91@.microsoft.com...
>
>

Linked server to Excel - Fails across network

I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.
Make sure that your SQL Server services are running under a Domain User
account. Also, for that account, make sure that the Everyone group has FULL
CONTROL of the source share and underlying NTFS. Finally, make sure the
Everyone goup has FULL CONTROL of the temp directory for the SQL Server
service account. This is how Jet works.
Sincerely,
Anthony Thomas

"Dave Alvarado" <Dave Alvarado@.discussions.microsoft.com> wrote in message
news:95369ABE-EE31-4B69-A8BE-CA1222601A7F@.microsoft.com...
I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on
devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.

Linked server to Excel - Fails across network

I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
--
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.Make sure that your SQL Server services are running under a Domain User
account. Also, for that account, make sure that the Everyone group has FULL
CONTROL of the source share and underlying NTFS. Finally, make sure the
Everyone goup has FULL CONTROL of the temp directory for the SQL Server
service account. This is how Jet works.
Sincerely,
Anthony Thomas
"Dave Alvarado" <Dave Alvarado@.discussions.microsoft.com> wrote in message
news:95369ABE-EE31-4B69-A8BE-CA1222601A7F@.microsoft.com...
I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
--
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on
devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.

Linked server to Excel - Fails across network

I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on devbox03
.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.Make sure that your SQL Server services are running under a Domain User
account. Also, for that account, make sure that the Everyone group has FULL
CONTROL of the source share and underlying NTFS. Finally, make sure the
Everyone goup has FULL CONTROL of the temp directory for the SQL Server
service account. This is how Jet works.
Sincerely,
Anthony Thomas
"Dave Alvarado" <Dave Alvarado@.discussions.microsoft.com> wrote in message
news:95369ABE-EE31-4B69-A8BE-CA1222601A7F@.microsoft.com...
I'm having trouble getting a linked server to work across the network when
connecting to an Excel file Here's the code I'm using:
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\devbox03\shared\test.xls', NULL, 'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT Invoice.[Total]
FROM [ExcelSource]...['Total Invoice$'] Invoice
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
--
Running the code above gives me this error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\devbox03\shared\test.xls'. It is already opened exclusively
by another user, or you need permission to view its data.]
The goal here is to get this into a stored procedure to import this excel
file every month. I'm executing the code on one server (sqlbox01), connected
with Windows Authentication as myself through query analyzer. The file lives
on a different server (devbox03). The "shared" share is set up for Everyone
to have modify permissions, and the underlying folder (and excel file) have
Everyone / modify as well.
The interesting thing is, when I run filemon on devbox03, I don't see any
connections at all being made to the file.
The exact same code works when I run it in QA connected to devbox03 as
myself, or if I connect from another server (sqlbox02), and I see a whole
mess of connections in filemon, so the problem seems to be related to
connecting to the file across the network from that one machine. It's like
sqlbox01 doesn't even get to the point of trying to hit the file on
devbox03.
I have no idea how the authentication works behind the scenes, or what
connections should be made, so I'm looking for some guidance toward things I
can check and may just be missing.

linked server to Excel

We've created a linked server to access an Excel spreadsheet that resides on
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:

> We've created a linked server to access an Excel spreadsheet that resides
on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provide
r
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...[vbcol=seagreen]
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
>
resides on[vbcol=seagreen]
2000)[vbcol=seagreen]
provider[vbcol=seagreen]

linked server to Excel

We've created a linked server to access an Excel spreadsheet that resides on
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:

> We've created a linked server to access an Excel spreadsheet that resides on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>
|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...[vbcol=seagreen]
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
resides on[vbcol=seagreen]
2000)[vbcol=seagreen]
provider[vbcol=seagreen]

linked server to Excel

We've created a linked server to access an Excel spreadsheet that resides on
the same server as SQL Server 2000. If we log on to the database server
(Windows 2000), then launch Enterprise Manager, the linked server works
perfectly.
But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
and attempt to use the linked server we get: "Error 7399 : OLE DB provider
'Microsoft.Jet.OLEDB.4.0' reported an error."
Any ideas?SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
5.0')...[test1$]
And the 'test1' is the name of work sheet.
"Vince" wrote:
> We've created a linked server to access an Excel spreadsheet that resides on
> the same server as SQL Server 2000. If we log on to the database server
> (Windows 2000), then launch Enterprise Manager, the linked server works
> perfectly.
> But, if we launch Enterprise Manager from a desktop machine (Windows 2000)
> and attempt to use the linked server we get: "Error 7399 : OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' reported an error."
> Any ideas?
>
>|||Thanx for the response.
However, I get the same result.
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FFE5CD8B-ED99-4568-8B55-21AB6A73242C@.microsoft.com...
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\test.xls";User ID=;Password=;Extended properties=Excel
> 5.0')...[test1$]
> And the 'test1' is the name of work sheet.
> "Vince" wrote:
> > We've created a linked server to access an Excel spreadsheet that
resides on
> > the same server as SQL Server 2000. If we log on to the database server
> > (Windows 2000), then launch Enterprise Manager, the linked server works
> > perfectly.
> >
> > But, if we launch Enterprise Manager from a desktop machine (Windows
2000)
> > and attempt to use the linked server we get: "Error 7399 : OLE DB
provider
> > 'Microsoft.Jet.OLEDB.4.0' reported an error."
> >
> > Any ideas?
> >
> >
> >

Linked Server to DBC Data

Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset operati
on from SQL Server against once of the DBC files from one of our vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following e
rror...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************
Can someone advise?
Thanks!
TravisSorry, for clarification, a DBC file is a FoxPro Data file.
"REM7600" <rem7600@.hotmail.com> wrote in message news:%23mkG6JLiGHA.4044@.TK2
MSFTNGP03.phx.gbl...
Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset operati
on from SQL Server against once of the DBC files from one of our vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following e
rror...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************
Can someone advise?
Thanks!
Travis|||Hi Travis,
You haven't posted which version of SQL Server you are using or the code
you're using in the OpenRowset statement.
First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
I've successfully set up linked servers with code like this:
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_Northwind',
@.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
@.provider=N'VFPOLEDB',
@.datasrc=N'C:\Program Files\Microsoft Visual FoxPro
9\Samples\Northwind\Northwind.dbc',
@.provstr=N'VFPOLEDB.1'
After that you can access data with the four-part naming convention:
Select * From VFP_Northwind...Customers
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"REM7600" <rem7600@.hotmail.com> wrote in message
news:%23mkG6JLiGHA.4044@.TK2MSFTNGP03.phx.gbl...
Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset
operation from SQL Server against once of the DBC files from one of our
vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following
error...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o
Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************|||Cindy,
Thanks for the reply... I've definitely seen your name a time or two around
the web in my search. As it turns out, I believe it to be related to
security. And how our network (Linux/Samba based shares) handles
authentication. I could be wrong but I've given up... Locally I can make
it work fine, on a share NO GO...
Regardless, I've taken a different direction on the solution and it looks
like I'm going to be able to come out with my "goaled for" end result.
Thanks for the reply.
TR

> you're using in the OpenRowset statement.
> First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
> provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
> I've successfully set up linked servers with code like this:
> EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_Northwind',
> @.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
> @.provider=N'VFPOLEDB',
> @.datasrc=N'C:\Program Files\Microsoft Visual FoxPro
> 9\Samples\Northwind\Northwind.dbc',
> @.provstr=N'VFPOLEDB.1'
>

Linked Server to DBASE 5 Tables

I am trying to create a linked server to some dbase 5 tables. I have read several other posts about using the OPENROWSET option and that does work however, I would like to be able to create a linked server for easier access.

End Goal: I am developing an ASP based app that needs to join a DB5 table to a SQL table.

Any help on the linked server properties in Enterprise Manager for dbase5 or on the proper sp_addlinkedserver function for dbase5 would be highly appreciated.

JoeCan't you use DTS to get the data from DBase5.|||I have pulled the tables in with a DTS package very easily however, I would like to be able to access the data real time in it's current place. for example:

Select sqltable.Name, dbasetable.BillCharges
From sqltable
Inner Join database...dbasetable On
sqltable.AcctNum = dbasetable.AcctNum

--------------------
By using an example in one of these forums I do have another work around:

SELECT sqltable.Name, dbasetable.BillCharges
FROM OPENROWSET('MSDASQL','DRIVER={Microsoft Dbase driver(*.dbf)}' ,'SELECT Name, AcctNum FROM \\Server\Data\dbasetable.dbf') dBase
Inner Join sqltable On
sqltable.AcctNum = dbase.AcctNum

But this is a little too slow for an asp application.

Thanks for the reply ....

Linked Server to dBASE

I'm trying to create a "Linked Server" in QA to a dBASE file. Below are some
examples I've tried. They seem to create the "Linked Server" in Ent.
Manager, but give errors when i run a SELECT .. OPENQUERY statement. They
also give errors with Ent. Manager when trying to expand the linked server's
node.
The errors say the path is wrong, which it isn't. Other errors when clicking
"tables" under the linked server are "OLE DB" errors.
Does anyone have the correct syntax for creating a linked server to a dBASE
file? Which provider should I use?
-- Example 1:
Exec sp_addlinkedserver @.server ='alarm_dBASE',
@.srvproduct ='DBASE',
@.Provider ='Microsoft.Jet.OLEDB.4.0',
@.datasrc='E:\data\databases\myDBASEfile.dbf'
-- example 2
exec sp_addlinkedserver 'alarm_dBASE2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'E:\data\databases\myDBASEfile.dbf',
NULL,
'dBase IV'
go
exec sp_addlinkedsrvlogin @.rmtsrvname='alarm_dBASE2',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = NULL,
@.rmtpassword= NULL
goscott (sbailey@.mileslumber.com) writes:
> I'm trying to create a "Linked Server" in QA to a dBASE file. Below are
> some examples I've tried. They seem to create the "Linked Server" in
> Ent. Manager, but give errors when i run a SELECT .. OPENQUERY
> statement. They also give errors with Ent. Manager when trying to expand
> the linked server's
> node.
> The errors say the path is wrong, which it isn't. Other errors when
> clicking "tables" under the linked server are "OLE DB" errors.
Note that the path is from the SQL Server machine, not on your local
machine. (Unless SQL Server runs on your local machine.)
Since I don't know anything about dBase, this is all I can contribute
with.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||i'm doing all of this from the server.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BCD2B8BF520Yazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Note that the path is from the SQL Server machine, not on your local
> machine. (Unless SQL Server runs on your local machine.)
> Since I don't know anything about dBase, this is all I can contribute
> with.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||You're using the wrong driver so it cannot work. You're trying to use the
Jet engine (Microsoft Access .mdb engine).
You have an ODBC driver for .dbf files. I'm not shure if it works with Dbase
IV (most probably it'll do the trick).
Go to Control Panel - Data Sources (ODBC) and make a DSN.
change @.Provider to OLEDB for ODBC and supply your DSN (search the web on
exact syntax)
Milan|||Use "Microsoft OLE DB Provider for ODBC Drivers" instead of MS.Jet
NO "@.datasrc" but use provider string as of "Driver={Microsoft dBASE Driver
(*.dbf)};DriverID=21;Dbq=(you file, no filename);Mode=Read;
i.e. @.provstr = 'c:\' if you file is under c drive

Linked Server to DB2 from SQL 2005 IA64BIT computer

Has anyone created a linked server to DB2 on AS/400 from SQL Server 2005 IA64 BIT?

We were hoping to use the new Microsoft OLEDB Provider for DB2...The problem is the driver looks like it will only work on x64 computers.
"The single setup program will install the provider and tools on both x86 and x64 computers"

We were able to install the driver on the IA64 computer and were able to successfully create a data source from the Data Access Tool. Oddly enough when we attempted to create a linked server in SSMS we received an error, we also noticed that the DB2OLEDB provider is not listed in SSMS.

This is becoming a real problem so any ideas on how we can get this driver to work or suggestions on an alternative would be greatly appreciated.

thx.
art.

Did you ever figure this out? I am trying to configure a linked server using DB2 Connect as provider.

Linked Server to DB2 from SQL 2005 IA64BIT computer

Has anyone created a linked server to DB2 on AS/400 from SQL Server 2005 IA64 BIT?

This is becoming a real problem so any ideas on how we can get this driver to work or suggestions on an alternative would be greatly appreciated.

thx.
art.

Did you ever figure this out? I am trying to configure a linked server using DB2 Connect as provider.

Monday, February 20, 2012

Linked Server to MYSQL using OLEDB Provider for MYSQL cherry

linked server to Mysql

Linked Server to MySQL

linked server to Mysql

linked server to Mysql

linked server to jet database

linked server to jet database

Linked server to Interbase

Linked server to Interbase

Linked server to Interbase

Linked Server to Informix

Linked Server to Informix

Linked Server to Indexing Service

Linked Server to IBM AS400

Linked Server to IBM AS400

Linked server to FoxPro - permissions

Linked server to FoxPro - permissions

Linked server to foxpro - order by causing error

Linked server to foxpro - order by causing error

Linked Server to Foxpro

Linked Server to Foxpro

Linked Server to Foxpro

Linked Server to FileMaker

Linked Server to Excel Issue

Linked server to Excel - Fails across network

Linked server to Excel - Fails across network

Linked server to Excel - Fails across network

linked server to Excel

linked server to Excel

linked server to Excel

Linked Server to DBC Data

Linked Server to DBASE 5 Tables

Linked Server to dBASE

Linked Server to DB2 from SQL 2005 IA64BIT computer

Linked Server to DB2 from SQL 2005 IA64BIT computer

Linked Server to Foxpro

Blog Archive

About Me