May 8, 2006 at 3:51 am
Hi,
I see a lot of threads on these forums relating to getting info out of a foxpro database up into an sqk server database... but, how about in the other direction?
I have a legacy system that needs a feed from an sql server database...
I presume it could be done via DTS.. but (for reasons I don't want to bore you with) it would suit me better to be able to do it in a stored proc...
Can it be done as, say, an insert ? I'm thinking in terms of a cross database query...
INSERT INTO [foxprodatabase.tablename]
SELECT stuff FROM dbo.MYTABLE
WHERE ....
Am I dreaming?
Failing that... a BCP statement maybe?
Thanks
Vida
May 8, 2006 at 6:35 am
If memory serves, most versions of FoxPro can import CSV files, so you could BCP out to one of those. I also seem to remember that it required quotes around the strings, so you'd probably want to set up a format file for this.
May 8, 2006 at 7:08 am
Yeah...
I was kinda hoping for a more automatic kinda result... the CSV file would need to be imported into FoxPro...
I've been digging around on the web sice I asked the question and found an interesting article on Linked Servers... which really seems to be more like what I'm after...
FYI: http://www.informit.com/articles/article.asp?p=21644&rl=1
vida
May 9, 2006 at 6:55 am
Hi Vida,
Be careful because linked servers often provide read only access when used in conjunction with SQL Server.
For example:
msdn.microsoft.com/library/en-us/dv_foxhelp/html/dggrfoledbproviderforvisualfoxpro.asp
This page states that the foxpro OLEDB provider is read only.
Shaun
Quis custodiet ipsos custodes.
May 9, 2006 at 7:48 am
Woo.. well spotted Shaun.. thank you!
vida
May 9, 2006 at 7:56 am
Vida,
What version of FoxPro are you using?
May 9, 2006 at 8:01 am
Vida,
Another option might be an application, Activex control, FoxPro project or vbscript (depending upon the tools available to you). DTS could invoke methods of an object that extracts data from SQL Server and updates the appropriate tables within the foxpro database.
Hope this helps
Shaun
Quis custodiet ipsos custodes.
May 9, 2006 at 8:07 am
Our Foxpro guy has just told me to give hima csv now anyway.. sigh...
Something to do with the headers that the OLE foxpro driver generates wouldn't be compatible... think there is something interchangeable between foxpro and dbase ?? and we are Really using dbase???(sorry have no experience of these database types).. basically he says I can't be sure the header will be right so just give him a csv .. he needs to reindex the file (?) once loaded anyway.. so he might as well...
So...anyway.. thanks for all the helpful suggestions folks!
vida
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply