Transferring data from Advantage Database Server to SQL Server

views:

591

answers:

+1 Q:

Transferring data from Advantage Database Server to SQL Server

My client is using Advantage Database Server and wants to move to SQL Server but obviously wants to move all his clients data as part of the upgrade over to SQL Server.

I've thought about writing an app to do the transfer but thinking it might be more trouble than its worth.

What would you recommend?

+2 A:

Haven't used (or heard of) Advantage Database Server, but I would:

setup a linked server instance in SQL Server that connects to the Advantage db. You'll need the OLE driver for Advantage db - this link might help.
Once that's in place, you can suck the rows out of the Advantage db and into the SQL Server ones without the need for scripts.

For a less permanent solution, you can use OPENROWSET to open a connection to the Advantage db. It uses the same OLE driver as you would with Linked Servers, but the connection is only open while you use it.

OMG Ponies 2009-12-08 21:14:46

Would this be automated some how? The users would not be able to do that themselves so I have to give some sort button to click and data transfer begins

Jon 2009-12-09 13:32:07

OMG Ponies 2009-12-09 17:21:30

Sorry, what I mean is the users will not be expected to open SQL Server and do the transfer. They need something to do it for them so I will need to write an app I think to create a linked server and then transfer the rows across

Jon 2009-12-09 17:33:27

+1 A:

You may also use SSIS, should you feel that you need more control over the ETL process, otherwise as OMG Ponies suggested. As Sybase advertises "zero-administration, no need for DBA" -- sooner or later you and your data grow out of this. There is an ODBC driver available, so it looks doable.

Damir Sudarevic 2009-12-09 01:22:31

+1 A:

I have switched between sql server and advantage server while developing applications and I have mainly used the import data function within the sql server management studio to do this. Basically create a database and import data using the oledb driver for advantage - available from the advantage database website. Try this link for more information . Going back again is a bit tricker.... but that is another story.

If it is just a one off transfer that should do the trick you will have a fair degree of control over transfer. Be warned that the information about PK and data relationships may not survive the transfer as at least with adt/adi files advantage database server does not keep a record of the relationships between tables. If they want to keep the advantage database running and keeping a copy of the data in both databases i have not tried to do. The suggestion from OMG Ponies may do the trick in that case.

delphigirl 2009-12-09 16:38:01

Thanks will look into it. The thing that makes it complicated is the userbase. Obviously they have no expertise so I will need to knock up an application I think to do the data transfer which I'm not lloking forward to!

Jon 2009-12-09 17:34:27

advantage database is often used in situations where low expertise is the rule because in general the database needs no maintenance as usually the amount of data stored is relatively low. I have a few years experience with advantage so if you need any help let me know. But do try the sql server import tool using odbc first as that might be at least give you the start of a data structure. If you are connecting to both databases in the same app use ado as both databases connect quite well using that technology. Look up the tdataset component for use with advantage in delphi not sure about c.

delphigirl 2009-12-10 08:59:26

The app that I will write to do the data transfer will be in C#. We are moving from a Delphi/Advantage to a C#/MSSQL. I am guessing I will have to do a sp_addlinkserver command to link the advantage database. I will then somehow need to get the schema from the advantage database but I maybe able to hard code that and then do a create database command and a load of create tables. Then loop through all advantage tables importing the data. Fun!

Jon 2009-12-10 10:16:50

Have fun!Sorry don't know C#. I would recommend getting hold of the most up to date version of the advantage data architect. download from the link in my reply. Very simple delphi app written by the advantage guys. It has a good feature which will output the code required to create the tables you select in either delphi, c++ or sql. Might at least give you a good starting point for you code.... The newest version might do C# i mostly use 6.1 due to most of users still on this older version of the server. Newest version of advantage is 9.1 or 9.2 I believe.

delphigirl 2009-12-10 11:20:37

Do you know if its possible to import all tables via SQL Server Import as I only see the option to write a query to import data rather than the option to select the desired tables.

Jon 2009-12-11 13:10:23

Just an FYI, Advantage has a .NET data provider so you could use Advantage with your C# app.

Joshery 2010-02-23 21:06:48

ansaurus

tags:

views:

answers:

Transferring data from Advantage Database Server to SQL Server

related questions