How AbaPerls Loads a File

This page describes the AbaPerls file-loading process which is activated each time you load a file with AbaPerls. There are three ways you can load database files with AbaPerls: You use the tool ABASQL to load one (or a handful) files at time; you use DBBUILD to build an entire database or to add a subsystem to a database; you use update scripts generated by DBUPDGEN to update the database from one version to another. No matter which tool you use, AbaPerls uses the same mechanisms to locate and load the file. You need to have an understanding of what AbaPerls does behind your back – at least when AbaPerls does something you did not expect. (And that will happen sooner or later. Trust me.)

Contents:
   Overall Loading Procedure
   The AbaPerls File-Lookup Order
   Messages from SQL Server
   Emitting SET commands
   Version Checks for Production and Test Databases
   Pre-SQL Analysis: Creating and Recreating Objects
      Checks on Conformance to the Subsystem Structure
   Pre-SQL Analysis: Temp Tables and Table Variables
   Pre-SQL Analysis: Reloading a Table File
   Pre-SQL Analysis: Supplemental SQL Checks
      Use of TOP with INSERT, UPDATE, DELETE and MERGE
      SET ROWCOUNT
      = NULL
      Checks for CREATE TABLE
      EXECUTE AS
      Private Names
      Check that $DEPENDSON is Present
   Pre-SQL Analysis: Permissions on Objects
   Pre-SQL Analysis: Other Miscellaneous Fixes
   Retrieving Name of Current Subsystem

Overall Loading Procedure

When AbaPerls processes a file, it performs these steps:
  1. Looks up the file through the AbaPerls file-lookup order.
  2. Reads the file and strips it of comments.
  3. For production and test databases, performs a version check against what's in the database.
  4. Runs the file through the Preppis preprocessor.
  5. Pre-SQL Analysis, detailed in the several sections on this page, and which may add extra code before the file (pre-work) and after the file (post-work), as well alter the code of the file itself.
  6. Puts back the comments that were stripped, unless the ‑crypt configuration option is in force.
  7. Starts a transaction and sends the generated pre-work code to SQL Server.
  8. Sends the file itself to SQL Server.
  9. Performs Object Checking. This is described in a separate topic.
  10. Sends post-work code to SQL Server, for instance GRANT statements.
  11. Update the AbaPerls system tables, if the ABAPERLS subsystem is installed.
  12. Commits the transaction started in point 7.

If an error occurs in any of the steps, the remaining steps are not performed. If errors occurs in step 7 or later, the transaction is rolled back.

When you use the ‑noexec switch, AbaPerls only performs steps 1, 2 and a modified version of step 4: Preppis processes only the directives $INCLUDE, $REQUIRE, $KEYFILE, $COMPILE, $DLLINCLUDE, EXTERNAL NAME and $USEDBY to retrieve all dependent files and to create DLLs from source files written in .Net languages. You combine ‑noexec with ‑get to create a kit for installation at a customer site and you want the include files and the DLL files created by $COMPILE in the kit too.

AbaPerls starts a user-defined transaction to maintain the integrity between AbaPerls own tables and the SQL Server system tables. Thus, if an error occurs in any of the steps 7 to 11, AbaPerls will roll everything back. However, a file may include a command that is not permitted to appear in a user-defined transaction, for instance a call to the system procedure sp_tableoption. In this case, AbaPerls restarts the operation on step 7, but without starting a transaction. The error is not reported.

A general disclaimer about Pre-SQL Analysis: AbaPerls does not include a full-blown SQL parser, but the pieces of AbaPerls that parse SQL code make some "reasonable assumptions" about formatting and syntax in order to cut down development time. In the various sections, I try to document what these assumptions are and which restrictions you face.

The AbaPerls File-Lookup Order

Introduction

When AbaPerls is fed a filename to process, AbaPerls may look for the file in these locations:

  1. On disk.
    1. With the filename as given.
    2. In the appropriate directory in an AbaPerls SQL directory structure.
    3. For a file that is checked out, the path for the local file according to the version-control system.
  2. On disk in an AbaPerls SQL directory structure in a subsystem subdirectory.
  3. In version-control according to the AbaPerls SQL directory structure.

AbaPerls will never attempt all these locations. Instead AbaPerls identifies the situation in which you use AbaPerls, by asking these questions: 1) Is version-control active? 2) Is ‑use_disk active? 3) Is there a directory subsystem/SQL visible in the current folder? 4) Are the special files SS‑FILES.LIS and SUBSYSTEMS.LIS present in the current directory? Depending on the answers, AbaPerls identifies any of these four situations:

Version-Control Only. This occurs when the answers are 1) Yes. 2) No. 3) Don't care. 4) Don't care. In this situation, AbaPerls only reads from location 3 in the list above.

An AbaPerls Install Kit. This situation is identified by these answers: 1) No. 2) Yes. 3) Don't care. 4) Yes. In this situation, AbaPerls only reads from location 2 in the list above.

A Pseudo-kit. This situation is identified by these answers: 1) No. 2) Yes. 3) Yes. 4) No. In this situation, AbaPerls only reads from location 2 in the list above.

Developer Environment. The answers are: 1) Don't care. 2) Yes. 3) No. 4) No. In this case, AbaPerls attempts the steps 1a, 1b, 1c and 2 in that order, with these qualifications:

The reader is likely to quite confused at this point by all these possibilities. For this reason I like to butt in that this is designed to do what you want. Before I explain this a little closer, let's revisit the defaults for the different AbaPerls tools. Two general rules: a) if version-control is not active, ‑use_disk is in force. b) ‑get implies ‑nouse_disk.

