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.

Contents:
   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:

Table sources (tables, views, table-valued functions).
Scalar user-defined functions.
Sequences.
Service Broker objects: queues, services, contracts and message types.

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:

If the ABAPERLS subsystem is not present in the database, all missing objects are reported as warnings.
If the ABAPERLS subsystem is present, all missing objects with the exception of stored procedures, are reported as errors. (All following bullets assume that the ABAPERLS subsystem is present.)
With ABASQL, a missing stored procedure is always reported as an error.
To faciliatte development when you wat to play around, you can use the ‑force option with ABASQL (only!) to downgrade these errors to warnings, if you are in a development or test database. You will get an extra warning to remind you that will need to fix this before checking in.
With DBBUILD a missing stored procedure is always reported as a warning. There are three different warnings:

The procedure is unknown to DBBUILD. While DBBUILD reports it as a warning, it is presented as an error when you view the log with LISTERRS. (And this may become an error with DBBUILD in a later release of AbaPerls.)
DBBUILD knows that this procedure will be loaded later for the same subsystem. In this case, DBBUILD requeues the procedure for a second load, and issues a warning that it could not perform parameter checking.
DBBUILD knows that the called procedure failed to load. In this case, DBBUILD permits the caller to load, but issues a warning about the dependency on the missing procedure.

With update scripts generated by DBUPDGEN, a missing stored procedure can be either an error or a warning:

If the update script was generated by an older version of DBUPDGEN (before version L1.0.0290 of AbaPerls), a missing procedure is always a warning (as the script has no means for requeueing). This warning is displayed as an error by LISTERRS.
For update scripts generated with L1.0.0290, a missing procedure is an error with these two exceptions:
- The called procedure appears in the same SP section in which case the update script requeues the procedure and loads it a second time. The first load produces a warning, which you normally will not see with LISTERRS (as LISTERRS only displays the last load of a file).
- The update script knows that it has attempted to load the missing procedure and this failed. This results in a warning.

As mentioned above, SQL Server produces a warning about missing procedures. AbaPerls suppresses these warnings in favour of its own messages.

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:

User-defined data types.
Table sources (tables, views, table-valued functions).
Scalar user-defined functions.
Stored procedures.
Sequences.
Service Broker objects: queues, services, contracts and message types.

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:

Not supplying mandatory parameters.
Supplying DEFAULT for a mandatory parameter.
Not receiving the return value when you call a scalar UDF with EXECUTE.
Supplying too many parameters.
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.
Specifying a parameter both as positional and as named. For example, using the same procedure as a above:
```
EXEC some_sp 12, @a = 9
```
Specifying a parameter as OUTPUT, when the formal parameter is not OUTPUT.
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
```
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.
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:

Calls to remote stored procedures on linked servers are not checked at all.
Calls to procedures named through a variable (e.g. EXEC @mysp) are not checked at all.
For calls to extended stored procedures and procedures referred to through synonyms, AbaPerls only checks for conditions 8 and 9, as the parameter list is not known.
If the called stored procedure is obfuscated with WITH ENCRYPTION, AbaPerls assumes that all parameters are optional. (For T‑SQL procedures, SQL Server does store in metadata whether a parameter has a default or not, why AbaPerls needs to parse the code to find out.)

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:

SELECT ...
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:

A rowset function (OPENXML, OPENQUERY, OPENROWSET, OPENDATASOURCE).
A derived table.
A JOIN or APPLY operator.

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

SELECT ...
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

SELECT ...
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:

SELECT ...
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 TABLE #temp(a int NOT NULL)
SELECT a FROM #temp
go
CREATE TRIGGER katiuser_update_tri ON katiusers FOR UPDATE AS
SELECT a FROM #temp

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:

If a two-part name is followed by left parenthesis, AbaPerls assumes this to be a call to a user-defined function, and if it cannot find any such UDF, this is an error. That is, in the last two examples, AbaPerls will give you an error for c.value and geocol.AsGml despite that they are legit SQL.
AbaPerls entirely ignores three-part names followed by a left parenthesis. That is, you will not get an error for SELECT thatotherdb.dbo.nosuchfunction(). Or for that matter SELECT nosuchdb.dbo.some_fun().
In addition, in UPDATE and MERGE statements AbaPerls ignores two-part names followed by a left parenthesis where the last part is write or modify. (Because these are type methods that could appear in the SET clause where a prefix may not be permitted.)

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.