Caché Transact-SQL (TSQL) Migration Guide
[Home] [Back] [Next]
InterSystems: The power behind what matters   

InterSystems TSQL is an implementation of Transact-SQL which supports many of the features of both the Microsoft and Sybase implementations. Transact-SQL is used with Microsoft SQL Server (MSSQL) and Sybase Adaptive Server.

InterSystems TSQL also contains a few proprietary extensions not found in either of these implementations. These are described in the Commands chapter.
Regardless of which Caché interface is used, TSQL code is used to generated corresponding Caché SQL executable code. Caché does not provide system-level support for native TSQL.
This document will help you to quickly migrate schemas and stored procedures from Microsoft or Sybase databases and it will provide you with an understanding of the TSQL (Transact-SQL) implementation for InterSystems Caché™.
Microsoft provides good TSQL reference material at:
Migrating Existing TSQL Applications
To migrate existing TSQL applications to InterSystems TSQL, you need to perform three operations: configure Caché for TSQL, migrate the TSQL source code, and migrate the data.
Configuring TSQL
To configure your system for TSQL:
Migrating Source Code
The initial application migration is simple:
  1. Import the DDL: Import table and view definitions using either the %SYSTEM.SQL.DDLImport() method (for single files) or the %SYSTEM.SQL.DDLImportDir() method (for multiple files in a directory). Within these methods, you set the DDLMode parameter to either "MSSQLServer" or "Sybase". These methods import DDL statements, as well as DML statements such as INSERT, convert them to equivalent Caché SQL, and execute them. For further details, see Importing SQL Code.
    Alternatively, you can invoke the $SYSTEM.SQL.Sybase() or $SYSTEM.SQL.MSSQLServer() method to import the schema. For further details, see Importing SQL Code.
    If the TSQL source contains CREATE PROC statements, then a class method containing the CREATE PROC source is created. Caché places this class method in either an existing class or in a new class whose name is based on the schema and procedure name. If the procedure already exists, then the existing version is replaced by the new version. If a class matching the class name generated from the schema and procedure already exists, then this class name is used — if it was previously generated by the TSQL utility. If not, then a unique class name is generated, based on the schema and procedure name. The resulting class is compiled once the procedure has been successfully created. If logging is requested then the source statements are logged along with the name of the containing class, class method, and the formal arguments generated. Any errors encountered by the process are also reported in the log. If an error is detected during CREATE PROC processing, Caché deletes any new class that was generated for that procedure.
  2. Inspect the log file for errors: Search by Error #. A summary count of errors and successful imports will appear at the end of the log. In most cases, errors can be worked around or addressed by using information found in this document.
  3. Compile: When you import DDL, table and view definition compilation is automatically performed. To compile other TSQL source code, it is best to use the command as follows:
      DO $SYSTEM.OBJ.CompileAll("-l")
    The lowercase “L” qualifier flag specifies that locking is not applied for the duration of the compile. For a full list of flag qualifiers, call DO $SYSTEM.OBJ.ShowFlags().
Migrating the Data
In the Management Portal select System Explorer, SQL, then from the Wizards drop-down list select the Data Migration Wizard. This runs a wizard to migrate data from an external source and creates a Caché class definition to store it.
Writing and Executing TSQL on Caché

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA