My company is using SQL 2000 and a Visual Foxpro
database. In order to perform joint queries against the
two data sources, we set up a linked server definition to
the VFP data source. Everything was working perfectly
until the other day, when I noticed that the server had
not been updated in quite some time. I installed SQL 2000
SP3a, which worked just fine. However, after that, when I
submit a query that uses the VFP linked server, it does
not work like it did. If the VFP part of the query is
going against a local view defined in the VFP database,
sometimes it works, sometimes it doesn't. There are no
error messages, nothing to indicate there is a problem.
It just doesn't return any rows. This is true if I do the
query through EM, Query Analyzer, or via ODBC connection
to the server. This is a critical situation. I even went
so far as to remove SQL 2000 and re-install and NOT
upgrade to SP3a. No change. Any assitance in this area
would be greatly appreciated. Thank youJohn,
It's never a good idea to just apply service packs without testing first.
Have a look here and see if this addresses your issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;810474&Product=sql2k
--
Andrew J. Kelly
SQL Server MVP
"John" <kcmo_710@.yahoo.com> wrote in message
news:00f001c3afbb$07f640f0$7d02280a@.phx.gbl...
> My company is using SQL 2000 and a Visual Foxpro
> database. In order to perform joint queries against the
> two data sources, we set up a linked server definition to
> the VFP data source. Everything was working perfectly
> until the other day, when I noticed that the server had
> not been updated in quite some time. I installed SQL 2000
> SP3a, which worked just fine. However, after that, when I
> submit a query that uses the VFP linked server, it does
> not work like it did. If the VFP part of the query is
> going against a local view defined in the VFP database,
> sometimes it works, sometimes it doesn't. There are no
> error messages, nothing to indicate there is a problem.
> It just doesn't return any rows. This is true if I do the
> query through EM, Query Analyzer, or via ODBC connection
> to the server. This is a critical situation. I even went
> so far as to remove SQL 2000 and re-install and NOT
> upgrade to SP3a. No change. Any assitance in this area
> would be greatly appreciated. Thank you
Showing posts with label foxpro. Show all posts
Showing posts with label foxpro. Show all posts
Friday, March 30, 2012
Monday, March 12, 2012
Linked Server Connection Problem
Hi
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
ThanksHello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> 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.
>|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/...7D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> [url]http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-[/ur
l]
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> 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.
>
>|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
----
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwin
d','northwind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a. Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
----
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
----
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a. Start FileMon to monitor the file system events.
b. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c. Stop FileMon and save the log to a file named filemon.log.
d. Start RegMon to monitor the registry events.
e. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f. Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> 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.
>
>|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
----
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
ThanksHello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> 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.
>|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/...7D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> [url]http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-[/ur
l]
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> 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.
>
>|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
----
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwin
d','northwind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a. Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
----
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
----
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a. Start FileMon to monitor the file system events.
b. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c. Stop FileMon and save the log to a file named filemon.log.
d. Start RegMon to monitor the registry events.
e. Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f. Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> 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.
>
>|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
----
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
Linked Server Connection Problem
Hi
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
Thanks
Hello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> 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.
>
|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/d...D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> http://www.microsoft.com/downloads/d...D8F-2D58-491F-
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> 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.
>
>
|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwind','northw ind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a.Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a.Start FileMon to monitor the file system events.
b.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c.Stop FileMon and save the log to a file named filemon.log.
d.Start RegMon to monitor the registry events.
e.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f.Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> 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.
>
>
|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
I have some code that I am testing to read data from a FoxPro file.
I have copied the FoxPro file locally to test.
I have created a linked server that points to this file. Enterprise manager
correctly shows the tables available.
Works fine.!
However, when I create another linked server pointing towards the network
files, enterprise manager shows no available tables and my code returns an
error stating that the table does not exist.
I have checked the security permissions on the network drive/directories and
I appear to have full access. I can indeed move, delete etc these files.
I can successfully set up a linked server to an Excel file which resides in
the same directory as the problem FoxPro files.
I have the most up to date VFPOLEDB.DLL file.
Any ideas please .?
Thanks
Hello,
Please refer to the following articles for related information:
199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
http://support.microsoft.com/?id=199131
207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
http://support.microsoft.com/?id=207595
The article provide T-SQL code example demonstrates how to set up and use
distributed queries with FoxPro with OpenQuery and OpenRowset functions. It
also demonstrates how to update a remote FoxPro table from SQL Server 2000.
You can test this code in SQL Query Analyzer after you install the Visual
FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works fine.
You will need to change the data source names and path to the FoxPro files
as appropriate.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Unfortunately the articles below do not help.
I already had the syntax for creating a linked server and the code snippet
was I assume using VFP which I am not.
In this instance I have actually created the linked servers manually within
Enterprise Manager. I have created 2 identical servers excepts for the data
source. One points locally to a FoxPro *.dbc file whilst the other points to
the same file on the network. Only the local instance is returning any
tables and letting me access any data.
I still have an issue.
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:TEoaHEMmFHA.3472@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Please refer to the following articles for related information:
> 199131 HOWTO: Add a FoxPro Database to SQL Server as a Linked Server
> http://support.microsoft.com/?id=199131
> 207595 HOWTO: Do SQL Server 7.0 Distributed Queries With FoxPro .dbf Files
> http://support.microsoft.com/?id=207595
> The article provide T-SQL code example demonstrates how to set up and use
> distributed queries with FoxPro with OpenQuery and OpenRowset functions.
> It
> also demonstrates how to update a remote FoxPro table from SQL Server
> 2000.
> You can test this code in SQL Query Analyzer after you install the Visual
> FoxPro ODBC driver on a SQL Server 2000 machine to make sure it works
> fine.
> You will need to change the data source names and path to the FoxPro files
> as appropriate.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> 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.
>
|||Hello,
I have tested the issue on my side without any problems. To narrow down
the issue, you may perform the following steps:
Step 1: Download the OLE DB provider for FoxPro from the following
Microsoft Web site:
http://www.microsoft.com/downloads/d...D8F-2D58-491F-
A0FA-95A3289C5FD4&displaylang=en
Step 2: Removing the original driver entirely on your computer and
installing the downloaded driver.
When removing, please make sure you have removed all the old driver files
on the hard disk.
When installing, please select "Custom install" and install all files
includes the Sample database.
By default, the driver files are located in the "C:\Program Files\Microsoft
Visual FoxPro OLE DB Provider" folder.
Step 3: Create a new linked server called fox1 to link to the sample
database northwind in SQL server Enterprise Manager (EM).
The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
Provider\Samples\Northwind\northwind.dbc"
Step 4: Running the following code in Query Analyzer(QA).
select * from openquery(fox1, 'select * from customers')
If it works fine, continue with the following steps.
Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
Make sure you have full control on the folder.
Step 6: Create a new linked server fox2 in SQL EM to the shared file
northwind.dbc.
Step 7: Running the following code in Query Analyzer(QA).
select * from openquery(fox2, 'select * from customers')
This should work fine.
If you receive any error message, please post here the exact error message.
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Hi Sophie
thanks for the reply.
Having done as below, everything works fine against the local data source,
however when pointing at a shared folder I get the following message:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Invalid path or file name.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize
returned 0x80040e21].
Thanks
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:qqTe0AZmFHA.3672@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have tested the issue on my side without any problems. To narrow down
> the issue, you may perform the following steps:
> Step 1: Download the OLE DB provider for FoxPro from the following
> Microsoft Web site:
> http://www.microsoft.com/downloads/d...D8F-2D58-491F-
> A0FA-95A3289C5FD4&displaylang=en
>
> Step 2: Removing the original driver entirely on your computer and
> installing the downloaded driver.
> When removing, please make sure you have removed all the old driver files
> on the hard disk.
> When installing, please select "Custom install" and install all files
> includes the Sample database.
> By default, the driver files are located in the "C:\Program
> Files\Microsoft
> Visual FoxPro OLE DB Provider" folder.
>
> Step 3: Create a new linked server called fox1 to link to the sample
> database northwind in SQL server Enterprise Manager (EM).
> The provider name is "Microsoft OLE DB Provider for Visual FoxPro".
> The data source is "C:\Program Files\Microsoft Visual FoxPro OLE DB
> Provider\Samples\Northwind\northwind.dbc"
> Step 4: Running the following code in Query Analyzer(QA).
> select * from openquery(fox1, 'select * from customers')
> If it works fine, continue with the following steps.
> Step 5: Copy the sample database folder "C:\Program Files\Microsoft Visual
> FoxPro OLE DB Provider\Samples\Northwind" to a shared folder.
> Make sure you have full control on the folder.
> Step 6: Create a new linked server fox2 in SQL EM to the shared file
> northwind.dbc.
> Step 7: Running the following code in Query Analyzer(QA).
> select * from openquery(fox2, 'select * from customers')
> This should work fine.
> If you receive any error message, please post here the exact error
> message.
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> 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.
>
>
|||Hello,
I suggest that you perform the following steps to narrow down the issue:
Step1: Creating a linked server in Query Anaylzer(QA)
1. Please connect to SQL server as a domain\administrative user account and
run the following code in QA:
sp_addlinkedserver 'fox3',
'','VFPOLEDB','\\test\northwind\Northwind','northw ind.dbc'
Since the error is "Invalid path or file name", please perform the
following steps to verify the folder path:
a.Click Start > Run, type
\\test\northwind\Northwind
In the box.
b. Check the northwind.dbc file is in the open window.
Note: You need to replace the '\\test\northwind\Northwind' with your shared
folder path.
2. Run the following code to check if the linked server works fine:
select * from openquery(fox3, 'select * from customers')
If you still receive the same error message, please continue with the
following steps.
Step 2: Perform step 1 on another machine which has SQL server 2000
installed.
Check if the issue still exists when creating a linked server from another
SQL server 2000 machine. If so, the machine which stores the shared files
may have problems. To narrow down the issue, copy the shared files to
another machine then test again.
Step 3: Run FileMon and RegMon to check if there is a file or registry
problem.
1. Download them from the following web site:
FileMon:
http://www.sysinternals.com/ntw2k/source/filemon.shtml
RegMon:
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Perofrm the following steps:
a.Start FileMon to monitor the file system events.
b.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
c.Stop FileMon and save the log to a file named filemon.log.
d.Start RegMon to monitor the registry events.
e.Run the following code to repro the problem.
select * from openquery(fox3, 'select * from customers')
f.Stop RegMon and save the log to a file named regmon.log.
Check the filemon.log and regmon.log file to see if there are any related
errors. For example, "access denied" error. If so, grant full permission on
it and check the issue again.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Hello,
I have received the same error when SQL server is running under the Local
System account. After changing the startup account for SQL server to a
domain\administrator user account, it works fine.
Please check the startup account for SQL server on your side and make sure
it is running under a domain\administrator user account. You can change
the startup account in SQL Enterprsie Manager. To change the account:
right-click the SQL server instance, click Properties. Click the Security
tab.
For more information, refer to the SQL server Properties(Security tab)
topic in BOL. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||SQL Server is running under a domain admin account.
Pete
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:beq19OLnFHA.2700@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> I have received the same error when SQL server is running under the Local
> System account. After changing the startup account for SQL server to a
> domain\administrator user account, it works fine.
> Please check the startup account for SQL server on your side and make sure
> it is running under a domain\administrator user account. You can change
> the startup account in SQL Enterprsie Manager. To change the account:
> right-click the SQL server instance, click Properties. Click the Security
> tab.
> For more information, refer to the SQL server Properties(Security tab)
> topic in BOL. I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> 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.
>
>
|||Hello,
Based on my research, the issue may be caused by many factors. You may
perform the following steps to narrow down the issue:
Step 1: Configuring the VFP provider to run inproc.
1. Start Enterprise Manager
2. Drill down into server.
3. Drill down into Security/Linked Servers.
4. Right-click on Linked Servers and select New linked server
5. For Provider name, select Microsoft OLE DB Provider for Visual FoxPro
6. Click Provider Options
7. Check Allow InProcess checkbox. This will apply to any VFP linked
servers or distributed queries.
8. Click OK
9. Click Cancel, since you don't need to create a new linked server.
Restart SQL server. This step is important.
Step 2: Use SQL authentication to connect to the SQL server from the
client, and set the domain account used to start SQL Server to have Full
Control permissions in the share and folder on the machine where the data
is located.
Check the issue. I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.
|||Sophie:
I've followed this post with great interest as I battle the exact same
thing - simply pulling VFP data into a SQL Server session. My goal is
to have a stored procedure within SQL Server call a stored procedure
within VFP.
However, I'm not able to get OPENQUERY() and/or OPENROWSET() working
unless I'm logged into the machine that SQL Server resides. I've done
all the steps above and no go.
I'm left with a solution of:
1. Creating a mirrow table on the SQL Side
2. Have VFP triggers for add/delete/update simply push the data to
stored procedures on the SQL server.
3. Write a custom synchronization program to periodically make sure the
data is in sync.
This works, but takes a lot more work on my end.
*** Sent via Developersdex http://www.codecomments.com ***
Subscribe to:
Posts (Atom)