Gentlemen, Just a little tester for you at the end of the week.
I am trying to import data from a live VFP databases into some software written in C# which has worked fine using the VFPOLEDB interface. Now, if possible, I want to set up a linked database in M$SQL Server so I can access the data from the same database directly in a TSQL stored procedure and I have had partial success.
Using SQLManagement Studio I have set up the VFPOLEDB software on the SQL Server and under ServerObjects/LinkedServers/Providers can see the SQLOLEDB provider - so far so good.
I have also set up a linked server definition as follows:
EXEC master.dbo.sp_addlinkedserver @server = N'SQL2012_DEV_Flexispec', -- Your linked server name here @srvproduct=N'Visual FoxPro 9', -- can be anything @provider=N'VFPOLEDB', @datasrc=N'c:\flexispec_temp\data\flexdata.dbc', @provstr=N'VFPOLEDB.1'
This works and I can see above the linked server in the LinkedServers section but I can't link to it at all (Right click on Linked server and 'test connection' returns back SQL Error 7302 -> Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "SQL2012_DEV_Flexispec".
So, my hope of running the following statement: select * from SQL2012_DEV_Flexispec...Customers (yes you need 3 x full stops is what you have to format the code as) is obviously not working, returning back the same error about the creation of the OLEDB Instance.
I am beginning to doubt my sanity here as I have trawled all the net looking for a solution, to no avail. I have changed the options in the Windows Component services as per one post and allowed "In Process" creation against the Provider Properties for the OLEDB.
I am beginning to wonder if the 64bit SQL Server doesn't want to play nicely with the VFP OLEDB 32 bit drivers but can't prove it.
Any ideas before you go home?
Dave
On Fri, Jul 14, 2017 at 8:34 AM, Dave Crozier DaveC@flexipol.co.uk wrote: [snip]
I am beginning to wonder if the 64bit SQL Server doesn't want to play nicely with the VFP OLEDB 32 bit drivers but can't prove it.
This sounds suspicious.
I've run into issues before with 64-bitness not interoperating with 32-bit VFP. I'd dig around the documentation to see if this is supported.
Any ideas before you go home?
Sorry, I'm already home.
Thanks Ted.
From a couple of posts I have found they recommend setting up a 32 bit server, linking this to the VFP tables as a linked server then linking from the 64 bit server back to the 32 bit linked VFP server.... Definitely not a job to do on a Friday, it can wait until I feel somewhat fresher and up for a challenge on Monday!!
Life is never easy!
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Ted Roche Sent: 14 July 2017 13:57 To: profox@leafe.com Subject: Re: VFP OLEDB in M$SQL Linked Server
On Fri, Jul 14, 2017 at 8:34 AM, Dave Crozier DaveC@flexipol.co.uk wrote: [snip]
I am beginning to wonder if the 64bit SQL Server doesn't want to play nicely with the VFP OLEDB 32 bit drivers but can't prove it.
This sounds suspicious.
I've run into issues before with 64-bitness not interoperating with 32-bit VFP. I'd dig around the documentation to see if this is supported.
Any ideas before you go home?
Sorry, I'm already home.
[excessive quoting removed by server]
A heads up on calling code via a linked server.
You may have to do a call via OpenQuery
Select * from OpenQuery( VFPOLE, ' Select * from ' [SERVER_Name].VFPDatabase.Table where bla bla bla ' )
Or you may have to just use [ ] brackets around your server name and not do an OpenQuery() call.
It
On Fri, Jul 14, 2017 at 8:09 AM, Dave Crozier DaveC@flexipol.co.uk wrote:
Thanks Ted.
From a couple of posts I have found they recommend setting up a 32 bit
server, linking this to the VFP tables as a linked server then linking from the 64 bit server back to the 32 bit linked VFP server.... Definitely not a job to do on a Friday, it can wait until I feel somewhat fresher and up for a challenge on Monday!!
Life is never easy!
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Ted Roche Sent: 14 July 2017 13:57 To: profox@leafe.com Subject: Re: VFP OLEDB in M$SQL Linked Server
On Fri, Jul 14, 2017 at 8:34 AM, Dave Crozier DaveC@flexipol.co.uk wrote: [snip]
I am beginning to wonder if the 64bit SQL Server doesn't want to play
nicely with the VFP OLEDB 32 bit drivers but can't prove it.
This sounds suspicious.
I've run into issues before with 64-bitness not interoperating with 32-bit VFP. I'd dig around the documentation to see if this is supported.
Any ideas before you go home?
Sorry, I'm already home.
[excessive quoting removed by server]
I don't have any VFP to test on my side. I set up linked servers way too often. First guess is the 64bit-32bit issue. Second is SECURITY needing to be set.
I am downing a db server this weekend and today is massive test day for the replacement being correctly set. Oh, the joys of vendor upgrades on a product that has been in place for 8 years.
On Fri, Jul 14, 2017 at 7:34 AM, Dave Crozier DaveC@flexipol.co.uk wrote:
Gentlemen, Just a little tester for you at the end of the week.
I am trying to import data from a live VFP databases into some software written in C# which has worked fine using the VFPOLEDB interface. Now, if possible, I want to set up a linked database in M$SQL Server so I can access the data from the same database directly in a TSQL stored procedure and I have had partial success.
Using SQLManagement Studio I have set up the VFPOLEDB software on the SQL Server and under ServerObjects/LinkedServers/Providers can see the SQLOLEDB provider - so far so good.
I have also set up a linked server definition as follows:
EXEC master.dbo.sp_addlinkedserver @server = N'SQL2012_DEV_Flexispec', -- Your linked server name here @srvproduct=N'Visual FoxPro 9', -- can be anything @provider=N'VFPOLEDB', @datasrc=N'c:\flexispec_temp\data\flexdata.dbc', @provstr=N'VFPOLEDB.1'
This works and I can see above the linked server in the LinkedServers section but I can't link to it at all (Right click on Linked server and 'test connection' returns back SQL Error 7302 -> Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "SQL2012_DEV_Flexispec".
So, my hope of running the following statement: select * from SQL2012_DEV_Flexispec...Customers (yes you need 3 x full stops is what you have to format the code as) is obviously not working, returning back the same error about the creation of the OLEDB Instance.
I am beginning to doubt my sanity here as I have trawled all the net looking for a solution, to no avail. I have changed the options in the Windows Component services as per one post and allowed "In Process" creation against the Provider Properties for the OLEDB.
I am beginning to wonder if the 64bit SQL Server doesn't want to play nicely with the VFP OLEDB 32 bit drivers but can't prove it.
Any ideas before you go home?
Dave
[excessive quoting removed by server]
-
Dave Crozier -
Stephen Russell -
Ted Roche