ABASQL Version-control is active with ABASQL if you specify a VC-path with ‑VC or AbaPerls is able to map your current folder to a directory in TFS. (This does not apply if you are only using SourceSafe.) ABASQL will always read from disk, unless you explicitly specify ‑nouse_disk.
DBBUILD Version-control is active with DBBUILD if specify a config-file with ‑config, a VC-path with ‑VC, or if AbaPerls is able to map your current folder to a directory in TFS. If version-control is active with DBBUILD, this implies ‑nouse_disk and you need to specify ‑use_disk to override this.
Update scriptsUpdate scripts always have version-control active, unless you explicitly give the option ‑noVC. When version-control is active, the default is ‑nouse_disk, unless the script was created with the ‑checkedout option to DBUPDGEN.

Let's look at the four situations again, but in different order. In Developer Environment, you typically use ABASQL to load individual files. You mainly work from disk, but you are likely to take files like grant.template or include-files from version-control. If the item is in version-control but not checked-out by you, AbaPerls avoids your disk copy, since it may be out of date. The search order permits you to load a stored-procedure file while in the TBL directory or vice versa.  In this situation, you may also want to use DBBUILD and update scripts for test builds. However, these tools are geared for the other three situations.

The situation Version-control only is mainly used by the build master who wants to build or upgrade a database in a controlled way, or create an install kit for a remote site with ‑noexec ‑get. In this situation, local files on disk should not interfere. You may also want to use ABASQL in this situation to deliver a single fix.

The situation AbaPerls Install Kit is the result of Version-control only with ‑noexec ‑get and the net result for the database is the same: all files will appear to have been loaded from version-control. The reason for this two-step operation is that when you are at a customer site, you are not likely to have access to your version-control system. See the page Installation at a Customer Site for a longer discussion.

The Pseudo-kit situation is not as common as the other three. In this situation, you have a structure which is similar to the install kit you get with ‑noexec ‑get – except that the files SS‑FILES.LIS and SUBSYSTEMS.LIS are not there and in the database and there is no connection to version-control whatsoever. You may set this because you want to experiment. For instance, you may want to test out a restructuring of your current system.

Here follows a more detailed specification for each step:

1a. On disk with the filename as given

If you specify a full path such as C:\Temp\nisse_sp.sp that is the file, and AbaPerls will not try anything else. If you say nisse_sp.sp, and there is a nisse_sp.sp in the current directory, this is the file. The same is true, if you specify sub\nisse_sp.sp if there is a sub-directory sub in the current directory, and in sub there is a nisse_sp.sp.

1b. In the appropriate directory in an AbaPerls SQL directory structure.

If step 1a yields no file, AbaPerls examines whether the current directory in part of an AbaPerls SQL directory structure. This is the case, if the current path includes any of the special names in this structure that is Message, Type, Tbl, Servicebroker, SP, Functions, Include, View, Assemblies or Scripts. In this case, AbaPerls looks for the file in the directory that maps to the extension for the file to load. If AbaPerls finds itself in the SQL directory, AbaPerls will look for the file in the subdirectory that the extension maps to.

1c. For a file that is checked out, the path for the local file according to the version-control system.

Note here that if you have multiple checkouts from different computers or different workspaces on the same computer, it is not predictable which version AbaPerls will use; it uses the first it is served from the version-control system. Checkouts in this context include pending Add operations in TFS, which supports this concept.

2. On disk in an AbaPerls SQL directory structure in a subsystem subdirectory.

Say that there is your current directory, there is a directory SUBSYS1 which in its turn has a subdirectory SQL, and you specify ‑subsystem SUBSYS1 and nisse_sp.sp as the file. AbaPerls will then look for SUBSYS1/SQL/SP/nisse_sp.sp.

3. In version-control according to the AbaPerls SQL directory structure

If the VC-path does not end in /SQL, AbaPerls adds this to the VC-path, and then uses the file extension to determine in which subdirectory in the structure to look for the file. If you somewhere have specified a label (or a date or version number), AbaPerls will retrieve the corresponding version of the file.

An Example

Assume that your current directory is C:\projects\abc\sql\tbl. You give this command:

abasql -d mydatabase -VC C:\SourceSafe/$/abc/3.20 some_sp.sp

This file includes these two lines:

$INCLUDE this_include.sqlinc
$INCLUDE sub/that_include.sqlinc

You have some_sp.sp checked out to C:\projects\abc\3.20\sql\sp. $/abc/3.20/SQL/include/this_include.sqlinc exists in SourceSafe, but you don't have it checked out. There is no file $/abc/3.20/SQL/include/sub/that_include.sqlinc in SourceSafe.

AbaPerls first looks up some_sp.sp in $/abc/3.20/sql/SP/some_sp.sp and finds that it exists you have the file checked out, AbaPerls will search the locations in this order:

1a. C:\projects\abc\sql\tbl\some_sp.sp. (Local directory.)
1b. C:\projects\abc\sql\SP\some_sp.sp. (The directory for stored procedures.)
1c. C:\projects\abc\3.20\sql\SP\some_sp.sp. (Checkout location.)
3. $/abc/3.20/sql/SP/some_sp.sp (Path in SourceSafe.)

