Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 29 Current »

On this page:

Target audience:

CAST Administrators

Summary: this page provides detailed information about CAST's support for the Microsoft SQL Server and Sybase ASE technology.

Detailed technology support

Microsoft SQL Server

Language versionSupportedSupported by referenceDeprecatedComments

Microsoft T-SQL 2005

(tick)  Supported with limitations: Some syntax is not supported. See section below entitled Unsupported Microsoft SQL-Server syntax for Microsoft SQL Server.

Microsoft T-SQL 2008

(tick)  
Microsoft T-SQL 2012(tick)  Supported with limitations: New syntax introduced in Microsoft T-SQL 2012 and 2014 is not supported.
Microsoft T-SQL 2014(tick)  

Sybase ASE

Language Version

SupportedSupported by referenceDeprecated

Comments

Sybase T-SQL 12.0

 (tick) 

 

Sybase T-SQL 12.5.x with x ≤ 3

 (tick) 

Supported with limitations: Some syntax is no supported. See section below entitled Unsupported Sybase ASE 12.5.x syntax (see elements in bold).

.

Sybase T-SQL 12.5.4

 (tick) 

Supported with limitations: Databases should be at syntax level 12.5.3 or lower. New syntax introduced in later versions is not supported.

Sybase T-SQL 15.0+ESD#2
Sybase T-SQL 15.0.2

(tick)  

Sybase T-SQL 15.5

(tick)  
Sybase T-SQL 15.7 (tick) 

Required third-party software

To successfully deliver and analyze Microsoft SQL Server or Sybase ASE T-SQL code, the following third-party software is required:

Install on workstation running the DMT (for extraction)
Install on workstation running CMS (for analysis)

Nothing required

All that is required is network access to the live database/schema to be packaged.

Nothing required
The CAST SQL Analyzer (used to analyze PL/SQL and T-SQL) is an "offline" analyzer, therefore, no live connection to the RDBMS is required during the analysis in the CAST Management Studio. Instead, the target schemas/databases are packaged by the CAST Delivery Manager Tool via a JDBC connection into files that can be analyzed by the CAST SQL Analyzer.

Microsoft SQL Server and Sybase ASE T-SQL objects and links

The following section lists the objects and links between objects that the SQL analyzer is capable of detecting and storing in the CAST Analysis Service:

Objects

Instance
Database
Schema
Trigger
Function
Procedure
Table
View
Table Column / View Column
Index, Foreign Key Constraint, Primary Key Constraint, Unique Constraint

Links detected by the SQL Analyzer are listed in the attached Excel file:

Technology support notes

