How AbaPerls Loads a File

This page describes the AbaPerls file-loading process which is activated each time you load a file with AbaPerls. You can load a singular file with ABASQL, or many files as part of a database build with DBBUILD or through an update script generated by DBUPDGEN. AbaPerls does quite a few things behind your back, and understanding what is happening can reduce the amount of confusion that could arise.

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: Object Checking
    Pre-SQL Analysis: Checks on SQL Syntax
         Old-Style Outer Joins
         = NULL
         Missing WITH for Table Hints
         Checks on CREATE TABLE
         EXECUTE AS
    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:

Looks up the file through the AbaPerls file-lookup order.
Reads the file, stripping it of comments.
For production and test databases, performs a version check against what's in the database.
Runs the file through the Preppis preprocessor.
Pre-SQL Analysis, detailed in the several sections on this page, and which may add extra code before and after the file, as well alter the code of the file itself.
Puts back the comments that were stripped, unless the -crypt configuration option is in force.
Sends the resulting code to SQL Server.

The comment-stripping is something you are not very likely to notice, since the comments are restored before the code is passed to SQL Server. But if you place the comments in funny places, they may not reappear exactly where you put them

When you run DBBUILD or an update script generated by DBUPDGEN with -noexec, AbaPerls only performs steps 1, 2 and a modified version of step 4: Preppis only processes $INCLUDE, $REQUIRE, $DLLINCLUDE, EXTERNAL NAME and $USEDBY. The resulting code is thrown then away and not used as such. The point is that you combine -noexec with -get to create a kit for installation at a customer site and you want the include files in the kit too.

The code that AbaPerls sends to SQL Server consists of seven parts:

BEGIN TRANSACTION
Pre-work code.
The file itself.
Object checking.
Post-work code.
Procedure calls to update the AbaPerls system tables, if the ABAPERLS subsystem is installed.
COMMIT TRANSACTION or if an error occurred somewhere, ROLLBACK TRANSACTION.

AbaPerls starts a user-defined transaction to maintain integrity between the AbaPerls own tables and the SQL Server system tables. Thus, if the there are syntax errors in the file, AbaPerls will roll everything back. Now, a file may include a command that may not appear in a user-defined transaction, for instance a call to the system procedure sp_addrole. The reaction of AbaPerls is a pragmatic one: AbaPerls simply starts all over again, this time without starting a transaction. (There is a known issue with the transaction arrangement which concerns SQL 6.5 only.)

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 looks for the file in three different places in this order:

On disk with the filename as given.
On disk in an AbaPerls SQL directory structure.
In SourceSafe in the AbaPerls SQL directory structure.

In many cases, AbaPerls will look only in the first two places, or only in the third. Exactly where it looks, depends on command-line switches such as -VSS, -config, and -use_disk. The exact rules are somewhat different for ABASQL, DBBUILD and the update scripts generated by DBUPDGEN; please see each command description for the details. You should also beware of that AbaPerls may change what is the current directory, if there is an SS-FILES.LIS in the current directory to an SQL structure for the specified subsystem. This is detailed further on the page Installation at a customer site.

Here follows a more detailed specification for each step:

1. On disk with the filename as given

That is, if you simply 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. You can also specify a file with complete path, e.g. C:\TEMP\NISSE_SP.SP.

2. On disk in an AbaPerls SQL directory structure

If step 1 fails, AbaPerls examines whether the path to the current directory includes any of the special directory names in the AbaPerls SQL directory structure – i.e. MESSAGE, TYPE, TBL, SP, FUNCTIONS, INCLUDE, VIEW or SCRIPTS. In such case the current directory is within the AbaPerls structure and the directory above the special name is the top directory for the structure. If there is no special name in the path to the current directory, AbaPerls will assume that the current directory is the top directory of an AbaPerls structure – regardless whether its name is SQL or not.

Having determined the top directory for the structure, AbaPerls looks up the file in the special directory where is should reside according to its extension.

3. In SourceSafe in the AbaPerls SQL directory structure

