Multiple database queries

VBJoe
01-11-2004, 01:39 PM
In the Tutor's Corner forum, there's an example of how to export directly from one database format to another database format:

http://www.visualbasicforum.com/showthread.php?threadid=17524

I was wondering if anyone knows how to do this to convert from Visual FoxPro to SQL Server 2000. Or if it can even be done.

And before anyone suggests a DTS package... simply put, I can't.

Gardener
01-11-2004, 02:53 PM
In the Tutor's Corner forum, there's an example of how to export directly from one database format to another database format:

http://www.visualbasicforum.com/showthread.php?threadid=17524

I was wondering if anyone knows how to do this to convert from Visual FoxPro to SQL Server 2000. Or if it can even be done.

And before anyone suggests a DTS package... simply put, I can't.

If you can use sql queries on both databases then couldn't you write a program in vb with 2 recordsets, one on each Database. Then populate the second recordset with the data from the first?

VBJoe
01-11-2004, 03:03 PM
I could do that easily, but I'm converting 1000's of databases, all of which have hundreds of tables. Some of those tables have 1,000 or more records. All of these databases are being consolidated into a single SQl Server 2000 database, and this process will occur once a week.

Let me explain a little more about what I'm doing.

The FoxPro database is being used by an application that won't be abandoned anytime soon, but new applications are being written to use the data. We want these applications to use SQL Server. Instant, real-time data isn't important, so we'll be converting the data from FoxPro to SQL Server 2000 every weekend. I want this to be an automated process, and because new FoxPro databases are added every day, I'm trying to stay away from using a DTS package that I have to update every week manually. Instead, I'll check the directory where the FoxPro databases are located for new subdirectories.

Masterwiz
01-11-2004, 03:22 PM
Are all db's same kind? They have same columns?

Masterwiz
01-11-2004, 03:30 PM
There are some examples in SQL Server 7.0 install-CD. I think MS SQL Server 2000 have also those examples.

In SQL7 the path is \devtools\samples\dts.

There could be answer to your question.

VBJoe
01-11-2004, 05:29 PM
Are all db's same kind? They have same columns?For the most part, all the FoxPro databases are the same. But there are several databases with a different model, and these will need to be converted also.

There are some examples in SQL Server 7.0 install-CD. I think MS SQL Server 2000 have also those examples.

In SQL7 the path is \devtools\samples\dts.

There could be answer to your question.
I can't use a DTS package. There is the potential to have 100+ FoxPro databases added every month. I don't want to manually add all of these to the DTS package. I'm being paid to write an automated process. If I have to do work later on updating the DTS package every time a database is added, I'm losing money.

Optikal
01-11-2004, 05:39 PM
You can use the ADOX libraries to discover the tables/schema at runtime. Then you should be able to use that information to programmatically build your SQL statements.

VBJoe
01-11-2004, 05:48 PM
You can use the ADOX libraries to discover the tables/schema at runtime. Then you should be able to use that information to programmatically build your SQL statements.I've already got the SQL database built, and it matches the schema of the FoxPro database(s).

By question is: what sort of query can I write that will transfer the data from the FoxPro database to the SQL Server database. Here's what I have, but I get an 'Invalid Object Name' error:

'DECLARE DATABASE OBJECTS:
Dim sql As String, cmd As New ADODB.Command
Dim sqlCN As ADODB.Connection, foxCN As ADODB.Connection

'CREATE INSTANCES:
Set sqlCN = New ADODB.Connection
Set foxCN = New ADODB.Connection

'OPEN CONNECTIONS:
sqlCN.Open "Provider=sqloledb;Data Source=TestServer;Initial Catalog=Test;User Id=sa;Password=password"
foxCN.ConnectionString = dat.cn.ConnectionString 'dat is a Data Environment
foxCN.Open

'BUILD QUERY:
sql = "SELECT * INTO [tblcomfld] From [Visual Foxpro;DATABASE=C:\temp\PDatabase\data1.dbc].[tblcomfld]"

'SETUP COMMAND OBJECT:
Set cmd.ActiveConnection = sqlCN
cmd.CommandType = adCmdText
cmd.CommandText = sql

'EXECUTE QUERY:
cmd.Execute

If I comment out the cmd.Execute, both connections are opened successfully, and I can run queries on them without a problem.

