I have a recordset in my VB6 app that was created using ODBC and I would like to insert the data into a SQL db table. I already have the target Db & table setup. What is the fastest way to accomplish the data transfer? Can I do a Select into using the ODBC recordset as my source?
Thanks
Insert data to database foxpro using open query. I already create connection like below
@server = 'EXIMBIL_LINK',
@provider = 'MSDASQL',
@srvproduct = '',
@provstr = 'Driver={Microsoft Visual FoxPro Driver}; UID=;SourceDB=D: raining_testdata;SourceType=DBF ;Exclusive=No;BackgroundFetch=Yes'
when i try to show table van with command :
select * from openquery(EXIMBIL_LINK,'select * from van')
It show query running well but when i try to insert new row to table van with command :
INSERT into OpenQuery(EXIMBIL_LINK, 'SELECT c_user_id,c_user_name,full_name from van')
VALUES('1000007668','IDMTEST2','IDMTEST2')
throws error like this : Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done
Hi all,
This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro.
Please help me.
Thanks in Advance.
Regards,
Rajeev Vandakar
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
I found toll for migrating FoxPro( 2.6 and 3.0) databases to Oracle but I need one
for FoxPro DB to MS-SQL. Any idea where to find it( if there is any ?)
Thanks
Hello everyone,My company uses a FoxPro database right now as an interfaceand a database. For our situation, I have come to the conclusion thatit would be a better choice for us to move to an SQL server of somesort. I have been given the task of overseeing the overhaul on theprogram. I am paranoid about security and uptime, and so is the CEO andthere is more and more demand for the company to get on the interactiveinternet. I'd like our clients to be able to submit data to ourdatabase and pull data from it (only certain data of course). My ideais to convert the FP tables to and SQL server and write an internalapplication(or web-based - advantages? I dunno) for the interface. Forthe internet side of things, my idea is to have seperate web database(SQL) that will put information from web clients. Through the internalinterface, internal users would then be able to pull data from the webdatabase to the internal SQL. And through the internet (authenticatedof course), the web users would pull data though the web database, whopulls information from the internal SQL database. Would someone pleasetear this idea apart w/ advantages and disadvantages. Also, if this isthe best route, tell me how I can sell this idea to my boss. What's sogood about using SQL vs. FP over the internet? What about internally?What about security? Cost is going to place a big role on the what theCEO decides, unless I can sell him otherwise. Should I tell him that weshouldn't do it now and save some money to do it right? Or what? Somehelp please. Thanks.Alex
View 12 Replies View RelatedAny suggestions on the least slowest way to import a large Foxpro table file into SQL Server? I'm looking for ANY suggestions. I'm not sure if I changed a setting, but I get an "out of disk space: can't create file c:emplahblah.xxx " error message when I try to run my standard data pump DTS (with a Fox ODBC datasource to a SQL table, no tranformations, straight copy). Thanks for the help.
Jim
Hello, all. I need to migrate a visual Foxpro database to SQL server. Is there a way I can import the visual Foxpro data files into Enterprise manager?
Thanks in advance.
Hi,
There is an issue at my company that creates a lot of confusion. Some people blame the slowness of queries on the FoxPro database, so we're considering to migrate to MS SQL Server.
What is the real difference between MS SQL Server and MS FoxPro?
Isn't MS SQL Server supposed to be faster? (It's a high-performance and very mainstream database, where I personally never knew anybody who used FoxPro). Some people claim that FoxPro performs faster in benchmarks. Is it true? If it is, why don't most people use FoxPro?
Also, when buying the database software for the server, which one is more expensive?
I would appreciate any pointers. Thanks.
Hello,
I'm looking to migrate a visual foxpro database to SQL server. Before I map and migrate over to the new system I need to get an understanding of the structure of the old one and cleans the data. Unfortunatley I don't have any of the orginal design documention. Is there a way I can import the visual foxpro data files into Enterprise manager? Or what method would you recommend to first get an understanding of how the database works?
Thanks for any help you can give me.
Andy
I want to send parameters to a DTS (Foxpro to SQL 2000). I got it figured out with a DTS(Exel to SQL 2000) but i can't find a way to get the thing to work for foxpro.
I want to do this because i have a stored procedure that calls the xp_cmdshell function and then by running the dts and just passing a parameter to it for the old order i want in my new application database...
Please any help would be appreciated......
I'm trying to export the data from sql to foxpro dbf file. I can't seem to find the OLE in 2005 any where. Can some one give me the direction how to export and connect to foxpro dbf file from import/export wizard
thanks
Is it possible to connect virtual foxpro v6.0?
View 5 Replies View RelatedI have 2 packages, one to import data from Foxpro to Sql and another to send the data back from Sql to FoxPro.
The time it takes to execute the package that moves 2 million rows of data from FoxPro to Sql takes about 18 to 25 mins. Not bad. However, moving approximately the same number of rows from Sql back to FoxPro takes about 8 hrs. Not acceptable. I have tried several things with no luck.
Any advice would be appreciated.
V
Hello,
I want to know if there is an way to SET an index when send a query using the OpenQuery function to a FoxPro database using a linked server in the MS-SQL that points to a System DSN with the MS ODBC Driver to FoxPro.
Thank you in advance,
Aldair.
Hi,
Today was my first day to use DTS. I tried to transfer a Visual FoxPro Database to my SQL Server. I noticed that I am getting errors on the data that contains date fields. So, for testing purposes I transformed the Visual FoxPro fields that contained datefields into char fields. And this works, but obviously I cannot hunt around in a huge database looking for datefields in tables and change them manually. Plus, by changing to char fields I noticed that those fields that are empty are transformed as 1899-12-30. :p
Does anyone have any ideas?
Thanks,
Laura
I have to migrate Foxpro database ( DOS and windows versions) to SQLserver. Could you please someone write me, any measures that i would need to consider in migration?
View 1 Replies View RelatedHi,
I've got about 6000 tables in foxpro 3.0 and a database in sql srv 2000.
now i've to convert the data of these 6000 tables into the database. the problem is that i have to convert all the tables seperatly!
is there a tool where i can convert these tables automatically?
i know it can with DTS, but the 6000 tables are divided over 1400 directories. So with DTS i can't convert them automatically.
does anyone know a solution?
I have a standard adjacency matrix recursive table.
XOrderChildParent
1100NULL
2101100
3102100
4103100
5104102
6105102
7106NULL
8107106
The problem is the rows are in a random order and XOrder is not populated. I need to populate XOrder such that when the table is ordered by XOrder it will be in the order above.
I originally did this in FoxPro. Being able to SCAN through records in a table made it easy. SQL however is a different story. What I did in Fox was
1. Indexed on XOrder.
2. Set XOrder to 1 for all Parent = NULL.
3. This placed all the parents at the top of the file.
4. This is where Fox has a SCAN command that would sequentially step through the file from top to bottom. Starting from the top of the file…
5. Find the children of this row.
6. Set their XOrder to the current XOrder+.5. This placed the children under the parent.
7. Sequentially number all remaining records starting from the XOrder of the current record.
8. Now the current record and the record immediately below it have the correct XOrder.
9. Next SKIP to the next record
10. Replete from step 5. This would be the end of the Fox SCAN loop and would automatically repeat until it reached the end of the table then exited the loop.
This will correctly set XOrder for the entire table.
The problem is there is no SCAN or SKIP in SQL. How would I accomplish this in SQL?
Hi all, I'm having issues with a FoxPro linked server.
I've set up a linked server to a FoxPro dbc using the Microsoft OLE DB Provider for Visual FoxPro. When I'm on Management Studio on my server the link appears to be working fine and a stored procedure I've created to get the indo from the dbc and put it into a temp table works fine.
However, when I try to execute the sp on management studio on my local machine I get the following error:
OLE DB provider "VFPOLEDB" for linked server "tern" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Procedure usp_SSRS_007, Line 28
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "tern".
And I also get a similar error when I try to test the connection of the linked server on my local machince.
This is now driving me nuts , so many many thanks in advance for any help!!!
I need help to import data from FoxPro tables to SQL server 2005. I want to be able to use SSIS Pkgs in 2005.
We already have Foxpro Drivers installed on the 2005 Server. Pl let me know what data source connections shud I use to be able to extract data from Foxpro .dbf tables into sql server 2005?
Hallo,
I have a question and hope that someone can help me.
I use of accountview application and now that are stored in the Foxpro database. I want to copy the data in my sql server, but not with ODBC but with SSIS. But I dotnow how this works. Can someone explain me step by step?
if I with the OBDC do see only virtual tables and I do not want that. I want the complete bases tables in my sql-server copying
Please Help me!!!!!!!!!
Hello,
I have a question and hope that someone can help me.
I use of AccountView application this application use of the Microsoft Visual FoxPro database. We have on another server Sql-server. Me question is how I am possible foxpro and Sql-server to each other cross-belt Len (linken)
Or can I import the database from foxpro in sql-server?
Thanx
Does anyone know how to attach a Foxpro database to SQL Server Express?
Thanks,
Grant
Does anyone know how I can, using SQL and/or T-SQL syntax (although I could do a CLR in C# too..), export a SQL table from SQL Server 2005 to a new Visual FoxPro 9 DBF (table)? The DBF would need to be created on the fly too because this will have to basically be able to export any SQL table...
Thanks ahead of time for any and all assistance...
Hi all, I'm having issues with a FoxPro linked server.
I've set up a linked server to a FoxPro dbc using the Microsoft OLE DB Provider for Visual FoxPro. When I'm on Management Studio on my server the link appears to be working fine and a stored procedure I've created to get the indo from the dbc and put it into a temp table works fine.
However, when I try to execute the sp on management studio on my local machine I get the following error:
OLE DB provider "VFPOLEDB" for linked server "tern" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Procedure usp_SSRS_007, Line 28
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "tern".
And I also get a similar error when I try to test the connection of the linked server on my local machince.
This is now driving me nuts, so many many thanks in advance for any help!!!
I know that the DTS Wizard is supposed to be able to handle heterognous
data imports but I can't get it to work with a free FoxPro table. I have to export to a text file and then import from the text file and spend an hour renaming columns and farting around with datatypes.
CAN I GET A FOXPRO TABLE INTO SQL SERVER DIRECTLY OR NOT?
HOW DO I DO IT?
I don't fully understand some of the questions the DTS Wizard is asking. Can anybody give me a blow by blow account before I ring the Samaritans?
Thank you and Happy Christmas - it may be my last if I can't speed up these imports. Either I'll jump under a bus or my boss will make mince pies out of me.
Thanks
Mark
i have an old database in foxpro. The table in foxpro now has been broken into more than tables in sql server 6.5 . how do i append the data to sql server database to the respective tables from the foxpro database.
vineet
Hi,I have a large FoxPro table with an index that I need to be Queried from SQLServer by OLE.DB or ODBC. If I query the DBF directly a search takes 1Minute +. Is there a way I can call the data from the table and use theexisting FoxPro Index?ThanksSteve
View 1 Replies View RelatedHi everyone,
I have worked soley with Sybase for almost ten years! Now I have been tasked with converting a bunch of data currently in a FoxPro database to a Microsoft SQL Server database. Short of writing some routines myself (which I don't mind doing), is there a shortcut for doing this? Any specifics would be great as it seems this could be mind boggling as far as dates go and so many other things!
Thanks so much!
Rachel
Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ????
thanks kindly
My basic proccess is as follows: The source foxpro database has hundreds of dbf files ---the SSIS procress is as follows
1: Copy only required dbf files and/or matching .fpt files over to local drive on SSIS box(we only need 10 files)
use MS Visual Foxpro ODBC driver (free table option) and set up a system DSN--dataflow tab---datareader source--and away you go
Good day,
Current Setup:
Using:
Visual Web Developer Express
SQL Express
IIS 6.0 with >net 2.0 Installed
Visual Foxpro
I actually have two questions (but am making good progress so will focus on the issue at hand).
1. I setup a Login.aspx / Default.aspx / Register.apsx / Membership.aspx as specified in the examples in MSDN...all is working ok.
2. When I register a new user, I wanted to capture their Customer ID during login to store in their Membership view table. I did this and is working ok....and to make it easy, am storing the value in Comments field.
3. I want to query a Visual Fox free table (DBF) that contains my client's Customer Invoice data...and have the ODBC Connection setup and working properly.
Here's the problem....
I'm having trouble formulating the SELECT string within the asp code to capture the Membership.Comments field.
I need the proper functions to grab the Comments field data and then use it within my select statement.
Presently, the query works if I just hard-code the value i.e.
"SELECT * from [invdata] WHERE cus_no = '1' "
I need to replace the '1' with the comments field data.
Thanks:
On a side issue: it took me days to just to get this all working to where it is...does anyone have a detailed example of how to Login to a site and pass the parms to another page to query the Logged In user's data from a database......a real world example that I have yet to see highlighted anywhere in the Forums or books I've bought.
For example: I am customer ABC123, I log in to my vendor's site and want to see all my orders placed.