Illusion Posted May 6, 2004 Posted May 6, 2004 Hi, I have 2 databases, one in the form of a file (see the dbf post i made) and one connected to an SQL server, now I need to put data from both sources and do checking against the two before I can push the data back into the SQL server. I can do this through a bit of coding, but I was wondering if there is an easier way.. such as a Select between two datasets? .. Is this possible .. or is there another way which you could recommend..? Quote
Joe Mamma Posted May 6, 2004 Posted May 6, 2004 Hi, I have 2 databases, one in the form of a file (see the dbf post i made) and one connected to an SQL server, now I need to put data from both sources and do checking against the two before I can push the data back into the SQL server. I can do this through a bit of coding, but I was wondering if there is an easier way.. such as a Select between two datasets? .. Is this possible .. or is there another way which you could recommend..? Connect the FoxPro file to SQL server via a Remote Server setup, then review transact-SQL for how to query data accross servers. . . heterogeneous joins Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Illusion Posted May 6, 2004 Author Posted May 6, 2004 *looks blankly at you* huh? all I know is SELECT ... *sobs quietly in a corner* Quote
Joe Mamma Posted May 6, 2004 Posted May 6, 2004 *looks blankly at you* huh? all I know is SELECT ... *sobs quietly in a corner* Got SQL Enterprise Manager? Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
JABE Posted May 7, 2004 Posted May 7, 2004 I think Joe was referring to linked servers. Read up on sp_addlinkedserver on SQL Server help for more info. Once set up as a linked server, the dbf could be accessed from T-SQL code and can be JOINed to SQL Server tables. The help files provide good samples on these. Quote
Joe Mamma Posted May 7, 2004 Posted May 7, 2004 I think Joe was referring to linked servers. Read up on sp_addlinkedserver on SQL Server help for more info. Once set up as a linked server' date=' the dbf could be accessed from T-SQL code and can be JOINed to SQL Server tables. The help files provide good samples on these.[/quote'] yes thats what I meant linked server Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Illusion Posted May 7, 2004 Author Posted May 7, 2004 *looks at SQL manual* ..hmm ok. I kinda understand that .. but I don't understand the stored procedures ... I only use the view to make up my SQL code to use in VB.net .. So i'm still at a loss.. :( Quote
*Experts* Nerseus Posted May 7, 2004 *Experts* Posted May 7, 2004 You might look into using SQL Server's Data Transformation Services (DTS). If you have SQL Server Enterprise Manager, expand "Data Transformation Services". If your goal is to import data from some datasource (like FoxPro or DBIV or whatever - even Excel) into SQL Server, then DTS is probably the way to go. Start by right clicking the DTS "folder" in Enterprise Manager and selecting All Tasks->Import Data. You can pick your source (FoxPro) and destination (this SQL Server or another database - Access if you want). You can tweak the fields being copied and apply a script to each copied row. For example, say you have a "Name" field in your FoxPro database and you want to split that into "First", "Middle" and "Last" fields in SQL Server. The custom script allows you to use VBScript or Javascript (Maybe more, haven't done this in awhile) to tweak whatever data you want. As a default, the script will look something like: source["Field1"] = dest["Field1"] source["Field2"] = dest["Field2"] source["Field3"] = dest["Field3"] You could tweak it something like: Dim first as String Dim pos as Long pos = InStr(source["Field1"], " ") first = SubString(source["Field1"], 1, pos) dest["First"] = first source["Field2"] = dest["Field2"] source["Field3"] = dest["Field3"] You get the idea, hopefully... -nerseus Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
Illusion Posted May 10, 2004 Author Posted May 10, 2004 but the idea of having vb.net is the fact that all the data needs to be checked by other people before it is put into the live data.. otherwise there could be mass chaos .. i'm still unsure.. I mean I understand that code, thats easy.. but how it all fits together.. I think I will stick to pulling down information putting into a dataset and then pulling it apart left right and centre.. since thats the only way i know... :) Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.