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.)
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
Checks for CREATE TABLE
Check that $DEPENDSON is Present
Pre-SQL Analysis: Permissions on Objects
Pre-SQL Analysis: Other Miscellaneous Fixes
Retrieving Name of Current Subsystem
‑cryptconfiguration option is in force.
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
AbaPerls only performs steps 1, 2 and a modified version of step 4:
Preppis processes only the directives $INCLUDE,
EXTERNAL NAME and
$USEDBY to retrieve all dependent files and to create DLLs from source
files written in .Net languages. You combine
‑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
sp_tableoption. In this case, AbaPerls restarts the
operation on step 7, but without starting a transaction. The error is not
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.
When AbaPerls is fed a filename to process, AbaPerls may look for the file in these locations:
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
‑use_disk is in force. b)
|ABASQL||Version-control is active with ABASQL
if you specify a VC-path with |
|DBBUILD|| Version-control is active with DBBUILD if specify a config-file with
|Update scripts||Update scripts always have version-control
active, unless you explicitly give the option |
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
‑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
‑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
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
‑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:
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.
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.
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.
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
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.
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
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:
Note here that sub is included in both cases.
You are in C:\Build and there is a structure
‑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 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.)
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.
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
These are the checks:
‑VCoption, you must load it from the same directory in version-control 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 version-control 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
‑labeloption 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.)
‑rebuild, simply specify
‑environment DEVtogether with
‑force, and then restore the setting when you are done.)
abasqlfile('BETA!other_sp.sp'). This load is only performed if the MD5 hash for other_sp.sp matches the hash stored in abasysobjects. If they do not match, this results in an error (i.e., the severity level is > 10.) Presumably, other_sp.sp was included in the script, because other_sp depends on a file in ALPHA, for instance an include-file and needs to be refreshed. In this case, it would not be safe to load a different version of the file, because you may not be running an update script for BETA, and a different version of other_sp could break functionality in BETA.
Note: "loading from version-control", includes loading from a structure previously
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
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:
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.|
If there is no table column or procedure parameter of this type, AbaPerls drops and recreates the type. If the command is
|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.
AbaPerls does not generate any |
AbaPerls does not generate any |
What is said here, applies to permanent tables, not temp tables.
|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.)|
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.|
|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 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.
. AbaPerls attempts to handle them, but testing has not been rigorous, and there may still be holes. (When you define CLR object, you may want to use quoted names in the EXTERNAL NAME clause. This is tested and tried.)
should be applied consistently.
@par1 AS int. Don't use this with AbaPerls, who thinks that AS is a great place to insert a SET NOCOUNT ON or WITH ENCRYPTION. (The syntax errors you get are very mystifying.)
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.
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 ASThis 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
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
‑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
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.
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:
COLLATE database_default, unless there already is a COLLATE clause for the column.
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.)
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
‑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
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
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,
,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
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:
sp_renameto change the name, and avoids dropping the index.
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.
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:
‑forceswitch. You also get an error with update scripts generated by DBUPDGEN after the check was introduced. With DBBUILD and update scripts generated by DBUPDGEN before the check was introduced, you get a warning with level 9. Note that if an existing script is regenerated with DBUPDGEN, it still falls under the latter category; that is, the check yields a warning. AbaPerls uses this model for new checks to give you a grace period so that you can change existing code. You can count on that in some future version of AbaPerls, that the syntax in question will yield an error with all tools.
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:
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.
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 ...
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
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.
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:
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.
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.
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.
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:
^and a closing
$to the regular expression. They match start and end respectively, so that the pattern you specify must match the entire object name, and not only a substring. For instance, the pattern zz_.* above matches zz_cleanup_sp, but does not match fuzz_sp.
\s(which matches any white-space) or
\_which is a special AbaPerls convention).
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:
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
‑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
‑revokeall to implement the change.
suser_name(), AbaPerls will replace this with SYSTEM_USER, since
suser_name()at least for a while was deprecated and could return NULL.
‑cryptis active, AbaPerls inserts WITH ENCRYPTION in the definition of stored procedures, views, triggers and functions. This is not applied for CLR objects.
‑quotereplis in effect, AbaPerls replaces double quotes that are string delimiters with single quotes. (See the description of
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.)
Copyright © 1996-2016,
Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 16-09-13 9:55