Solved

Convert Sybase tables to MSSQL Server tables

Posted on 2008-10-07
4
1,419 Views
Last Modified: 2012-05-05
Hi EE -
My Boss, has tasked me with  finding out procedures for converting 10 or so tables from an SyBase ASE v.11.9.26
Database (linux box) to a MSSQL Server 2005 friendly set of tables?

I have been reading SSMA for SyBase :
http://www.microsoft.com/sql/solutions/migration/sybase/default.mspx

and DTS:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx

The SSMA above sounds simple: a) I install the program SSMA and b) then launch it. c) Login to my SyBase DB and d) then tell it (using built in SSMA GUI tools) to convert (extract) these 10 tables. e) I then open my MSSQL tool (whatever the customer is using) and f) grab this object that I extracted and g) import it in to  the MSSQL Server 2005 DB.

The DTS above sounds mor like I have to be moving data around within MSSQL system. But I read another post here :

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20868418.html

this sounds like I could use the DTS to convert the tables, mentioned above.

All assistance is appreciated.

Thanks -
Wotan1953

0
Comment
Question by:wotan1953
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 22661136
You really have two issues here:
 - Creating the equivalent Domains/Tables/Keys/Indexes in MSSQL
 - Moving the data

There are a couple of questions here as well:
 - Is this a one time thing or is this going to be done on a regular basis?
 - How much data are you actually going to move, e.g. how many rows in the tables?

SSMA is designed to help you do a one-time migration from Sybase to SQL Server.  DTS is designed to help you move data on a regular basis from external sources (in this case Sybase) into SQL Server.

If you have to do this on a regular basis, then you should probably start with SSMA so you can get the tables and associated pariphinalia moved over the first time.  After that, you can use DTS to move the data on whatever schedule is required.

Without knowing more about your environment and requirements, it is a little difficult to give you more specific advice.

Regards,
Bill

BTW, Sybase is a proper name with the "b" in lower case.
0
 

Author Comment

by:wotan1953
ID: 22661600
Hi ... and thanks.

This is a one time thing. Also, we are hoping to have a create script and table descriptions, with any indexes and unique constraints etc., that we send the client. They would then open this in their MSSQL environment and add to the MSSQL db.

Regarding the number of rows, several of the tables have 10s of thousands of rows. Most others are more manageable .

So it looks like SSMA is the answer. I am assuming I need to download the SSMA package. First I need to add the .NET Framwork plus it looks like a J# package too. I will start that.

What other environment/requirements, would you need? Or if I download the SSMA package, will it's running be fairly straight forward?

Thanks again
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
ID: 22661948
Actually, there are other ways of going about this, the viability of which depends on how complex the source database is.  If your tables are defined with a bunch of domains (user defined data types) or there are incompatible data types (there should not be many) things are a bit trickier.

You can extract the DDL for a Sybase database using the "ddlgen" utility for 11.9.2 or "defncopy" for newer releases.  It will extract the definitions of the database objects to a flat file SQL script.  You can then simply edit out any tables and their associated components you are not interested in.

The second step is to use BCP to get the data out of the tables.  You will have to do this in character mode and it will generate a complete copy of the table data.  You then use BCP on the MS end to stuff the data back in.

All of this is pretty straight forward and well documented.

Another alternative is to use a multi-database development tool like Embarcadero RapidSQL or Aquafold Aqua Data Studio.  You can extract the ddl for the objects you are interested in as well as creating INSERT scripts for all the data.  It is not as effecient as BCP for moving data but if you only have a few 10s of thousands of rows, performance is not really an issue.

Regards,

Bill
0
 

Author Closing Comment

by:wotan1953
ID: 31503900
Hi Bill -
Thanks. I went with WinSQL 30 day triial. It seems similar to the other to packages you mentioned. But if you decide to buy, it is only about $250 ... which is much less then Embarcadero RapidSQL or Aquafold Aqua Data Studio. I will comment more externally, about DDL and BCP, but just wanted you to know I appreciated the quick and helpful responses.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question