For AbaPerls to pursue step 3, it must have an SS-path that specifies a top project in an AbaPerls SQL directory structure. As in step 2, AbaPerls uses the file extension to determine in which subproject in the structure to look for the file. If you somewhere have specified a version number, date or label, AbaPerls will retrieve the corresponding version of the file.

Examples

Assume that your local directory is C:\PROJECTS\ABC\SQL\SP. To the AbaPerls tool you are using you have specified the SS-path C:\SOURCESAFE/$/ABC/3.20/SQL. You pass AbaPerls the file specification SOME_UDF.SQLFUN. AbaPerls will look for the file in these three places in order: (The part of the paths in bold face represents the fixed part of the path, and the text in regular type face is the what AbaPerls tacks on for the file.)

On disk for C:\PROJECTS\ABC\SQL\SP\SOME_UDF.SQLFUN (Current directory)
On disk for C:\PROJECTS\ABC\SQL\FUNCTIONS\SOME_UDF.SQLFUN (SP is one of the special directory names in the AbaPerls SQL directory structure. And in this structure .SQLFUN files are expected to be in the FUNCTIONS directory.).
In the SourceSafe database C:\SOURCESAFE for the file $/ABC/3.20/SQL/SQLFUN/SOME_UDF.SQLFUN. (Again according to the AbaPerls SQL directory structure.)

Assume now that your local directory is C:\PROJECTS\ABC\TEMP and the SourceSafe database is the same. AbaPerls will now look in these places:

On disk for C:\PROJECTS\ABC\TEMP\SOME_UDF.SQLFUN (Current directory)
C:\PROJECTS\ABC\TEMP\FUNCTIONS\SOME_UDF.SQLFUN. (As the path to current directory does not include any special name, it assumed to be a top directory itself.)
In the SourceSafe database C:\SOURCESAFE for the file $/ABC/3.20/SQL/SQLFUN/SOME_UDF.SQLFUN. (Again according to the AbaPerls SQL directory structure.)

Here is an example with subdirectories. Assume that your current directory is C:\PROJECTS\ABC\SQL\SP\SUB and you specify SUB2\MYTABLE.TBL. The SourceSafe specification is still C:\SOURCESAFE/$/ABC/3.20/SQL. Here are the places AbaPerls considers:

C:\PROJECTS\ABC\SQL\SP\SUB\SUB2\MYTABLE.TBL as deduced from the current directory.
C:\PROJECTS\ABC\SQL\TBL\SUB2\MYTABLE.TBL from the AbaPerls SQL directory structure.
$/ABC/3.20/SQL/TBL/SUB2/MYTABLE.TBL in the SourceSafe database C:\SOURCESAFE.

Note here that AbaPerls never will look for C:\TEMP\SQL\TBL\MYTABLE.TBL; the sub-directory specification SUB2 always applies.

In these examples, AbaPerls looked in all three places in the lookup order, but recall that depending on command and command-line switches, AbaPerls may only look at a subset.

Messages from SQL Server