Optikal
01-11-2004, 05:52 PM
I've never tried selecting from a different database like that, so I dunno if thats possible or not. It would depend on whether that particular OLE DB provider supported it or not.

I was thinking you could just pull all the fox pro data down into a recordset, then use CREATE TABLE and INSERT statements to populate your SQL Server from it.

VBJoe
01-11-2004, 05:53 PM
I've never tried selecting from a different database like that, so I dunno if thats possible or not. It would depend on whether that particular OLE DB provider supported it or not.

I was thinking you could just pull all the fox pro data down into a recordset, then use CREATE TABLE and INSERT statements to populate your SQL Server from it.And that's probably how I'll end up doing it. I was just looking for a speedier method.

Thanks for your help. :)

Shurik12
01-11-2004, 06:05 PM
Hi,

If I'm not mistaken this trick is possible between two Access databases.
If so won't linking the tables from MS SQL and FoxPro to it and the running INSERT INTO sql be an option for you?


Regards,
Shurik.

Optikal
01-11-2004, 06:08 PM
using that idea wouldn't he have to update the mdb every week manually?

Shurik12
01-11-2004, 06:13 PM
surely not, for the tables are linked to Access, so any change
in tables in MS SQL or Foxpro tables are "propagated" to Access

Optikal
01-11-2004, 06:15 PM
yeah, but he said new tables (databases) are created on a daily basis. So wouldn't the mdb need to be updated to link in these new tables/databases?

Shurik12
01-11-2004, 06:15 PM
surely not, for the tables are linked to Access, so any change
in tables in MS SQL or Foxpro tables are "propagated" to Access

EDIT: I have this kind of situation at work (Access-Oracle)

VBJoe
01-11-2004, 06:15 PM
surely not, for the tables are linked to Access, so any change
in tables in MS SQL or Foxpro tables are "propagated" to AccessI'd end up having thousands of linked tables, and I would have to update the Access database every time a new FoxPro database was added. The process needs to be automatic.

Optikal
01-11-2004, 06:17 PM
It also seems pretty strange to have new tables/databases being created every day. Any possibility of changing the design of whatever is doing that to use a more normalized database design that doesn't require new tables be created on the fly?

Shurik12
01-11-2004, 06:26 PM
Agree, guys, I did not take into account new databases that will be added...
On the other hand, as Optikal mentioned it's possible to get tables/schema at run-time, plus the linking can be done in code too..
Well probably it's getting too complicated...

VBJoe
01-11-2004, 06:28 PM
It also seems pretty strange to have new tables/databases being created every day. Any possibility of changing the design of whatever is doing that to use a more normalized database design that doesn't require new tables be created on the fly?Man, I wish I could. :( The application was written to track sales data, but it was designed to track large amounts of data for large clients of the company I'm doing the work for. The problem is, many small clients have been added, and continue to be added. This crappy FoxPro application actually creates a new database every time a client is added to the system, and I've run into some serious problems with bloat. The file system that stores all these databases has literally hundreds of thousands of folders, sub-folders, etc.

I've spoken with the developer of the application, and it's clear that he has very little formal software development or database training. He's essentially a sales guy with some tech experience, and this company has paid him a lot of money for a very poor product. Eventually, I hope to rewrite the application in .NET, but for now I have to live with what I have.

Optikal
01-11-2004, 06:31 PM
well, if theres any possibility of changing the application, it should be possible to just use one table with a ClientID field in there, rather than a separate table for each client...

VBJoe
01-11-2004, 06:31 PM
Agree, guys, I did not take into account new databases that will be added...
On the other hand, as Optikal mentioned it's possible to get tables/schema at run-time, plus the linking can be done in code too..
Well probably it's getting too complicated...The fact that new databases are added is definitely a problem. If the current database setup was static, I could write a DTS package and set it up on a schedule. I have a feeling I'm in for a long, torturous (and profitable) project... :)

VBJoe
01-11-2004, 06:33 PM
well, if theres any possibility of changing the application, it should be possible to just use one table with a ClientID field in there, rather than a separate table for each client...Luckily, the tables contain a fairly decent primary key setup. So the thousands of FoxPro databases can be imported directly into a single SQL Server 2000 database without many problems.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum