Object Checking

When AbaPerls has successfully loaded a file to SQL Server (step 8 in the AbaPerls file-loading process), AbaPerls performs Object Checking to find errors that SQL Server is too lazy to report itself, as well as breaches against the subsystem order. This page details these checks.

   Missing Objects
   Misspelled Object Names
   Incorrect Subsystem References
   Extra Checks for Calls to Stored Procedure
   Limitations with Object Checking
      Table Sources
      Scalar Functions
      User-defined Types
      Stored Procedures
      Service Broker Objects

Missing Objects

SQL Server has a (mis)feature known as deferred name resolution. If certain types of objects are missing when you create a procedure or trigger, SQL Server does not report any errors or warnings. It's not until you execute the actual statement that you will get an error message. Furthermore, if the procedure creates a temp table, and there is a query with the temp table and other tables in the procedure, SQL Server may not report errors related to other tables in that query, e.g. a misspelled column name. As the temp table does not exist when the procedure is created, SQL Server defers compilation of the entire query.

Deferred name resolution applies to the following types of objects:

In addition, a call to a non-existent stored procedure only produces an informational message, but at least SQL Server reports it.

AbaPerls performs a number of actions to nullify the effects of deferred name resolution. One part of Pre-SQL Analysis, step 5 in the AbaPerls Load order is to extract temp-table definitions and create the temp tables as part of the pre-work code. Thereby, SQL Server will not neglect checking queries involving temp tables. (A side effect is that this causes the dependencies to the tables in these queries to be recorded in sys.sql_dependencies, which else would not have happened.) Also during pre-work, AbaPerls sifts through the code and when it finds a keyword or sequence of keywords that is typically followed by an object for which SQL Server employs deferred name resolution, AbaPerls makes a record of that reference.

If the file loads with errors, AbaPerls performs no actions with the data it has recorded as in this case, AbaPerls may have recorded nonsense. On the other hand, if the file loaded successfully, AbaPerls verifies that all objects it has recorded actually exist and reports all objects that it is unable to find. Depending on the tool and the object type, the missing object may be reported as a warning or an error. If any object is reported as an error, loading of the file is rolled back. The following applies:

Misspelled Object Names

Depending on collation, SQL Server may accept deviations from the string that is recorded as the name in the system catalogue. For instance, if the collation is Finnish_Swedish_CI_AI and the object was created as Wally, here is a list of variations that all are accepted by SQL Server: vally, WÁLLY, vãllü. Since this can cause problems if you deploy the database on a different collation, AbaPerls takes a much stricter approach, and requires a binary match for objects names. Thus, the object is reported as missing, but AbaPerls adds a hint to the error message about the correct spelling of the object.

AbaPerls is only this zealous for objects that are recorded in abasysobjects and only if the ABAPERLS subsystem is of version L1.0.0290 or later. For other names, AbaPerls accepts the name if there is a match according to the metadata collation for the database. That is, AbaPerls permits you to say SYS.OBJECTS or information_schema.tables as long as SQL Server agrees. This does not mean you should do it.

Incorrect Subsystem References

If your database consists of many subsystems, you may not want to allow subsystems to refer each other willy-nilly. Say that you have the subsystems A, B, C, CUST1 and CUST2 and you build the subsystems in this order. If there is a stored procedure in A that refers to a table in B, this will cause an error when you run DBBUILD, because when DBBUILD loads the procedure, the table does not exist. Furthermore, even if you build CUST1 before CUST2, you cannot permit references from CUST2 to CUST1, since these are customer-specific subsystems, and CUST1 is not shipped to Customer2 (and vice versa.) It could also be the case that you cannot permit references from CUST2 to C, because C is an optional component that Customer2 does not have.

However, a developer who writes a new stored procedure (or some other SQL object) and loads the procedure with ABASQL may not be aware of the build order, or which subsystems that are shipped to which customer. But since the developer loads the object to a development database with all subsystems, he does not get any error about missing objects. They will only appear later when you build an empty database with DBBUILD or run an update script generated by DBUPDGEN for that customer, causing a delay as you have to go back to the developer to have him to fix the code. (Which may be non-trivial, as the reference is likely to be due to an incorrect design.)