Messages from SQL Server stemming from the 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, then this is typically a message from AbaPerls itself. (E.g. warnings about non-existing objects.)

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. For instance, the warning for a missing stored procedure (...can't add rows to sysdepends...) appears to most of the time have a bogus line number. There are also issues with SQL2000 reporting incorrect line numbers for some errors in INSERT statements. And, overall, with its fairly simple parser, T-SQL may not discover an error such a missing END until the end of file.

Speaking of the warning about missing stored procedures, SQL Server reports this warning also for a stored procedure that calls itself. However, in this case, AbaPerls discards that warning, so you won't see it.

If errors occurs in any of the SQL code that AbaPerls adds before or after the file (parts 1-2 and 4-7 above), 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 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. They fall into two groups. The first group is always the same:

   SET DEADLOCK_PRIORITY LOW
   SET IMPLICIT_TRANSACTIONS OFF
   SET CURSOR_CLOSE_ON_COMMIT OFF
   SET NOCOUNT ON
   SET NUMERIC_ROUNDABORT OFF
   SET XACT_ABORT OFF
   SET DISABLE_DEF_CNST_CHK OFF   -- SQL 6.5 only

The second group consists of these set commands: ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_NULLS, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL (Not on SQL 6.5) and QUOTED_IDENTIFIER. Whether these settings are ON or OFF depends on the configuration options -Adefaults, -Anulls, -Apadding and -Aquoted.

Since AbaPerls resubmits the SET commands for each file, you can override the settings for an individual file by putting the desired SET commands in that file. Note however, that use of other SET commands than those listed here in files outside the definition of stored procedures is not supported by AbaPerls. DBBUILD and the update scripts generated by DBUPDGEN maintains the same connection throughout their executions, so the effect of a setting you make in one file, sticks to the end.

Version Checks for Production and Test Databases

Note: the checks detailed in this section are not performed on SQL 7 and SQL 6.5.

With config-option -envrionment, 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.

The ultimate purpose of these tests is to handle the situation that a developer uses ABASQL to loads 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 that file, but an earlier version of it. Normally, in a production environment, you proabably 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.

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:

All tools, PROD: For existing files, when there is a value in the column ss_version in the table abasysobjects, i.e. the file was previously loaded from SourceSafe, you must load the file from SourceSafe, or you will get an error message. If you were to load from disk, an update script would not be able to conclude whether the database has an older or newer version of the file. The check is not performed for new files (as sometimes you need to play with test versions, even in a production database), or for files with no value in ss_version (damage already done).
All tools, TEST: If you load from disk, you will get a warning recommending you to load from SourceSafe.
ABASQL, TEST and PROD: When you load a file from SourceSafe with the -VSS option, you must load it from the same SourceSafe project that the most recent upgrade script for subsystem was run from. The check is not performed for new files. Note: If the version of the ABAPERLS subsystem is L1.0.0080 or earlier, this check is instead performed against the SourceSafe path the file itself most recently was loaded from, and which may be different from the subsystem. You can suppress this older check by clearing abasysobjects.ss_version to force a load from the correct SourceSafe project.
ABASQL, TEST and PROD: If you load a file from SourceSafe, and you explicitly specify a version with the -label option and you load an older version than what's currently in the database, you will get a warning. (The higher version might have been installed by mistake – or you are making a mistake now.)
Build tools, TEST and PROD: If the there is no SourceSafe version for the file in abasysobjects, you get a warning. (As this unknown version could be ahead of or behind the version from the build tool.)
Build tools, TEST and PROD: If the version number in abasysobjects is higher than the version number that the build tool is about to load, but the SourceSafe project in abasysobejcts.path is different from what the build tool uses, you get a warning. (This may be OK, if there were more checkins in the old version-subproject than in the new.)
Build tools, PROD: If the version number in abasysobjects is higher than the version number that the build tool wants to load, and the SourceSafe projects are the same, the build tool ignores the file and does not load it. The tool leaves message with level 9 in the log file. For update scripts you can override this by setting the AbaSql property Low_version_force, see the DBUPDGEN page for details. (To do the same with DBBUILD -rebuild, simply specify -environment DEV together with -force, and then restore the setting when you are done.)
Build tools, TEST: If the version number in abasysobjects is higher than the version number that the build tool wants to load, and the SourceSafe projects are the same, the build tool will install the lower version, but leave a warning with level 10 in the log file.

Note: "loading from SourceSafe", 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 SourceSafe, 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. Note that this does not extend to files loaded through $INCLUDE; for these AbaPerls does not perform any version checks at all.

For CLR assemblies, AbaPerls does not version-check the .ASSEM file, but instead performs the check for the accompanying DLL. (Since the .ASSEM file only holds a CREATE ASSEMBLY statement which is not like to change very often.)

For files of the types .SP (stored procedurs) 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. The point with this is that this permits you to enter 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.

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. However, AbaPerls handles this for you for most kind of objects, and adds pre-work code to drop any existing object or it changes CREATE to ALTER. Thus, in these cases you should not add your own code 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 we 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.
EXEC sp_addtype	AbaPerls generates an `sp_droptype`, which is executed only if there is no table/view column or procedure/function parameter with this type. (Note that variables in procedure, triggers and functions are not considered, and neither are temp tables in procedures or triggers.)
EXEC sp_bindrule	AbaPerls does not generate any `sp_unbindrule`.
EXEC sp_bindefault	AbaPerls does not generate any `sp_unbindefault`.
CREATE TABLE	If the table is not a temp table, AbaPerls generates a DROP TABLE which is executed only if the table is empty. AbaPerls does not handle referring foreign-key constraints. (Code for this is generated by DBUPDGEN for the update scripts.) If there are dependent objecs (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.)
ALTER TABLE	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. (AbaPerls assumes that ALTER TABLE only occurs with adding constraints, per the rules for table files in the AbaPerls SQL directory structure.) 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 that are 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 CREATE STATISTICS	Unless the table is a temp table, AbaPerls compares the definitions of the indexes and statistics in the file with their definitions in the database, and for the indexes and statistics that are the same in the file and in the database, AbaPerls replaces the CREATE statement with an informational message, to avoid that they are recreated unnecessarily, which for big tables could take considerable time. If an index or a statistics is different in the database and in the file, AbaPerls generates a DROP INDEX/STATISTCS or adds a WITH DROP_EXISTING as it sees fit. Thus, in this case the index is recreated. (Or created if it is not in the database at all.) If there is an index or statistics for the table or view in the database, which is not in the file, and which according to the AbaPerls system tables is in the current subsystem, AbaPerls drops this index/statistics. (Manually created statistics only, AbaPerls ignores auto-statistics). Indexes/statistics in other subsystems are not affected. All indexes and statistics in the file must belong to the same table or view, see below. Note: if you employ any of the rarely used WITH or ON options in the CREATE INDEX statement, AbaPerls will always rebuild that index as AbaPerls does not care to parse these. For CREATE STATISTICS the opposite applies: AbaPerls will not rebuild a statistics with a WITH clause, but will also never notice if you have changed the clause.
CREATE VIEW	SQL 6.5: AbaPerls generates the corresponding DROP VIEW. SQL7 and later: if the view exists, AbaPerls changes CREATE to ALTER.
CREATE PROCEDURE	SQL 6.5: AbaPerls generates the corresponding DROP PROCEDURE. SQL7 and later: if the procedure exists, AbaPerls changes CREATE to ALTER.
CREATE FUNCTION	SQL 6.5: AbaPerls generates the corresponding DROP FUNCTION. SQL7 and later: 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 defintiion. See further the page Using Synonyms. If the ABAPERLS subsystem is not present in the database, AbaPerls does not give CREATE SYNONYM any particular consideration.

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:

The commands, together with the following name, must be on one line and be the first words on that line. (Leading white-space permitted.)
...except for ALTER TABLE tbl ADD CONSTRAINT where CONSTRAINT should appear first on the next line.
You are advised to restrict yourself to names with regular SQL identifiers. AbaPerls makes a half-hearted attempt to handle identifiers quoted with "" or []. See this known issue for more details.
In T-SQL, identifiers are case-insensitive, if you have a case-insensitive collation in the database. AbaPerls does not honour this, but always assumes identifiers to be case-sensitive. Similarly, AbaPerls thinks that quoting with "" or [] should be applied consistently.
Names must not include owner or database components.
For indexes and statistics: if you use WITH or ON clauses, you must put these on the same line as the closing parenthesis, and you must insert a line break in them.
For CLR objects you must have AS EXTERNAL NAME one single line. (AbaPerls will give you an explicit error if it finds EXTERNAL NAME without an preceding AS.)

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 objects in the file agrees 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.

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

In the same vein, AbaPerls issues a 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 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 file is non-blank after stripping comments and preprocessing, but does not define any objects, this is an error. Note that this permits you use $IF/$IFDEF to define a way an entire stored procedure that is to be loaded on a certain site or for a certain version of SQL Server.

Pre-SQL Analysis: Temp Tables and Table Variables

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

All user-defined data types are replaced with their definitions (sans rules and defaults).
(SQL 2000 and later only): for columns with a character datatype (char, varchar, nchar, nvarchar, text, ntext), AbaPerls adds a COLLATE clause unless one is already present. The COLLATE clause uses the default collation of the database.

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)