This section provides more detail about the support for specific Microsoft SQL Server and Sybase ASE syntax and the way in which CAST handles them:

  • For duplicated objects (objects with the same name belonging to the same database but with different users), only one of the objects is managed.
  • Support for Bracketed and Quoted Identifiers:
    • Names and symbols will be saved in the Analysis Service in their standard form, i.e.: the form in which they are displayed in the system catalog.

    • Certain character types are not supported when they are enclosed within a QUOTED IDENTIFIER: ' or \n (new line), \r (carriage return), \t (tabulation).

    • If the name of an object (or any other component with a qualified name) contains at least one non-supported character, the link to this object will not be saved in the Analysis Service.

    • If a database name contains non-supported characters, a warning will be emitted, but the synchronization process will continue (it will not be stopped). This warning can be interpreted by the potential risk of failure of query execution or object resolution. The presence of a quotation mark (") in a database name is NOT supported - if this were the case, the synchronization process would be stopped because of the failure of the save process.

  • Most of the syntaxes related to administration commands (e.g. create database, create user, dbcc etc) are not supported.
  • Grouped Stored Procedures are partially supported (only the first procedure of the group is taken into account).
  • Cross server links are not created.

Please note that currently inter-Application links (i.e. links between source code in separate Applications) between the following combinations of technologies is not supported:

  • Between Microsoft SQL Server T-SQL Analysis Units in different Applications
  • Between Sybase ASE T-SQL Analysis Units in different Applications

If you do require link resolution, then all Analysis Units must belong to the same Application.

Column Level Impact Analysis

The functionality known in previous releases of CAST AIP as the Column Level Impact Analysis (CLIA) for participating Microsoft SQL Server or Sybase ASE databases is no longer available (and has not been since CAST AIP 7.2.x). Since the SQL analyzer generated objects for columns, you can use the following workaround to obtain CLIA-like results:

  • For Column Level Impact Analysis when performing a column modification/deletion: you can put the selected column into a CAST Enlighten view and use the "show linked objects" function.
  • For Column Level Impact Analysis when performing a column addition: you can put the table to which you want to add the column into a CAST Enlighten view and use the "show linked objects" function.

Unsupported Microsoft SQL-Server syntax for Microsoft SQL Server

The following syntax is not supported:

  • ADD [ COUNTER ] SIGNATURE
  • ALTER AUTHORIZATION
  • ALTER DATABASE
  • ALTER/DROP SCHEMA
  • BACKUP CERTIFICATE
  • BACKUP DATABASE/LOG: MIRROR TO < backup_device > [ ,...n ] [ ...next-mirror ] is not supported
  • BACKUP/RESTORE SERVICE MASTER KEY
  • BEGIN CONVERSATION TIMER
  • BEGIN DIALOG [ CONVERSATION ]
  • CREATE [PRIMARY] XML INDEX
  • CREATE/ALTER/DROP APPLICATION ROLE
  • CREATE/ALTER/DROP ASSEMBLY
  • CREATE/ALTER/DROP ASYMMETRIC KEY
  • CREATE/ALTER/DROP CERTIFICATE
  • CREATE/ALTER/DROP CREDENTIAL
  • CREATE/ALTER/DROP ENDPOINT
  • CREATE/ALTER/DROP FULLTEXT CATALOG
  • CREATE/ALTER/DROP FULLTEXT INDEX
  • CREATE/ALTER/DROP MESSAGE TYPE
  • CREATE/ALTER/DROP PARTITION FUNCTION
  • CREATE/ALTER/DROP PARTITION SCHEME
  • CREATE/ALTER/DROP QUEUE
  • CREATE/ALTER/DROP REMOTE SERVICE BINDING
  • CREATE/ALTER/DROP ROLE
  • CREATE/ALTER/DROP ROUTE
  • CREATE/ALTER/DROP SERVICE
  • CREATE/ALTER/DROP USER
  • CREATE/ALTER/DROP/OPEN/CLOSE SYMMETRIC KEY
  • CREATE/ALTER/DROP/OPEN/CLOSE/BACKUP/RESTORE MASTER KEY
  • CREATE/DROP CONTRACT
  • CREATE/DROP EVENT NOTIFICATION
  • DENY: Microsoft SQL-Server 2005 specific syntax is not supported (Microsoft SQL-Server 2000 syntax is fully supported)
  • END CONVERSATION
  • GET CONVERSATION GROUP
  • GRANT: Microsoft SQL-Server 2005 specific syntax is not supported (Microsoft SQL-Server 2000 syntax is fully supported)
  • MOVE CONVERSATION
  • RECEIVE
  • REVOKE: Microsoft SQL-Server 2005 specific syntax is not supported (Microsoft SQL-Server 2000 syntax is fully supported)
  • SEND ON CONVERSATION
  • SELECT: FOR <XML>
  • WAITFOR ( { GET CONVERSATION GROUP ... | RECEIVE ... } ) [ , TIMEOUT timeout ]
  • WITH XMLNAMESPACES

Unsupported Sybase ASE 12.5.x syntax (see elements in bold)

  • MATCH FULL criterion in CREATE/ALTER TABLE statements with a foreign key constraint:
    • ALTER TABLE [database.[owner].]table_name ADD column_name datatype REFERENCES [[database.]owner.]table_name [(column_name)] [MATCH FULL]
  • Following GRANT/REVOKE commands:
    • GRANT/REVOKE DBCC dbcc_command [on {all | database }] to { user_list | role_list }
    • GRANT/REVOKE default
    • GRANT SELECT ON built-in
    • GRANT SET PROXY
    • GRANT/REVOKE { TRUNCATE TABLE | { UPDATE | DELETE } STATISTICS }
  • Following SET commands:
    • SET BULK ARRAY
    • SET IDENTITY_INSERT
  • SQL-J procedure parameters initialized with default value:
    • CREATE PROC[EDURE] [owner.]procedure ([{[in | out | inout] @parameter_name datatype [= default_value]} ...
  • Java data type style in SQL-J function parameters:CREATE FUNCTION querytext ( xql java.lang.String, streamdoc java.io.InputStream ) RETURNS java.lang.String ...
  • WITH STATUSONLY clause in the KILL command:KILL spid_number WITH STATUSONLY
  • MOUNT/UNMOUNT DATABASE commands.
  • FOR XML clause in SELECT statements:
    • SELECT name, type FROM systypes WHERE name LIKE '%pattern%' FOR XML
  • Square bracketed identifiers:
    • CREATE TABLE [my table]( [c 1] int, [c 2] char(10) )
  • Unicode prefix and related notations u&:
    • CREATE PROCEDURE my_procedure @v nchar = u&'+000041' AS ...
  • Encryption features:
    • CREATE/ALTER/DROP ENCRYPTION KEY
    • GRANT/REVOKE CREATE/DECRYPT ENCRYPTION
    • SELECT .. INTO table_name ( column_name ENCRYPT [ WITH [database][.][owner][.]key_name], ...)
    • CREATE/ALTER TABLE ... ENCRYPT [ with [database][.][owner][.]keyname ]
  • XMLEXTRACT, XMLPARSE, XMLTEST keywords.
  • DEFAULT inside INSERT:
    • INSERT [INTO] <identifier> VALUES ( ..., DEFAULT, ... ).
  • Derived tables:
    • (SELECT ....) [ AS alias [ ( colum ) ] ] in FROM-clauses of SELECT, SELECT...INTO, CREATE VIEW, INSERT.
  • No labels