Known Issues

This page detail known issues with various AbaPerls commands.

Contents:
    General Restrictions
    Known Issues with Object Checking
    Uniqueness of Index Names
    Quoted identifiers

General Restrictions

AbaPerls does not support Unicode. Files fed to AbaPerls must use the ANSI code page of the computer. (That is, the code page which is used by regular Windows applications.)

The following features in T-SQL have not been considered:

Computed columns.
CREATE SCHEMA, neither in the old funny 6.5 form, or the new "real" schemas in SQL 2005.
Version numbers on stored procedures.
Global temp-tables.
Change Tracking
User-defined aggregates.

This is list is far from exhaustive. When we say "not been considered", means that you may still be able to use the features with AbaPerls, but there is to warranties for that things will work out fine.

Known Issues with Object Checking

When AbaPerls performs object checking as part of its file-loading process, there are a couple of cases where AbaPerls will not find a object reference in the code, and thus fail to verify its existence. There are also cases where AbaPerls will flag existing objects as missing.

Failing to find

When AbaPerls parses the code to find references to tables, views and table-valued functions, it listens for the keywords INSERT, UPDATE, DELETE, MERGE, FROM and JOIN and collects all names that are verifiable within the server (i.e. four-part names that specifies a remote/linked server are ignored), that follows these keywords with three exceptions.

When consuming all table names that may follow FROM in a comma-separated list, AbaPerls stops it if finds a rowset function (e.g. OPENXML) or a derived table. Thus, AbaPerls does not verify tables that follow rowset functions or derived tables in FROM lists. Use the ANSI-style joins with these constructs.
If a file defines two or more triggers, and one trigger creates a temp table and a another trigger has a reference to a temp table with the same name, but fails itself to create with the temp table, this will not result in an error message. This is because AbaPerls creates the temp tables before running the file, and drops them after.
While the syntax below is permitted, AbaPerls is too innocent to believe that anyone would actually use it:
```
SELECT col1, col2
FROM   table a
JOIN   tblb b ON a.keycol = b.keycol, tblc c
WHERE  c.keycol = b.keycol
```
If you have a call to a UDF with the database name included, i.e. db.schema.func, AbaPerls will not verify that db.schema.func exists.

There are a few more known cases where AbaPerls does not flag identifiers, although it should. But they are a bit too much of edge cases to take up space here. Add to this the cases I did not think of, and you should understand that while AbaPerls does a good job, SQL Server could do it better – if it only did it.

Flagging existing objects

AbaPerls has two methods to look up the objects it has found in the database. Both have their restrictions.

Method A – AbaPerls employs this method only if the stored procedure ap_check_existence_sp and the user-defined function ap_list_to_table are available in the database, else it reverts to method B. They were added to AbaPerls post label L1.0.0021. With method A, AbaPerls finds all objects, except:

Objects in other servers. (These are entirely exempted from object checking.)
Table-valued functions in other databases, or owned by other database users than dbo. You may have to use -force for these. This restriction applies to SQL 2000 only, not SQL 2005 and later.
Methods of XML and CLR methods which AbaPerls cannot distinguish from user-defined functions. You can suppress this check by using a column prefix, for instance tbl.col.method().

Method B – With this fallback method, AbaPerls only finds 1) tables, views and user-defined functions defined in the local database 2) temp tables. All referenses with two- or three-part names, for instance master..syslogins, will result in warnings for non-existing objects.

Note that for scalar functions, AbaPerls does verify all names since the T-SQL syntax requires you to specify the owner when you call a user-defined scalar function.

Flagging the wrong objects

If you use this construct:

FETCH FROM some_cursor

That is, you are using FETCH to produce a result set rather than copying the values into variables with the INTO clause, AbaPerls will flag the cursor as non-existing. The workaround is simple: remove FROM which is optional in the T-SQL syntax.

SELECT INTO from Table Variables

As part of object checking, AbaPerls tries to create all temp tables created in the procedure. This can fail on harsh circumstances. One such case is if the FROM clause includs a table variable. Another is if the query includes CTE. The workaround is to create the table with CREATE TABLE.

Uniqueness of Index Names

In difference to SQL Server, AbaPerls requires an index to be unique in a database. However, AbaPerls fails to enforce this, so you may in fact load two indexes with the same name. But this can other side effects, as other parts of AbaPerls may rely on that the name is unique, and a later version of AbaPerls will raise an error if you try to create a second index with the same name.

Quoted Identifiers

AbaPerls makes a half-hearted attempt to support identifiers quoted with "" or [], but more works needs to be done before AbaPerls can be said to fully support this. The known restrictions are:

Names including a single quote, e g [Jane's table] do not work and yields error messages.
When generating table-updates, DBUPDGEN does not consider quoted names.
Overall, testing has not been extensive in this area.

Note also that by default, AbaPerls replaces all occurrences of double quotes as string delimiter with single quotes, so if you wish to use double quotes to delimit identifiers (which is ANSI compatible in difference to [] which is SQL Server-specific), you must use the configuration option -noquoterepl.