Say that the collation of the database is Polish_CS_AS. This is how the table looks after AbaPerls have replaced the datatypes and added COLLATE clauses.

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

The first modifications makes it possible for you to use user-defined data types in tempdb and table variables without putting them in tempdb and model. (And SQL 2000 does not support the use of user-defined data types in table variables at all.) 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.

Pre-SQL Analysis: Object checking

This part of Pre-SQL Analysis is not performed on SQL 6.5 as this version of SQL Server performs this task itself.

In SQL7 and later, SQL Server does not inform you if you refer to a table that does not exist when you create a stored procedure. The error will not appear until you execute the procedure and you reach the statement that refers to the non-existing table. However, if all tables in a query exist, SQL Server will not permit you to refer to any non-existing columns when you create the procedure.

AbaPerls sifts through the code to find references to objects that SQL Server would be quiet about if they were missing.If the file loads without error from SQL Server, AbaPerls verifies the existence of all objects it has found. (AbaPerls does not perform the check if there are SQL errors, since bad syntax could have lured AbaPerls to register objects that are something else.) AbaPerls checks for these type of objects:

Tables, views and user-defined table-valued functions.
User-defined scalar functions, including user-defined aggregates (but see below).
Service-Broker queues in the RECEIVE statement.
Service-Broker services and contracts in the BEGIN DIALOG statement.
Service-Broker message types in the SEND statement.

