Database to Database

[Turpis]

I need to be able (in a simple way) to read the entire contents of a table from one database into a dataset.table then take that dataset.table and stick it into a completely different database. Without knowing the fieldnames and such, so using CREATE and ALTER are out.

I have tried building a connection1 & 2, dataadapter1 & 2 and dataset1. dataadapter1.fill(dataset1.table) and then dataadapter2.update(dataset1.table) - hoping it was that simple but it didn't work...it didn't even register an error. Nothing is ever as simple as I want it.

Any ideas?

Charles
Quality Assurance/Developer

 

[ThatRickGuy]

Wouldn't it be cool if there was a simple way like that! Maybe there is and RiverGuy and Chiph will come to the rescue!

But the slow/complexe way would be to use a CREATE SQL command. You'll have to cycle through the columns to get column names and types to build the CREATE string, execute it, and then loop through the records to create an update string.

-Rick

----------------------

http://www.ringdev.com

 

[Turpis]

That was not the answer I was wanting to hear Rick!



Charles
Quality Assurance/Developer

 

[ThatRickGuy]

It shouldn't be that bad to write, heck you could even try using a stored procedure to make the create statement and then pass in the field names and types as parameters.

-Rick

----------------------

http://www.ringdev.com

 

[chrissie1]

Are they on the same SQL-server?

then this should do the trick.

select * into databasename1.dbo.tablename from databasename2.dbo.tablename

this will only work if datbasename1.dbo.tablename doesn't exist.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[chrissie1]

And BTW Rick I take offense.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ThatRickGuy]

I know I know, I was trying to figure out how I could drop your name in that post creatively, but then I got side tracked with work. By the time I looked back at the page I had forgotten and just hit submit.

Next time I name drop, I'll be sure to put you at the top of the list

-Rick


----------------------

http://www.ringdev.com

 

[Turpis]

Actually no...SQL Server is not involved. Our MRP runs off of a Progress DB, and quite frankly blows. For reasons to lengthy to get into we are still likely a year or more away from moving to the latest version with does use a SQL Server backend. Not only that but the interface is awkward for many users who are not constantly using it. Over the last year, myself and the IT manager have been using linked tables to that Progress DB in Access to create custom apps, but the wait time for data is unreal, much as it is in our MRP thanks to Progress.

My plan is to create a windows service that hourly mines certain tables from Progress and gets that data to Access. So there maybe another solution to my problem that I am not seeing. I thought that just deleting the Access table and replacing it with fresh copies from Progress would be the easiest solution. The next solution I thought of was to truncate the table (those preserving the table structure and place new data) but I haven't seen a way to do this with VB.NET to Access.

So here is the 'other' idea I had, is it possible to do dataset 'syncing' without having to have a masters degree in ADO.NET. Obvious that I can check the recordset count against each other to see if any new records have been added in the last hour, but there is no timestamp or autosequence index, so how would I go about 'finding' and transferring just the new recordsets. (crossing my fingers that there is already some type of dataset syncing feature in ADO.NET [yeah right])

Or should I just live with long data retrieval time by connecting my little custom apps directly to Progress. We do this now.

Charles
Quality Assurance/Developer

 

[ThatRickGuy]

As for the Truncating idea, you can use a "TRUNCATE [TableName]" SQL command to clear the access database table.

I know your pain though. We have an OMD system running on a 6 year old IMB server. I ran what should have been a 45 second report (going against the same data/structure in SQL Server) that took 8+ hours.

-Rick

----------------------

http://www.ringdev.com

 

[Turpis]

I tried passing the TRUNCATE as a SQL command through an ODBC dataadapter and it said you can only use INSERT, CREATE, DELETE, blah blah Error.

Charles
Quality Assurance/Developer

 

[ThatRickGuy]

You can use "DELETE FROM [TableName] WHERE 1=1"

not quite as fast or efficient as TRUNCATE but acheives the same end result.

-Rick

----------------------

http://www.ringdev.com

 

[RiverGuy]

Your DataSet should have all your column names and defs. You just need to make a function that takes in a DataTable, examines the columns, and prepares a DDL string that will be executed on the other database.

You know,something like:

For Each C As DataColumn In MyTable.Columns
MyString += ", " & C.Name & " " & C.DataType.ToString()
Next

DataType.ToString() might not output the exact word you need, but you can translate all the types.

Regarldless....its going to come down to making an SQL Statement to create a new table. You will generate that SQL statement based on the definitions you have on hand.

 

[Turpis]

Thanks all, guess this is going to be a bigger project than I envisioned...oh well, scope-creep is my friend.

Charles
Quality Assurance/Developer