Saturday, July 16, 2011

SQL Server: How to Import Data from Sybase Advantage (adt Files)


Sybase Advantage database create separate files for each table and its indexes. Table files are created with extension “adt”. One can import data from Sybase Advantage to SQL Server if she has
  • Access to adt files
  • Advantage OLE DB Provider
But import process is bit tricky because first you must free target “adt” files from its database dictionary, which can be achieved by using Advantage Data Architect. It can be downloaded from http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=16&Product=8&Platform=6 . Once you have install Advantage Data Architect, follow these step to free your target adt files.

1.       Click on new connection wizard

2.       Create a connection to a directory of existing tables

3.       Provide DatabaseName of your choice and then provide correct path of folder where adt files are located on your hard drive and press finish.

4.       Once you have created connection, it will start showing adt tables BUT still you can’t open or export these tables as these are bound to directory.

5.       To free these tables, click on Tools -- > Free Data Dictionary Bound Tables

6.       Provide adt file name with its complete path and press OK button.

7.       Your adt table is now free. Now you can open it in Data Architect. You can export or you can close Data Architect and import this table from SQL Server Import Wizard.
Note: In next post, we will explore a simple method to import adt files by using SQL Server Integration Services Package.

In next post: How to import adt files by using SSIS package

6 comments:

  1. Just what i was looking for - Thanks!

    ReplyDelete
  2. I have a procedure that runs against ADT, but when I try to execute the same locally, there's an error:
    poQuery: Error 7200: AQE Error: State = HY000; NativeError = 5154; [iAnywhere Solutions][Advantage SQL][ASA] Error 5154: Execution of the stored procedure failed. Error 7200: AQE Error: State = HY000;
    NativeError = 5159; [iAnywhere Solutions][Advantage SQL][ASA] Error 5159: Error encountered when trying to open a database table. The table is a database table. It cannot be opened as a free table. Table name:
    SetupAdt ** Script error information: -- Location of error in the SQL statement is: 33 (line: 3 column: 1)

    ReplyDelete
  3. I am having trouble with one table. All others are opening with no problem. The error message states...The specified table, memo file, or index file was unable to be opened. Any thoughts??

    ReplyDelete
    Replies
    1. Open this table in design view and find out problematic column with memo data type. You have to remove all columns with memo data type before exporting it to csv.

      Delete
  4. Design View?? I don't see a design view in ADT...

    ReplyDelete

All suggestions are welcome