Copying tables between SQL Anywhere and MS SQL Server Databases

Date:Archived
Product/Release:LANSA for Windows
Abstract:Procedure for copying individual tables between SQL Anywhere and MS SQL
Submitted By:LANSA Technical Support
Last Review:November 2010t

Description:

Sometimes (e.g. deployment of runtime environments) it may be necessary to copy LANSA's or an application's tables to different databases. The following procedure explains how to do it:

  1. Decide whether the source and target databases are going to support or emulate collections. This will affect the syntax of the commands described below. The tables are going to be copied via a text file with fields delimited by TABs and not surrounded by quotes (i.e. common format). The following procedure can be used to copy the table and/or data definitions between databases of the same or different types (viz. SQL Anywhere and MS SQL Server). Please, before performing this procedure, ensure that the database engines have already been started, and a backup of the database or relevant tables has been done.
     
  2. Download the table data to a text file, on the source database:
    • For SQL Anywhere
      Go into isql and issue the command:
      UNLOAD TABLE "PARTLIB"."TABLE1" TO ‘X:\TABLE1.dat' QUOTES OFF …
      … DELIMITED BY ‘\x09'
    • For MS SQL Server
      Open a DOS-command window and issue the command:
      bcp PARTLIB_TABLE1 out X:\TABLE1.dat -U DBA –P SQL -c

    In both cases ‘\x09' is the hexadecimal value of TAB, and PARTLIB is the Partition library and X: can be network or portable media drive. For MS SQL Server, PARTLIB_ is optional (only if EMULATE_COLLECTIONS=YES on X_DBMENV.DAT).
     
  3. Copy common table definition (.ctd) file, relative record number file (.rrn –i.e. if any), and downloaded data file (from 1) to a drive available to the target database environment. The *.ctd files (e.g. TABLE1.ctd) are on the \X_WIN95\X_LANSA\X_PPP\SOURCE directory, and the *.rrn files (e.g. TABLE1.rrn) are on the \X_WIN95\X_LANSA\X_PPP directory (X_PPP is the directory associated to each LANSA partition) . Then, copy the *.rrn files to the \X_WIN95\X_LANSA\X_PPP directory on the target environment.
     
  4. Rebuild the table definitions on the target database. To do this, open a DOS-command window, go to the \X_WIN95\X_LANSA directory, and issue the command:
    • For SQL Anywhere
      RUNSQL Y:\TABLE1.ctd LX_LANSA Y Y SQLANYWHERE DBA/SQL
    • For MS SQL Server
      RUNSQL Y:\TABLE1.ctd LX_LANSA Y Y MSSQLS DBA/SQL
      … where Y: is the drive where the .ctd file was copied to.

    The RUNSQL command should be found on the \X_WIN95\X_LANSA\EXECUTE directory. This command reports some –usually ignored- warnings depending on the database features. Please, after running this command, verify that the table was created successfully from the native database manager environment (viz. SQL Central or MS SQL Server Enterprise Manager).
     
  5. Load the table data to the target database:
    • For SQL Anywhere
      Go into isql and issue the command:
      LOAD TABLE "PARTLIB"."TABLE1" FROM ‘Y:\TABLE1.dat' QUOTES OFF DELIMITED BY ‘\x09'
    • For MS SQL Server
      Open a DOS-command window and issue the command:
      bcp PARTLIB_TABLE1 in Y:\TABLE1.dat -U DBA –P SQL -c

    The above steps can be followed for multiple tables to build application deployment procedures (e.g. Command or .BAT files). In addition (for runtime environments), it is necessary to copy some LANSA internal tables in conjunction with the LANSA runtime (LANSA/X, which does not need any license or hardware key –i.e. dongle), and application DLLs (viz. I/O Modules, Processes and Functions). Please, for more information, refer to the Application Deployment chapter on the LANSA for Windows User Guide.
Back to Top