For this reason, AbaPerls permits you specify reference rules in the file SYSTEM.DEFINITION as detailed on the sysdef-file page. AbaPerls only performs reference checks if it can find SYSTEM.DEFINITION, the rules for finding this file is discussed in the topic on the sysdef-file page. AbaPerls reports all breaches against the reference rules as errors. If there is an object which is not stored in abasysobjects, or for which the subsystem column is NULL in abasysobjects, AbaPerls always permits the reference.

This check is performed for the following types of objects:

Extra Checks for Calls to Stored Procedure

In most other languages, the compiler makes a whole lot of checks when you call a function/procedure/method, and will give you an error message if you leave out required parameters etc. Not so in SQL Server. This is where AbaPerls steps in. AbaPerls checks for the following suspect conditions:

  1. Not supplying mandatory parameters.
  2. Supplying DEFAULT for a mandatory parameter.
  3. Not receiving the return value when you call a scalar UDF with EXECUTE.
  4. Supplying too many parameters.
  5. When using named parameters, specifying a formal parameter which does not exist. For instance, if the procedure definition is
    CREATE PROCEDURE some_sp  @a int AS ...
    and the call is
    EXEC some_sp @b = 12
    In this case, AbaPerls will flag @b as an error.
  6. Specifying a parameter both as positional and as named. For example, using the same procedure as a above:
    EXEC some_sp 12, @a = 9
  7. Specifying a parameter as OUTPUT, when the formal parameter is not OUTPUT.
  8. Specifying the same variable for more than one OUTPUT parameter, or as both return value and OUTPUT parameter, for instance:
    EXEC @ret = some_sp @a, 2, @ret OUTPUT
  9. Specifying a unquoted string as a parameter value, for instance
    EXEC some_sp myvalue
    This is permitted by SQL Server (to permit you to say things like sp_help mytable), but there is little reason to leave out the quotes. And more importantly, maybe you meant the variable @myvalue.
  10. Not specifying OUTPUT when the actual variable is a variable, and the formal parameter is declared as OUTPUT. Given
    CREATE PROCEDURE some_sp @a int, @ret int OUTPUT AS ...
    this is OK:
    EXEC some_sp 16, NULL
    (since you specified a constant, it's reasonable to assume that you don't care about the return value). But this is suspect:
    EXEC some_sp 16, @ret

Conditions 1-9 are reported as errors with ABASQL and update scripts generated by DBUPDGEN from AbaPerls version of L1.0.0290 or later. With DBBUILD and earlier update scripts they are reported as warnings. (It is likely that they will be always reported as errors with a later version of AbaPerls.)

Condition 10 is reported as a warning on level 10 with ABASQL and update scripts generated by DBUPDGEN from AbaPerls version of L1.0.0290 or later. With DBBUILD and earlier update scripts, the condition is reported on level 9. (You may have reason to ignore the value set by the called procedure, but it could be prettier if you copied the value to a dummy variable that you can permit to be destroyed.)

AbaPerls is not always able to check for these conditions. The following waivers apply:

Limitations with Object Checking

AbaPerls does not have a full-blown T‑SQL parser, and there are a couple of situations which it is not able to cope with, which can lead to that AbaPerls fails to report missing objects, or that AbaPerls incorrectly reports objects as missing. In this section we detail the limitations per object class.

Table Sources

Finding table sources is mainly a simple game for AbaPerls as they follow directly on certain keywords: FROM, JOIN, UPDATE etc. However the older comma notation presents some challenges, and AbaPerls is not able to overcome all. As long as your comma-list only includes tables, views and calls to user-defined table functions, AbaPerls will verify all table sources. For instance in:

FROM  alpha, beta WITH (HOLDLOCK, UPDATE), gamma(2, 3, 4), delta, nosuchtable

AbaPerls will find nosuchtable and flag it as missing. As the example indicates, index hints presents no obstacle for AbaPerls.

However, AbaPerls stops examining the comma-list, as soon as AbaPerls encounters any of the following:

That is, in the following examples, AbaPerls will not find the missing table:

