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......
Greetings,
I am unsure if this is the correct forum to send this question, but I can't seem to find any information regarding this problem. If this is the wrong place, please direct me to the correct spot.
I am attempting to import data from a free tables FoxPro database to SQL 2000 using a DTS Package which has worked correctly every day for the past 2 years. Yesterday, I got an error.
The package has around 10 tables that it deletes, re-creates, and populates with data from the Foxpro. All of the tables except one work correctly.
When I try to do an explicit import using the ODBC connection to populate that one table, I get the following error: Context: Error calling Openrowset on the provider.
I created an access database on my local computer and setup an ODBC connection and link tables to the database to see if it would work, and it did. So I thought there might be something wrong with the ODBC data source on the SQL Server, so I deleted it and created a new one, used it and I receive the same error.
I thank you in advance for any assistance or direction you can provide me for finding an answer.
Dear Sir/Madam,
I have some data which is stores in Foxpro dbf files & Access 200 MDB file. Can i extract import these files into SQL Server 2005
Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
View 1 Replies View RelatedThe DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0
View 1 Replies View RelatedI 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
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'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
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
View 1 Replies View RelatedIs 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