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 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 Relatedi have a old database in foxpro and it has to be converted to sql server 6.5 database . the table in the foxpro has been broken into more than 1 tables in the sql server . so how can i transfer the data from 1 foxpro table to different tables in sql server 6.5.
vineet
Hi 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
I'm trying to access Visual Fox Pro Data from Sql Server 2000, Cananyone have any Idea ?Thanks
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
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
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis?
I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis.
Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink).
Thanks for any and all assistance.....
Dave
The 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 RelatedMy vendor requires data to be sent in Excel format. Some of my tables have rows over 65,536 so I need to use Excel 2007 (Max of 1,048,576). Right now my data sits in SQL 2000. I am using MS SQL Enterprise Manager 8.0 to prepare the data. Is there some kind of add on or selection I am missing to use DTS to export from SQL to Excel 2007?Thanks in advance.
View 3 Replies View RelatedI have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
View 5 Replies View RelatedIs it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Thanks
Please give me a direction where to go to solve this problem...
our organization regularly pumps data from an AS/400 database (db2) to SQL Server 7 tables with the help of DTS. Some of the data transfers are straight column copy and several are ActiveX transformations. Also, we've got some of the transformations defined directly inside the select statement in the Source tab of the DTS designer. There is one stored procedure which collects and does computations with the data to put into a number of Excel reports. The strange thing is that some of the data seems to be getting corrupted somehow as it is transferred, because the resulting data in sql server is not identical to that which was transferred. We get outrageously high values for one of the columns with no apparent pattern. There are no error log entries for this, and we've already confirmed that all the transformation scripts and stored procedure functions that we've been using are correct. Would doing a server trace be of any help, and if so what should I be looking for and could you just brief me on how to start one, since I've never had the need to do one so far. Thanks for the help in advance.
we have 2 sql server 6.5 . i want to transfer the data of one to the other .both have the same database.
thank u
vineet
hi can somebody help me out on this.. is there a way to consolidate the data in different Sql Server 2005 database servers scattered at different locations into a single database without using REPLICATION.
p.s Data transfer should also be conditional and bi-directional
Ive have a website on SQL Server 2005 on my Localhost, that I need to transfer to a live test server, in MYSQL you can use dump to get the SQL script,can I do this in SQL server? If so how? if not how do I transfer the DB?
View 5 Replies View RelatedNew to sql scripting and am having a problem. I have two SQL servers:
1. Keeps track of all sales related activities.
2. I would like to use for backup and reporting of sales only.
I am wondering if there is a script that can be run to transfer only specific data from the first server to the second, and if so what is the setup and syntax.
I am looking to do something like:
insert into [database#2] [table]
* from [database#1] [table]
where database#1-table.result='Sale'
Please help...
Thanks.
I have an issue related to data migration.
We are developing a data migration tool that transfer data from the live sql server (used by customer) to another sql server.
we use SSIS packages to perform the migration. we have a engine (developed in c#) that reads the SSIS package and executes it.
Now, as the source sql server is live, it is certain to undergo changes during the data transfer operation. Changes would mostly be insert or update. After the data migration is completed the delta has to be taken care of.
I am looking for an efficient mechanism to transfer the delta from the live sql server to the target sql server.
HI,
Recently i copied data from one server to another server. Later i checked the data too. It was fine. I checked no of tables, views ,sps, users , roles and also permissions and no of records in each table.
Every thing looked fine, but my users are saying data got meshed up. I dont know why they are complainting like that? Can anyone tell me why it is happening. Did i made any mistake while copying data. i copied data using bcp.
Before that i copied structure without data using DTS.
pls tell me.
thank u.
--siva
Hi,
Which is the best way to transfer data from SQL server into Oracle?.
Thanks in Advance,
Ravi
My problem is aquisiton data with gdb extension.There is not driver of that type in DTS of SQL Server 2000.
Can anybody help??
Hi All:
I'm looking for some advice. We have multiple remote SQL Server 2005 systems at various clients that all contain the same WorkTrack database. We need to send this data on a daily basis from all of these remote servers to our main server (ChemWeb/Spindle) in our corporate office. The thing is that we need to do this over standard HTTP - no VPN connection, etc.
So I'm thinking of using SQL 2005 Web services to do such. Here's my plan (the name of the Database on the client side and server is WorkTrack, and SDP is the acronym I am using for a Spindle Data Packet which contains the data to be updated from the client)
1. Every day update a WorkTrack_SDP database on the client server that will contain all of the data from WorkTrack (name of Spindle Client Database) that has changed since the last SDP transfer. This WorkTrack_SDP database will only contain the tables from the WorkTrack database that are necessary for transfer to the ChemWeb/Spindle server
2. The WorkTrack_SDP database will have the same schema as the WorkTrack database with one additional Customer_No column in each table.
3. All of the tables in WorkTrack_SDP database will be truncated before insertion of updated data.
4. An acknowledgement of the previous SDP transfer and ChemWeb/Spindle Server update will be required before this process will proceed.
5. There will be an additional table in the Client WorkTrack database to track SDP transfers.
a. Date/Time range of updated data of each transfer
b. When the transfer was sent
c. When the transfer was completed.
6. Create SPROC’s on the ChemWeb/Spindle server to serve as SQL Endpoints (Web Services) to update/insert data from client systems to WorkTrack_SDP database on the ChemWeb/Spindle Server.
7. Create SPROC on ChemWeb/Spindle server that will transfer data from WorkTrack_SDP into WorkTrack
Any thoughts & comments are greatly appreciated.
TIA,
Ken Blum
Hello,
I am new to SQL server world.
I have a task to transfer data from excel sheet(s) to sql server.
I read I could do that using DTS.
But the problem is I have more than 1 excel files and in my table I need to get records from both the files with out duplication the data.
ex: one excel sheet conatins field 1,2,3
second sheet contains field 1,4,5,6 (now I should put data in the sql table as 1,2,3,4,5,6)
I will have to continue to do add data to this table as I get new excel sheets (the fields will remain same).
Can somebody help me with this?
If you can also give me some references for this it will be very helpful.
Thanks a lot in advance
Nicky
Hi All,
What is the best way to transfer data from a web-farm to another server for web-report?
Mirror?
Replication?
Daily Schedule job?
Other?
What are the advance and dis-advance of each type?
thanks
Hi
Can Anybody tell me the different ways of transfering data from ODBC Layer to SQL Server using C#.
I want to transfer using Threads,means by running 4 threads, I need to transfer 4 tables at a time.
Though we have SQLBulk copy Object in .NET it's not useful in multi threader env.
Can anybody help me in this.
Could any one help me in
Writing code/query to export simple English text data from MSSQL Sever2000 to FoxPro2.6 dbf
aamir_net68@yahoo.com
hello,guys,
my question is :
how do I query data from the linked foxpro database?
more:
I have linked a visual foxpro server to my sql server database by using the addserver clause.
Two server is in a local network.
My linked foxpro server named 'fox'.
its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:foxpro object.
In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'.
And the .dbf files are not in any database.
They are just three files in the same directory.
There is no dbc file.
Now I can see the table list on the right page of the linked server.
But there is something wrong with my sql clause.
sample:
select * from fox..sysu
then the message is:
server: message 7313，level 16，status 1，row 1
the appointed constructure or directory to the provider is inefficacious 'MSDASQL'
I know I may use 'openquery',or 'openrowset'.
The problem is that variable is not valid in 'openquery' and 'openrowset'.
But I must use variable.
so ,please give me some advice.
Thank you very much
Hello, I like to know how to transfer current data displayed in my datagrid into a table in sql server. Any help is much appreciated.Thanks!
View 1 Replies View RelatedI have a live server running windows Nt and sql server We brought a new
backup server of higher configuration we wanted to swap the data we installed nt and sql server in the new server will a cut paste of the data directory of the live server to the backup server is sufficient or the data directory to be restored to the respective files through sql enterprise manager please do clarify me
Regards
Ramesh
When executing an inline insert into command from isqw/w the command executes perfectly. Yet when I try to create a stored procedure with exactly the same insert into statement it will not create. It does not like the database.owner.table prefix on the selected rows. Any suggestions ??
Thanks.... Tom
Hi,
I'm new to SQL server, and I would like to achieve below tasks. Kindly
provide brief guildline on how to achieve these:
1.) I have managed to schedule job in SQL server to download the access database from remote server.
2.) Second, I would like to transfer all the data from my acccess database to my SQL tables.
May I know how do I achieve my task no 2???? I believe I need to write some script (procedure) in order to achieve this.
pls advise....
Hi All,1. i want to transfer the .csv file data into sql server table, itried with the DTS but while creating DSN it not prompt to attechthe .csv file. give me the proper steps to perform the datatransfer...2. i want result of my query into excel or text file by using the sqlquery( like Select * from employee where emp_salary>10000 to 'c:emp.xls).i know the other way right click into query analyzer windowand select option result to file, but want the result by using SQLquery.....
Quote:
Hi all,
Thanks in advance,
I have to transfer data from sql express 2005 to sql mobile(.sdf).
Actually i have a sql server express 2005 (sqlee) in laptop...
and the .sdf (sqlce) handheld device (ppc) that has been connected by activesync.
Can you please make it clear for my following questions?
1. Is it possible to write a query like " insert into ....sdf select from...sqlexp...." between 2 servers...?
2. Is there any copy command like a BULKCOPY .. to insert all records to .sdf (sqlce) from sql server express?
(or)
3. Is it correct to create a smart device application (CF 2.0) to transfer data between sql express and .sdf by using active sync...?
I would appreciate your comments..
Thanks,
Kumaran A