Hi, im trying to make a linked server with an oracle db. Here is the Tsql
code:
set nocount on
-- if exists must drop
IF EXISTS (select * from master.dbo.sysservers WHERE srvname = 'sena')
BEGIN
EXEC sp_droplinkedsrvlogin 'sena', NULL
EXEC sp_dropserver 'sena'
END
-- Create linked server
use master
go
EXEC sp_addlinkedserver @.server = 'sena', @.srvproduct = 'Oracle',
@.provider = 'MSDAORA', @.datasrc = 'bdsena'
go
EXEC sp_addlinkedsrvlogin 'sena', 'false', NULL, 'general', 'general'
go
SELECT * FROM OPENQUERY(sena, 'Select * From oferente')
set nocount off
When the analizer try to make the openquery i get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
The problem is that i have in the server the oracle client and i can connect
to the oracle database from sqlplus or activex (using msdaora)
Any idea?
Thanks,
Andres Chaves"Andres Chaves" <guest@.guest.com> wrote in message
news:#7OPittrDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, im trying to make a linked server with an oracle db. Here is the Tsql
> code:
> set nocount on
> -- if exists must drop
> IF EXISTS (select * from master.dbo.sysservers WHERE srvname = 'sena')
> BEGIN
> EXEC sp_droplinkedsrvlogin 'sena', NULL
> EXEC sp_dropserver 'sena'
> END
> -- Create linked server
> use master
> go
> EXEC sp_addlinkedserver @.server = 'sena', @.srvproduct = 'Oracle',
> @.provider = 'MSDAORA', @.datasrc = 'bdsena'
> go
> EXEC sp_addlinkedsrvlogin 'sena', 'false', NULL, 'general', 'general'
> go
> SELECT * FROM OPENQUERY(sena, 'Select * From oferente')
> set nocount off
> When the analizer try to make the openquery i get the following error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle client and networking components
> were not found. These components are supplied by Oracle Corporation and
are
> part of the Oracle Version 7.3.3 or later client software installation.
> Provider is unable to function until these components are installed.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> The problem is that i have in the server the oracle client and i can
connect
> to the oracle database from sqlplus or activex (using msdaora)
> Any idea?
> Thanks,
> Andres Chaves
>
Did you try to connect to the Oracle from sqlplus using SQL Server's Windows
NT account?
You might need to install Oracle client one more time.
Regards,
Igor Raytsin
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Wednesday, March 28, 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 ***
Friday, March 9, 2012
Linked Server and Verify Connection
I am adding a linked server to another SQL server within a stored procedure.
I need to verify the link was made before I start running any more code. How
can I verify that I have connected successfully to the linked server?
Matt,
When you add a linked server, you do not make a connection, but
instead just provide the details needed so a connection can be made to
execute a query that refers to the linked server. Basically there is no
"open a connection for the code that follows" statement in SQL. I'm not
quite sure why you would want to add a linked server within a stored
procedure - do you drop the linked server in that procedure as well, so
that repeated calls to the procedure won't try to create an existing
linked server?
It would help if you could give more information about what you are
trying to accomplish.
Steve Kass
Drew University
Matt Tapia wrote:
>I am adding a linked server to another SQL server within a stored procedure.
>I need to verify the link was made before I start running any more code. How
>can I verify that I have connected successfully to the linked server?
>
>
I need to verify the link was made before I start running any more code. How
can I verify that I have connected successfully to the linked server?
Matt,
When you add a linked server, you do not make a connection, but
instead just provide the details needed so a connection can be made to
execute a query that refers to the linked server. Basically there is no
"open a connection for the code that follows" statement in SQL. I'm not
quite sure why you would want to add a linked server within a stored
procedure - do you drop the linked server in that procedure as well, so
that repeated calls to the procedure won't try to create an existing
linked server?
It would help if you could give more information about what you are
trying to accomplish.
Steve Kass
Drew University
Matt Tapia wrote:
>I am adding a linked server to another SQL server within a stored procedure.
>I need to verify the link was made before I start running any more code. How
>can I verify that I have connected successfully to the linked server?
>
>
Subscribe to:
Posts (Atom)