AbaPerls will look for this_include.sqlinc in one single place: $/abc/3.20/sql/include/this_include.sqlinc. (Because you don't have the file checked out.)

AbaPerls will first look for that_include.sqlinc in $/abc/3.20/sql/include/sub/that_include.sqlinc and since this item does not exist, AbaPerls will try these two locations:

1a. C:\projects\abc\sql\tbl\sub\that_include.sqlinc.
1b. C:\projects\abc\sql\include\sub\that_include.sqlinc.

Note here that sub is included in both cases.

Another Example

You are in C:\Build and there is a structure created with ‑noexec get with three subsystems: ABAPERLS, SUBSYS1 and SUBSYS2. You run this command:

abasql -d somedb -subsystem SUBSYS1 some_udf.sqlfun

AbaPerls will attempt to load C:\Build\SUBSYS1\SQL\Functions\some_udf.sqlfun.

You now run this command:

abasql -d somedb -subsystem SUBSYSTEM2 someother_udf.sqlfun

This will yield an error, because there is no subsystem with that name in SUBSYSTEMS.LIS.

Assume now that the structure is the same, but there is no SUBSYSTEMS.LIS or SS‑FILES.LIS. The first command will have the same result; AbaPerls will attempt to load C:\Build\SUBSYS1\SQL\Functions\some_udf.sqlfun. However, the second command till have different effect. AbaPerls will now attempt load C:\Build\some_udf.sqlfun (and actually also C:\Build\Functions\some_udf.sqlfun), as there is no way for AbaPerls to detect the incorrect subsystem name. If any of these files would happen to exist, the load succeeds.

Messages from SQL Server

Messages from SQL Server stemming from the actual file you submitted, are likely to be syntax errors or warnings that relate to the SQL code you submitted to AbaPerls. AbaPerls prints a message that includes the message number, severity level, line number, file name and message text. If the message number is 0 or negative, this is a message from AbaPerls itself. (E.g. errors found by Object Checking.)

The line number SQL Server reports relates to the batch, but AbaPerls maps this to the correct file (it could be a file included with $INCLUDE) and line in the file (which could the definition of a long macro). (Provided that is, that the line number that SQL Server supplies is correct, which unfortunately is not always the case, although SQL Server has improved over the years.)

If errors occur in any of the pre-work or post-work code that AbaPerls adds before and after the file, AbaPerls prints the entire text of the complete SQL batch that failed together with line numbers directly related to that batch. The most likely user-error to cause this is an incorrect CREATE TABLE for a temp table or a bad grant.template, see below about permission on objects. Other errors are even less anticipated; would they occur it could be a bug in AbaPerls. (But there are a few evil things you could do mess things up for AbaPerls.)

Emitting SET commands

For each file, AbaPerls emits a bunch of SET commands that always are the same:

 SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
SET ARITHABORT ON
SET DEADLOCK_PRIORITY LOW
SET NOCOUNT ON
SET NUMERIC_ROUNDABORT OFF
SET XACT_ABORT OFF

Note: older versions of AbaPerls permitted you use config-options to specify that some ANSI options should be OFF. These options have been dropped, and AbaPerls always run with all ANSI options on (with the exception of IMPLICIT_TRANSACTIONS and CURSOR_CLOSE_ON_COMMIT) in agreement with defaults for SQL Server Management Studio and all modern client APIs.

You can of course use SET commands within stored procedures, triggers and functions. However, if you are considering putting a SET command in a file outside a module definition, there is all reason to think twice or even thrice whether you really must. This is not supported by AbaPerls, and it can lead to nasty surprises.

Version Checks for Production and Test Databases

With the config-option ‑environment, it's possible to define a database as a development, test or production database. For the latter two, AbaPerls performs tests of the objects being loaded versus the information stored about them in the system table abasysobjects. This section does not apply to databases marked DEV.

The ultimate purpose of these tests is to handle the situation that a developer uses ABASQL to load a new version of an object into a test or production database. This could be an urgent fix in a stored procedure, or a configuration change through an INSERT-file. Later, when an update script is run against this database, the update script happens to include an earlier version of that file. In a production environment, you probably don't want that newer version to be over-written, as this could reintroduce the bug that was fixed, or revert the configuration. In a test environment, you are caught between two wills: on the one hand, you may want to keep that fix, but on the other, you should actually test what you have built and may eventually ship into production. (By overwriting the file, you will be made aware of that what you installed, does not include that fix.)

This boils down to a couple of rules that depend on the environment and what tool you are using. On the one hand is ABASQL for manual loads, and on the other is DBBUILD ‑rebuild and update scripts, below referred to as "build tools". None of these checks can be overridden by ‑force. These are the checks:

Note: "loading from version-control", includes loading from a structure previously created with ‑noexec ‑get.

For most file types, AbaPerls performs this check as soon it has looked up the file in version-control, looking up the information in abasysobjects with the file name as key. If a file appears in a $REQUIRE macro, AbaPerls checks that file separately, and this can lead to that the $REQUIREd file is skipped, while the calling file is still loaded. (Recall that the purpose of $REQUIRE is to ensure that a dependency is loaded first.) This does not extend to files loaded through $INCLUDE; for these AbaPerls does not perform any version checks. 

For assemblies, AbaPerls version-checks both the .assem file and the input for the assembly, that is, the DLL or the source file. See the CLR Page for more details.

For files of the types .sp (stored procedures) and .sqlfun (user-defined function) AbaPerls defers the check until it has examined the code to see what object the code defines, and looks up abasysobjects.ss_version using the object name as key. This permits you to enter a test version of a procedure and load it directly from disk. (Note that you will need to use the ‑force option in this case, as AbaPerls normally requires the object name to match with the file name.) This does not apply when a file is loaded through $REQUIRE; for the $REQUIRE the look-up is always on file name.

When verifying that the directories in version-control are the same, AbaPerls checks the path to the repository and the path within the repository separately. The latter must be the same. The path to the repository is a different matter, since the same repository can be referred to in a multitude of ways. For instance, one developer may refer to a SourceSafe database using a mapped drive, whereas another developer uses a UNC path. In order to verify that these paths are the same, AbaPerls tracks an ID for every repository. The ID is derived from within the repository itself. The ability to track repositories by IDs was added to AbaPerls in version L1.0.0250, and ID may therefore be missing from the database, or from older versions of the files SS‑FILES.LIS and SUBSYSTEMS.LIS. If any of the IDs are missing, AbaPerls assumes the best and compare the paths within the repository only.

As noted above, AbaPerls stores an MD5 hash in abasysobjects. This was introduced since with TFS, the checks based on version number and paths cam lead to a large number of false positives with merging forth and back. Observe the following:

Pre-SQL Analysis: Creating and Recreating Objects

In many cases the object(s) defines in the file you load do already exist in the database. Therefore it is common in many shops to include IF EXISTS ... DROP ... in files that defines SQL objects. AbaPerls handles this for you for most types of objects, either by adding pre-work code to drop any existing object or by changing CREATE to ALTER. Thus, in these cases you should not add your files to handle this in the file, as this could cause confusion and unexpected results.

AbaPerls does not permit two subsystems to use the same name for an object. That is, if you first create a stored procedure some_sp in subsystem A and then try to use the same name in subsystem B, AbaPerls regards this as error, and does not override the definition of some_sp in A. There is a way to suppress this check, but only in update scripts.

This table shows exactly what AbaPerls performs for each command. Note: when I say that AbaPerls generates a DROP, it is tacitly understood that the DROP is only executed if the object exists.

CREATE RULE AbaPerls generates a DROP RULE. AbaPerls also generates code to unbind the rule from any type the rule is bound to. AbaPerls does not rebind the type, but assumes that the file where the rule is created also includes a command to bind the rule, according to the rules for .typ files in the AbaPerls SQL directory structure.
CREATE DEFAULT AbaPerls handles CREATE DEFAULT analogously with CREATE RULE, see above.
CREATE TYPE
EXEC
 sp_addtype

If there is no table column or procedure parameter of this type, AbaPerls drops and recreates the type. If the command is sp_addtype, AbaPerls rewrites the command to CREATE TYPE. (There is a small difference between the two: with sp_addtype, public is granted REFERENCES permissions on the type; this does not happen with CREATE TYPE, see Books Online for details.)
   If the type is a scalar type and is in use, and the version of the ABAPERLS system is at least L1.0.0270, AbaPerls compares the definition in the file with the definition in the database, and if they are different, this is an error. If you have an older version of the ABAPERLS subsystem, or the subsystem is not present at all, AbaPerls is not able to compare the types, but will always raise an error if the type is in use.
   If the type is a table type and the version of the ABAPERLS subsystem is at least L1.0.0342, and the comparison indicates a difference in the type definition, AbaPerls will drop all procedures and functions that uses the type as parameters that are listed in abasysobjects and produce a warning message for each object dropped. If there are references from objects not present in abasysobjects, the load will still fail. For lower versions of the ABAPERLS subsystem, the same applies as for scalar types.
   Private table types in .sp and .sqlfun files are handled in a special way: they are transferred to a temporary schema, so that the new definition of the type can be loaded. The type and the schema are dropped after the new definition of the procedure/function has been loaded.

CREATE XML SCHEMA COLLETCTION

If there is no table column or procedure parameter with this schema collection, AbaPerls drops and recreates the collection. If the type is referred to by a message type, AbaPerls transfers the old definition to a temporary schema before loading the new definition. AbaPerls then alters the message type to use the new definition. This technique is also used for schema collections that are private to a stored procedure or function.
   If the type cannot be dropped, and the version of the ABAPERLS system is at least L1.0.0270, AbaPerls compares the definition in the file with the definition of the schema collection in the database, and if they are different, this is an error. For older versions of the ABAPERLS subsystem, or if the subsystem is absent, AbaPerls raises an error if the collection is in use for a table column or procedure parameter (and it is not a private schema).

EXEC sp_bindrule AbaPerls does not generate any sp_unbindrule.
EXEC sp_bindefault AbaPerls does not generate any sp_unbindefault.
CREATE TABLE

What is said here, applies to permanent tables, not temp tables.
   If the table is empty and not referred to by a foreign key or a view or function created with SCHEMABINDING, AbaPerls drops and recreates the table. If there are dependent objects (triggers, foreign keys and indexes) listed in the AbaPerls system tables from other subsystems, AbaPerls emits a warning and lists all these objects to remind you that you need to reload these objects. (Since they were automatically dropped when the table was recreated.)
   If the table cannot be dropped, and the version of the ABAPERLS subsystem is at least L1.0.0270, AbaPerls creates the table in a temporary schema and then compares the definition with the current table in the dbo schema. If they are different, AbaPerls investigates whether it is possible to change the existing table with ALTER TABLE commands to achieve the definition in the .tbl file. If this is not possible this is an error. Else AbaPerls prints and executes the necessary ALTER TABLE statements. Note that the ALTER TABLE statements may fail due to objections from SQL Server. For more details on this comparison, see the section Pre-SQL Analysis: Reloading Tables below.
   If you have an older version of the ABAPERLS subsystem than L1.0.0270 or this subsystem is not installed, and the table cannot be dropped, AbaPerls produces an error message.
   AbaPerls supports use of storage properties like compression or placement on a specific filegroup for a table and its keys. If a certain property is not mentioned in the source file, AbaPerls makes sure that the actual settings in the database are retained, and thus permitting a site setting such properties as they see fit. For further details on this topic, see the page Storage Settings for Tables and Indexes.

ALTER TABLE When an ALTER TABLE command appears in an .fkey file, AbaPerls generates code to remove all foreign-key constraints for the table in the current subsystem, not only those in the file, since you may have removed or renamed a few. Foreign keys defined by other subsystems are retained.
ADD CONSTRAINT AbaPerls generates the corresponding DROP CONSTRAINT. (In case there are also CHECK constraints in the .fkey file.)
CREATE TRIGGER AbaPerls generates code to drop all existing triggers for the table or view in question that are in the same subsystem, so if a trigger was removed from the file, it is also dropped from the database. Triggers in other subsystems are retained. (Note that if you do not have the AbaPerls system tables installed, this is makes the entire database a single subsystem, so all existing triggers will be dropped.)
CREATE INDEX
What is said here, applies to permanent tables, not temp tables.
    AbaPerls compares the definition in the source file with the definition in the source file. If the two definitions are the same, AbaPerls prints an informational message that the index is unchanged, and skips loading the index. If the source file and the database have different definitions of an index, AbaPerls submits the CREATE INDEX statement, extending it with the clause DROP_EXISTING=ON.
   AbaPerls has two different strategies for comparing indexes. If the ABAPERLS subsystem is present in the database and is at least at label L1.0.0280, the comparison covers all parts of the CREATE INDEX statement, including the WITH and ON clauses. AbaPerls also compares the index definition against the actual definition and make sure that WITH and ON options not mentioned in the source file are retained. This is discussed further on the page Storage Settings for Tables and Indexes. If ABAPERLS subsystem has a lower version or is absent, AbaPerls only compares index columns, included columns and the WHERE clause of a filtered index. If the source file includes a WITH an ON clause, AbaPerls will always reload the index and inform you with a warning.
   AbaPerls supports all features regular relational indexes and columnstore indexes. AbaPerls has a very rudimentary support for XML and spatial indexes, and AbaPerls will drop and reload them every time you load such an index, and issue a warning to inform you. Observe that if the database has the ABAPERLS subsystem, but an earlier version than L1.0.0280, you will encounter an internal error if you attempt to load a columnstore, XML or spatial index.
CREATE STATISTICS Statistics are handled much in the same way as indexes. An existing statistics will be dropped and recreated only if it has changed.
CREATE VIEW If the view exists, AbaPerls changes CREATE to ALTER.
CREATE PROCEDURE If the procedure exists, AbaPerls changes CREATE to ALTER.
CREATE FUNCTION
CREATE AGGREGATE
If the function exists, AbaPerls changes CREATE to ALTER.
CREATE ASSEMBLY AbaPerls changes CREATE to ALTER. However, this does not work in all cases, so AbaPerls may use DROP + CREATE as a fallback See the page Using the CLR with AbaPerls for details.
CREATE SYNONYM AbaPerls drops all synonyms currently associated with a file in the AbaPerls System Tables, and then recreates the synonyms in the current version of the file, retaining the server/database part of the old definition. See further the page Using Synonyms.
CREATE MESSAGE TYPE
CREATE CONTRACT
CREATE QUEUE
CREATE SERVICE
CREATE BROKRE PRIORITY
AbaPerls drops all service-broker objects currently associated with a file in the AbaPerls System Tables, and then recreates the objects in the current version of the file. See further the page Service Broker Objects in AbaPerls.

Commands not listed in this table are passed as-is to SQL Server.

If AbaPerls in the same file finds ALTER TABLE, CREATE TRIGGER or CREATE INDEX that applies to different tables, this is a breach against the rules for table files in the AbaPerls SQL directory structure. This is a fatal error and AbaPerls does not process the file any further.

Formatting rules:

Here are some examples of what AbaPerls understands:

CREATE PROCEDURE my_sp @a int,
                       @b datetime AS

CREATE TRIGGER ap_sla_ins_upd_tri
              ON abasslabels FOR INSERT, UPDATE AS

ALTER TABLE abainstallhistory  ADD
   CONSTRAINT ap_inh_ap_sla FOREIGN KEY (ss_label)
      REFERENCES abasslabels (ss_label)

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS

And here are some examples of what will confuse AbaPerls:

CREATE PROCEDURE
            name_on_next_line AS

CREATE FUNCTION dbo.SalesByStore (@storeid varchar(30))
-- "dbo." should not be there.
RETURNS TABLE
AS

CREATE INDEX one_ix ON Sometable (somecol)
go
CREATE INDEX two_ix ON SomeTable (othercol)
-- AbaPerls considers "Sometable" and "SomeTable" to be different
-- tables and will flag this is an error.

Don't expect any useful error message if you don't obey to the formatting rules. You may not even see an error message first-time round.

Checks on Conformance to the Subsystem Structure

AbaPerls verifies that the object(s) in the file agree with the name of the file. This check is case sensitive, so if you have a file named my_own_sp.sp, and the file includes:

CREATE PROCEDURE My_own_sp AS
This will result in the error:
Msg 0 Level 16, Line 1, E:\TEMP\My_own_sp.sp
Object name 'my_own_sp' does not match file name My_own_sp.sp. Use -force to override.
AbaPerls SQL Analysis of E:\TEMP\My_own_sp.sp resulted in 1 error.

You can override this check for stored procedures and user-defined function by using the ‑force option. You will still get a warning, but the object is created. For other type of objects, the file name and the object name must match, period, and you cannot use ‑force to override.

If you quote your identifiers (despite the caveat above), you should not include the quote in the file name. That is, if you have:

CREATE TABLE [Order Details] (...

The name of the file should be Order Details.tbl.

For complete information on these checks, see the page on the AbaPerls SQL directory structure.

In the same vein, AbaPerls issues an error if an object appears in a file of which the extension does not match with the type of the object according to the AbaPerls SQL directory structure. You cannot override this check with ‑force.

AbaPerls also checks that a file which is supposed to define an object actually does so. If the file is non-blank after stripping comments and preprocessing, but there are no objects defined in it, this is an error.

Pre-SQL Analysis: Temp Tables and Table Variables

When AbaPerls finds a temp table, a table variable, the declaration of a table type or a schema declaration for OPENXML, AbaPerls makes two modifications to the table definition:

Example: this temp table comes from the AbaPerls procedure ap_sob_report_suspects_sp:

CREATE TABLE #missinginsys(name      sysname         NOT NULL,
                           objtype   ap_description  NOT NULL,
                           loadtime  datetime        NOT NULL,
                           subsystem ap_subsystem    NULL,
                           ss_label  ap_sslabel      NULL,
                           username  ap_ntname       NOT NULL,
                           hostname  ap_ntname       NOT NULL)

This is how the table looks after AbaPerls have replaced the datatypes and added COLLATE clauses.

  CREATE TABLE #missinginsys ( name nvarchar(128) COLLATE database_default NOT NULL ,
 objtype varchar(30) COLLATE database_default NOT NULL ,
 loadtime datetime NOT NULL ,
 subsystem varchar(30) COLLATE database_default NULL ,
 ss_label varchar(15) COLLATE database_default NULL ,
 username nvarchar(40) COLLATE database_default NOT NULL ,
 hostname nvarchar(40) COLLATE database_default NOT NULL ) 