FROM   alpha, beta, OPENQUERY(SERVER, 'SELECT * FROM db.sch.tbl'), nosuchtable
SELECT alpha, beta, (SELECT col, COUNT(*) AS cnt FROM gamma GROUP BY col), nosuchtable
FROM   alpha
JOIN   beta ON alpha.col = beta.col
JOIN   gamma ON beta.col2 = gamma.col2, nosuchtable

Instead of a schema declaration column by column, OPENXML permits you to specify a table name:

FROM   OPENXML(@idoc, '/Root/node')
WITH   myxmltable

AbaPerls is not aware of this possibility and will not flag myxmltable as missing.

There is an issue with temp tables and triggers. AbaPerls extracts all temp table definitions and creates the temp tables as part of pre-work and drops them later. But a trigger file can include multiple triggers, why an incorrect reference to a temp table in a different trigger will go unnoticed. Here is an example:

CREATE TRIGGER katiuser_tri ON katiusers FOR INSERT AS
CREATE TRIGGER katiuser_update_tri ON katiusers FOR UPDATE AS

Finally, AbaPerls is generally unable to check objects on remote server, so AbaPerls ignores:

SELECT ... FROM SERVER.db.dbo.nosuchtable

Then again, this does not matter to you, because SQL Server does not employ deferred name resolution for tables on linked servers, so SQL Server will report this error.

Scalar Functions

Due to the ambiguous syntax in T‑SQL, scalar functions presents the biggest challenge for AbaPerls. Consider:

SELECT thatotherdb.dbo.somefunc(1, 2), dbo.somelocalfunc(2)
FROM   ...
SELECT T.c.value('@attr'), 'int'), c.value('@otherattr', 'smallint')
FROM   @xml.nodes('/Root') AS T(c)
SELECT A.geocol.AsGml(), geocol.AsTextZM()
FROM   tbl A

From the names, it is reasonable to assume that the first two are calls to user-defined functions, and the last four are invocations of type methods. But there is no way to tell syntactically, and if there also is a database T with a scalar function c.value, SQL Server would give us an error for T.c.value saying that it is ambiguous.

To be able to correctly validate the three-part names above, AbaPerls would first have to determine whether the first component (thatothredb, T or A) is a table source that visible in the query, and only if not it, could look for a user-defined function in that database and schema. This is anything but trivial in a query with nested derived tables and subqueries, and in the queries with two-part names, the task is even more daunting, as AbaPerls would have to determine whether the leading part is a column which is visible at this point in the query.

Instead AbaPerls cuts corners, and the following rules apply:

From this follows that you should always refer to type methods for XML, spatial types and other CLR types with three-part names.

Finally, we can note the below is flagged as an error by AbaPerls:

DECLARE @x sys.char(4)

This is perfectly legal SQL, but we have not seen anyone actually use it – we think.

User-defined Types

Like scalar functions, user-defined data types presents a challenge for AbaPerls to identify syntactically. It knows that a type can only follow another identifier, possibly with an AS in between. This can occur in a parameter list, in a DECLARE statement and in a table definition, but the sequence can appear in other contexts as well and where the second name is not a type name. Since SQL Server does not have deferred name resolution for data types, AbaPerls only needs to verify that the spelling of the type is exact and it is not an illegal subsystem reference. Therefore, AbaPerls only has to check names that it can identify as an existing data type. Nevertheless, if you use a type name for something else, AbaPerls could incorrectly flag it as an error.

Assume that there are three subsystems, A, B and C with that build order. A defines a type MyType and C defines a type MyOuterType. In B there is a stored procedure with this SELECT:

SELECT a.col AS mytype, a.col2 MyOuterType

AbaPerls will flag both as errors. mytype because the spelling is different and MyType because of the incorrect reference. Note that this SELECT will not yield an error:

SELECT mytype = a.col, MyOuterType = a.col2

This is because a type name can never follow SELECT or a comma.

Stored Procedures

AbaPerls is not able to verify that stored procedures on linked servers exist. (And in this case, SQL Server will not report anything at compile-time) Nor does AbaPerls attempt to verify EXEC @spname.

See also the limitations listed in the section Extra Checks for Stored Procedures.

Service Broker Objects

Service Broker queues are subject to the same rules as for tables.

Note that in BEGIN DIALOG, AbaPerls only validates the FROM service. AbaPerls does not validate the TO service, as it could exist in another database, on another server or on another planet.