For all references to non-existing objects, AbaPerls will emit an error or a warning message for that reference. Whether you get an error or a warning, depends on the circumstances. AbaPerls will let it suffice with a warning in any of these cases:

The ABAPERLS subsystem in absent in the database.
The ABAPERLS subsystem is on a lower version of L1.0.0022.
You are running SQL 7.
-force was specified.

The reason you only get a warning in the first three cases, is that AbaPerls have two ways to verify the existence of objects, of which one relies of a table-valued function and a stored procedure added in L1.0.0022, and one fallback method which works these two. Since the backup method cannot identify objects in other databases, AbaPerls does not have the guts to raise an error in this case.

As for the use of -force, this is indended for the case where AbaPerls might err, to give you a chance and sneak by. This is assumed to be so rare, that the error message does not remind you that -force is available.

In order to verify that the temp tables you refer to exist, AbaPerls tries to extract all CREATE TABLE and SELECT INTO statements in the stored procedure that creates a temp table, and adds these statements to the pre-work code. This has also the effect that SQL Server will verify that queries that involves the temp tables do not refer no non-existing columns. (Without this in INSERT #temp SELECT complex-query, SQL Server will not about complex-query, even if it does not refer to #temp. Imagine how fun it is to get a run-time error because of a missing alias.)

AbaPerls does not have a complete SQL parser, but make some reasonable assumptions to find CREATE TABLE and SELECT INTO. The first command is fairly straightforward: you must put CREATE, TABLE and the table name as the first three words on a line, then AbaPerls will find the rest.

To find SELECT INTO, AbaPerls relies on that you put words like SELECT, INSERT etc as the first word of a line. That is, words that introduce an SQL statement. If the SELECT INTO statement includes variables, AbaPerls replaces these with NULL. If the SELECT INTO includes a UNION clause, AbaPerls ignores the part that comes after UNION. Beware that under unfortunate circumstances, the SELECT INTO in the pre-work code may fail or take a very long time to run. The recommended workaround is to use CREATE TABLE instead.

If the stored procedure refers to temporary tables that are supposed to be created by the caller? Then there should be an $INCLUDE of a file that includes the definition of the temporary table, which should be in a batch which precedes the batch that creates the procedure.

Scalar user-defined functions present a particular challenge for AbaPerls. AbaPerls looks for the pattern ident1.ident2( which in SQL 2000 must be call to a UDF in syntactially correct SQL. However, in SQL 2005, it can also be an invocation of a method of an XML column or a CLR user-defined type, and it's beyond AbaPerls capabilities to determine the data type of a column in a query, or even determine that it is a column. To sort this out AbaPerls ignores the pattern ident1.ident2.ident3(, and only checks ident1.ident2(. This means that if you have the code:

   SELECT db.dbo.func1(), dbo.func2()

AbaPerls will only check that func2 exists, but not func1. In the same vein, consider this piece of correct SQL code:

   SELECT t.item.value('@a', 'int'),
          item.value('@b', 'int')
   FROM @xml.nodes('/x/y') AS t(item)

Here AbaPerls will incorrectly say that item.value on the second line is undefined. The remedy is to always use a table prefix with an XML or CLR column when you use a method, to keep AbaPerls silent. Note that if you would misspell the table alias, for instance tt.item.value, AbaPerls will not tell you.

A similar issue exists with the XML method .nodes() which is table-valued. When you use .nodes(), you must always use a prefix, for instance

   CROSS APPLY tbl.xmlcol.nodes(...) AS t(item)

AbaPerls will assume that a three-part name that ends in nodes and is followed by a parenthesis refers to the XML method and ignore it.

See here for known issues with object checking.

Pre-SQL Analysis: Supplemental Checks on SQL Syntax

Beside object checking, AbaPerls performs a number of supplemental checks on SQL syntax to flag for obsolete syntax, or just poor coding practice.

Old-style Outer Join

Originally, to perform outer joins in SQL Server, you used the *= and =* operators. In SQL 6.5, Microsoft added the ANSI syntax with LEFT|RIGHT|FULL OUTER JOIN and deprecated the use of the older operators. There are several restrictions with the old operators, and also quite some funny quirks, why use of them is highly unrecommendable.

Of this reason, AbaPerls flags usage of these operators. How it is flagged, depends on how you invoked AbaPerls:

If you use ABASQL, you will get an error message and the object will not be created. You can override by using the -force option.
If you run an update script that was generated by DBUPDGEN after this check was added, AbaPerls will generate a warning but create the object. The same is true if you use ABASQL with -force.
If you use DBBUILD or an older update script you will get a message with level 9. (And they will thus not display if you view a log from DBBUILD with LISTERRS.)

The reason for these different levels is to permit for a smooth transition if you have plenty of use of these old-style joins.

In some distant future, AbaPerls may always flag this condition as an error. (Unless SQL Server beats us to it. Microsoft will take this out of the product at some point.)

= 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 abother 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 unexperienced SQL programmers, AbaPerls checks for this mistake, and flags it according to how you invoked AbaPerls:

If you are using ABASQL or an update script generated by DBUPDGEN after this check was introduced you get an error, and the object will not be created.
If you use DBBUILD or an older update script, AbaPerls will let it suffice with a warning.

Note: SQL Server has the setting SET ANSI_NULLS OFF, under which comparisons with NULL do return TRUE or FALSE. But you should not use this.

Missing WITH for Table Hints

In SQL 2005, you are required to use WITH for table hints, as in:

   SELECT col1, col2, ... FROM tbl WITH (INDEX = pk_tbl) WHERE ix = 1

There is one exception: WITH is not required for a one-word hint. That is (NOLOCK) does not require WITH, (UPDLOCK, ROWLOCK) does.

To make your transition to SQL 2005 speedier, AbaPerls flags missing WITH in one of these ways:

If you are using ABASQL or an update script generated by DBUPDGEN after this check was introduced you get an error, and the object will not be created.
If you use DBBUILD or an older update script, AbaPerls will let it suffice with a warning.
If you are running against SQL 6.5, AbaPerls will not perform the check, as this usage of WITH is not legal syntax on that platform.

Note: this check is performed in conjunction with object checking, and AbaPerls will not flag missing WITH, if SQL Server found errors in the file.

Checks on CREATE TABLE

AbaPerls performs two checks on coding style on CREATE TABLE statements. AbaPerls maintains these opinions about table definitions:

Only one column per line.
There must be a NULL/NOT NULL indicator.

This applies to both permanent and temporary tables.

AbaPerls flags violations against these rules with style messages. A style message is different from a warning in that the severity level is only 9; for warnings it is 10. Thus, when displaying the errors in the log from DBBUILD, LISTERRS will only list the errors if you specify 9 or lower as the severity level.

EXECUTE AS

SQL 2005 permits 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 wreck havoc of auditing and row-permissions schemesd based on SYSTEM_USER and similar functions. For this reasons 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.

Pre-SQL Analysis: Permissions on Objects

Normally you do not put GRANT statements in your code, but rather you let AbaPerls add this to the post-work code.

The permissions on the objects that are created in a file, are determined by the file GRANT.TEMPLATE which in AbaPerls SQL directory structure you find in the top directory. AbaPerls looks up this file according to the AbaPerls file-lookup order, with one twist: when reading from SourceSafe, AbaPerls always gets the latest version in SourceSafe and thus ignores any given version number, date or label. Note also that GRANT.TEMPLATE is not run through Preppis, so you cannot use $IFDEF etc in this file.

AbaPerls wants you to use GRANT.TEMPLATE, so you will get a warning if AbaPerls cannot find the file.

GRANT.TEMPLATE should consist of lines with GRANT, DENY and REVOKE statements where you have replaced the object names with regular expressions in Perl syntax. The statements should be followed by a comment saying TABLE, PROC, FUNCTION or VIEW. The statement will be applied to an object if it matches the regular expression and is of the stated object type. An example of a simple file:

   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 actually the default for the object type.

This system is devised mainly to support simplistic permission models, but by the use of regular expression, there is space for a greater flexibility. You find a crash-course in regular expressions on the page for SSREPLACE. 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.

Caveat: if you remove a line from GRANT.TEMPLATE, this will have no effect when you reload existing procedures, functions and views in SQL7 and later, since AbaPerls changes CREATE to ALTER, in which case all existing permissions retained. The best strategy is probably to change GRANT/DENY to REVOKE instead.

Pre-SQL Analysis: Other Miscellaneous Fixes:

If AbaPerls finds a CREATE TABLE for a temp table outside a procedure or a trigger, AbaPerls adds an explicit DROP to the post-work code so that the temp table does not hang round when you run DBBUILD or an update script generated by DBUPDGEN.
In SQL7 and later versions, if AbaPerls finds the string suser_name(), AbaPerls will replace this with SYSTEM_USER, since suser_name() should not be used on SQL7 and later and on SQL2000 it always return NULL.
AbaPerls always inserts SET NOCOUNT ON as the first command in a stored procedure or trigger. This is because most clients makes no use of the DONE_IN_PROC messages (which corresponds to the "rows affected" lines you see in Query Analyser), that are generated when NOCOUNT is OFF. They cause an extra load on client, server and network. They may also confuse ADO clients that have not forseen them. Thus, if you want this "rows affected" messages sent back to the client, you need to insert an explicit SET NOCOUNT OFF in your code.
This change is not applied if AbaPerls finds EXTERNAL NAME in the file, as the object in this case is a CLR object.
If the configuration option -crypt is active, AbaPerls inserts WITH ENCRYPTION in the definition of stored procedures, views, triggers and functions. This is not applied for CLR objects.
If the configuration option -quoterepl is in effect, AbaPerls replaces double quotes that are string delimiters with single quotes. (See the description of -quoterepl for details.)
If the configuration option -subscriber is active, AbaPerls changes timestamp to varbinary(8) and removes the IDENTITY property.
For SQL7 only, AbaPerls splits up ALTER TABLE statements in .FKEY files, so that there is one ALTER TABLE statement for each constraint. This is due to a bug in SQL7, which may cause the SQL Server to terminate the connection because it runs into an assertion error. The bug is corrected in SQL2000, and supposedly also in SQL7 SP3.

Retrieving Name of Current Subsystem

AbaPerls creates a temp table #current$subsystem:

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

There is one row in this table, and 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 example.)