The first modification makes it possible for you to use user-defined data types in temp tables and table variables without restrictions. The insertion of the COLLATE clause makes you virtually independent of the server collation, so you can mix databases with different collations on the same server and still use temp tables without problem. (The lost indentation is due to how AbaPerls parses and reconstructs table definitions.)

Furthermore, when AbaPerls finds a temp-table creation – with CREATE TABLE or SELECT INTO – AbaPerls extracts the temp-table definition and adds the creation staftement to the pre-work code, as well as a matching DROP statement to the post-work code. This nullifies the effect of deferred name resolution a serious misfeature in SQL Server where SQL Server stays silent at compile time if a table in an SQL statement is missing. Furthermore, missing tables often prevent from SQL Server from investigate other errors. For instance, through SSMS, you create a stored procedure and there is a join between a temp table and a permanent table, and you have misspelled a column name in the permanent table. SQL Server will not report the missing column due to that the temp table is non-existing at the time you create the procedure. By adding creating the temp table before issuing the CREATE PROCEDURE statement, AbaPerls lures SQL Server to perform full checks on queries with temp tables.

Please observe that while extracting CREATE TABLE is a simple game for AbaPerls, extracting SELECT INTO is considerably more difficult and this is more prone to fail, resulting in an error message which includes the full SELECT INTO statement as AbaPerls extracted it. For instance, this happens if the query includes a table variable or a CTE (Common Table Expression). If you run into such a problem, your best bet is to replace SELECT INTO with an explicit CREATE TABLE + INSERT. (Which is probably what you should have written in the first place.)

Pre-SQL Analysis: Reloading a Table File

When you load a .tbl file and there is data in the existing table, it is of course not feasible to drop and recreate the table. The same applies when the table is referred to by a foreign-key constraint or by a function or a view created WITH SCHEMABINDING. (SQL Server will not permit the table to be dropped in this case.)

If ABAPERLS subsystem is installed in the database, and the version is at least L1.0.0270, AbaPerls will change the CREATE TABLE statement so that the table is created in a temporary schema to be able to compare the definition in the source file with the database definition. If they are different, AbaPerls investigates whether it is possible to use ALTER TABLE or other statements to make the existing table to align with the definition in the file. If this is not possible, AbaPerls consider this to be an error, as it is not able to fulfil the contract. This may be slightly irritating when you attempt to load a .tbl file through ABASQL, since the difference is not necessarily a user error. But it is essential that you get an error message when the .tbl file is loaded by an update script or through DBBUILD with one of the options ‑rebuild and ‑restruct, so that you see the error when you view the log with LISTERRS. Note that if you get an error message about the tables being different or ALTER TABLE failing, this gives you an acknowledgement that the .tbl is correct syntactically, as else AbaPerls would have been able to compare the two definitions.

AbaPerls prints all statements – ALTER TABLE – and others, it executes to implement the change so that you seem in the log. An error message follows directly on the statement that failed.

There are many properties associated with tables, and AbaPerls only considers properties that AbaPerls expects to appear in a .tbl file. The main meat is of course columns and constraints, save for FOREIGN KEY constraints (since foreign key constraints should be defined in .fkey files and are not permitted in .tbl files) AbaPerls also understands various storage settings for filegroup, compression etc. As this is a fairly advanced topic, this is discussed on a separate page, Storage Settings for Tables and Indexes, and not further discussed here. AbaPerls does not consider settings for Change Tracking, Change Data Capture, replication or anything else discussed in this topic or the storage-settings page.

AbaPerls first checks if the column names are the same in the two tables, and whether the order is the same. If this not is the case, the following applies:

When adding a column, AbaPerls considers all properties that can be added with ALTER TABLE ADD with exception for the NOT FOR REPLICATION clause.

AbaPerls compares all columns to see if any has changed. AbaPerls considers the column properties below. If the item is followed by ALTER, this means that AbaPerls will attempt to use ALTER TABLE to transfer the difference, Error means that AbaPerls will give up and raise an error, typically because ALTER TABLE is not possible.

If the altered column is referred to by a CHECK or DEFAULT constraint, AbaPerls drops and recreates the constraint. If the column is indexed, AbaPerls does not drop and recreate the index, as this could be a costly operation. AbaPerls does not consider properties related to replication. That is, AbaPerls will not detect any differences in such settings.

Note: you may find that AbaPerls errors out, saying that a computed column has changed despite you have not touched it. This can happen if the column was created under compatibility level 100 (SQL 2008) or lower, and the compat level now is higher. This is because SQL Server does not save the expression for the computed column as-is, but normalises the expressions. AbaPerls tries to cater for this by adding the column in the original table as a temporary column in the table in the temporary schema so it can compare the text. However, this may still fail if the computed column uses convert(). Say that you have a column with the definition col AS convert(bit, 1). SQL 2008 stores this as (CONVERT([bit],(1),0)) where as in SQL 2012 stores it as (CONVERT([bit],(1))). Note that ,0 is missing here. However, if the ,0 is included, SQL 2012 does not remove it. For this reason, you can in most cases avoid the load error by adding the convert style ,0 to the table definition. The exception is the types date, time, datetime2 and datetimeoffset when the source is a string, since in this case the conversion produces different result with or without ,0.

As with columns, AbaPerls compares CHECK, DEFAULT, PRIMARY KEY and UNIQUE constraints and drops them and creates if they have changed. The following notes apply:

If the table reload does not succeed, you can use a update script generated by DBUPDGEN to bring the database in line with your source file.

Note: as noted above, ALTER TABLE is not employed if the table is empty and not referred to by foreign keys. Sometimes you may want to force an attempt ALTER TABLE to verify that your change is actually compatible with AbaPerls. This is possible in update scripts generated by DBUPDGEN, see further the topic Forcing ALTER TABLE in abasqlfile on this page.

Pre-SQL Analysis: Supplemental SQL Checks

AbaPerls performs a couple of supplemental checks for best practice or to enforce conformance with AbaPerls conventions. Depending on the check, AbaPerls flags the violation in one of the three ways below:

Use of TOP with INSERT, UPDATE, DELETE and MERGE

Starting with SQL 2005, SQL Server permits to use the TOP keyword with DML statements (INSERT, UPDATE, DELETE and MERGE). For instance you can say:

UPDATE TOP (@rows) tbl
SET    col =
FROM ....

If you attempt to use this syntax, AbaPerls produces an error message for two reasons:

  1. This is a feature of dubious value, because there is no way to control which rows that get updated.
  2. The syntax makes it very difficult for AbaPerls to find the target table of the statement and verify its existence.

If you want to perform a batched INSERT, you should put the TOP clause in the SELECT statement instead. For an UPDATE, DELETE or MERGE you could use the pattern below:

WITH CTE AS (
     SELECT col, ... rowno = row_number() OVER(ORDER BY yourcriteria)
)
UPDATE CTE
SET   col = ...
WHERE rowno <= @rows

to get a predictable statement.

Note: This check is performed as part of Object Checking, and AbaPerls will only flag this as an error if SQL Server accepts the file.

SET ROWCOUNT

Microsoft has deprecated the use of SET ROWCOUNT in combination with INSERT, UPDATE, DELETE and MERGE but curiously not with SELECT. AbaPerls is one step ahead and deprecates SET ROWCOUNT across the board. To permit a smooth transition, AbaPerls produces an error or a warning as discussed above whenever SET ROWCOUNT is encountered.

Instead of SET ROWCOUNT, use SELECT TOP. Note that to use a variable with SELECT TOP, you need to enclose it in parenthesis:

SELECT TOP(@noofrows) col1, col2 FROM tbl WHERE ...

= NULL

While this it's legal SQL to say IF @x = NULL, it's rarely meaningful. Since NULL is an unknown value NULL can never be equal to anything, not even another NULL. Or for that matter unequal of anything. Per the tri-state logic of SQL, all comparisons with NULL have the value UNKNOWN.

Since this is a common mistake by inexperienced SQL programmers, AbaPerls checks for this mistake and always flags it as an error.

Note: this check is performed as part of Object Checking, why AbaPerls will not flag incorrect NULL usage, if SQL Server finds errors in the file.

Checks for CREATE TABLE

AbaPerls performs a number of checks for table definitions, both CREATE TABLE for permanent tables and temp tables, and table variable and return tables for table-valued functions:

EXECUTE AS

SQL 2005 permits you to add the clause WITH EXECUTE AS to a stored procedure or any other module. This causes the procedure to execute in the context of some other user. This is a very dangerous clause, as it can wreak havoc of auditing and row-permissions schemes based on SYSTEM_USER and similar functions. For this reason AbaPerls does not permit the use of this clause at all.

Note: EXECUTE AS can also be a statement or appear as a part some Service Broker statements, e.g. ALTER QUEUE. All such usages are permitted. It's only WITH EXECUTE AS in the declaration of stored procedures and other modules that is not permitted.

Private Names

AbaPerls considers a name that starts with a $ character and then includes one more $ character to be a private name. The second $ may be the last character in the name, or there may be a suffix.

A private name may only occur in a file that defines a stored procedure or a user-defined function and the part of the private name between the two $ characters must agree with the name of the procedure/function. Private names are intended for table types or XML schema collections to be used by one single stored procedure for a parameter. AbaPerls vigorously checks that these names are not mistakenly used in other modules, as this could cause problems in update scripts.

That is, this is not legal:

CREATE PROCEDURE my_proc AS
DECLARE @tbl [$her_proc$]
INSERT @tbl ...
EXEC her_proc @tbl

If you want to use the same table type in two stored procedures, you should declare it in a .tbltyp file that you put in the TYPE directory.

Check that $DEPENDSON is Present

AbaPerls checks if $DEPENDSON is present in the cases below:

If AbaPerls finds that $DEPENDSON is missing, it issues an error or warning depending on the tool.

Pre-SQL Analysis: Permissions on Objects

Normally you do not put GRANT statements directly in the .sp, .tbl files etc. AbaPerls permits you to set up rules for permissions in files called grant.template. A file with this name contains GRANT, REVOKE and DENY statements where you can replace the object name with a Perl regular expression. The GRANT/REVOKE/DENY statement should be followed by a comment that contains the keyword for the type of objects the line applies to. AbaPerls executes all statements where the current object matches the regular expression and the object type. Here is a simple example:

GRANT SELECT  ON .* TO dvp --  TABLE
GRANT SELECT  ON .* TO dvp --  VIEW
GRANT EXECUTE ON .* TO dvp --  PROC
GRANT EXECUTE ON .* TO dvp --  FUNCTION

This file gives the group dvp SELECT permission on all tables and views and EXECUTE permission on all procedures and functions. The dot before the asterisk means "any character". The asterisk means "zero or more times".

If more than one line that matches the same object, AbaPerls executes the lines in the order they appear in the file. The effect of this example is that the group fond_usr gets EXECUTE permissions on all stored procedures, save those that start with zz_:

GRANT  EXECUTE ON .*    TO   fond_usr
REVOKE EXECUTE ON zz_.* FROM fond_usr

As you can see from this example, PROC is the default for the object type if you leave it out.

This system is devised mainly to support simplistic permission models, but by the use of regular expressions, there is space for greater flexibility. You find a crash-course in regular expressions on the page for SSREPLACE. A few notes about the regular expression that are specific to grant.template:

If some single stored procedure is to be run by a special user, it might be simpler to put the GRANT statement directly in the file. Note that this must be a user that is created when you build an empty database with DBBUILD – the build master may not fancy error messages for unknown users.

To grant permission on some type of objects, SQL Server requires you prefix the object name with the "object class". This applies to for instance to (table) types and XML schema collections. That is, the SQL syntax to grant EXECUTE permission on a table type is:

GRANT EXECUTE ON TYPE::mytabletype TO some_user

However, in grant.template you should not include this prefix; AbaPerls will add it for you. (If you add it, AbaPerls will not be able to match the line.) Thus, the line in grant.template should read something like:

GRANT EXECUTE ON .* TO some_user  -- TYPE

When you load a file in a subsystem, AbaPerls looks for grant.template in two places: 1) The version directory (global). 2) In the top directory of the AbaPerls SQL directory structure (local). AbaPerls looks up these files according to the AbaPerls file-lookup order, with one twist: when reading from version control, AbaPerls always gets the latest version of the files, no matter which label you load the subsystem from.

If there is both a global and a local grant.template, AbaPerls will read both files and apply them as they were a single file, with the lines from global file first. If you have a version-before-subsystem structure, this permits you to have a single grant.template that is common for all subsystems. If a particular subsystem needs different permissions, you can supplement the rules in the global file in a local grant.template.

Say that there is a system in $/Project/OurSystem/1.00 and there is a file $/Project/OurSystem/1.00/grant.template which contains this line:

GRANT SELECT ON .* TO our_users -- TABLE

That is, generally the role our_users has SELECT permission on tables. In the subsystem MAIN and EXTRA and there is no $/Project/OurSystem/1.00/Main/SQL/grant.template or $/Project/OurSystem/1.00/Extra/SQL/grant.template. However, you don't want users to be able to access table in the AUDIT subsystem, why there is a $/Project/OurSystem/1.00/Audit/SQL/grant.template containing this line:

REVOKE SELECT ON .* TO our_users -- TABLE

That is, by using REVOKE you can undo the permissions in the global grant.template.

It might be the case that the subsystem is so special that you don't want the permissions in the global grant.template to apply at all. In this case you can put this line first in the local grant.template:

$NOINHERIT

This instructs AbaPerls to ignore the global file.

While $NOINHERIT may look like a Preppis macro, it really isn't. More specifically, neither of the two grant.template are run through Preppis, so you cannot use $IFDEF etc in these files. Note that $NOINHERIT must appear on the very first line in the file; it cannot be preceded by comments or blank lines.

AbaPerls thinks that using a global grant.template is a good idea, so if there is no file in the version root, you will get a warning. You will not get a warning if there is no local file.

When you have a install kit created with ‑noexec ‑get, there is no version root. In such a kit, the global file exists directly under /subsystem and thus is duplicated. Since this is a generated structure, this should not be much of a problem. If you want to manually set up a pseudo kit as discussed under the AbaPerls File-Lookup Process above, you will need to duplicate the file in the same way.

Caveat: if you remove a line from grant.template, this will have no effect when you reload existing procedures, functions and views with ABASQL, since AbaPerls changes CREATE to ALTER, in which case all existing permissions are retained. However, you can use DBBUILD ‑rebuild with the ‑revokeall to implement the change.

Pre-SQL Analysis: Other Miscellaneous Fixes

Retrieving Name of Current Subsystem

AbaPerls creates the temp table #current$subsystem:

CREATE TABLE #current$subsystem(subsystem varchar(80) NULL)

There is one row in this table. The value of the column subsystem is the name of the current subsystem or NULL, if none has been defined. You can use this table to retrieve the name of the current subsystem in your T‑SQL code. The most likely use for this is in an INSERT-file. Say that you have a pre-loaded table to which several subsystems add data. To keep track of which subsystems which own which rows, you decide to add a subsystem column to this table, and with help of this temp table you can arrange for this column being filled in automatically. (The best way to do this would be to have a common stored procedure that loads data into this table; see the page for INSFILGEN for an example.)