Microsoft SQL Server

Tables that do not belong to owner DBO

In previous versions of LANSA, the table owner was not used to access SQL Server Tables at execution time. This was because versions prior to SQL Server Version 2000 did not implement a complete owner solution.

For backward compatibility, existing configurations and compiled OAMs will continue with the original behavior, which is described in Original Behavior below.

If you use SQL Server as your LANSA database and you created it using a version of LANSA prior to LANSA V11.3, and if you have deployed applications that also use SQL Server, you must NOT use this new feature. You must continue to use a LANSA database created by prior versions of LANSA. This is because there is no migration possible from a database that does not support collections to one that does.

If you are using a SQL Server LANSA database for the first time with LANSA V11.3 or you use Oracle or Adaptive Server Anywhere as your LANSA database, then this feature can be used.

Previously, the generation of OAMs for SQL Server PC Other table would always leave the table owner out of the generated SQL. Now, the table owner can be included in SQL in the OAM if the following instructions are followed:

• If you use SQL Server as your LANSA database, install from the LANSA V11.3 CD (or later) into a new configuration, and rebuild the OAM for the table.

• If you do NOT use SQL Server as your LANSA database, modify X_DBMENV.DAT. To do so, from your LANSA System's desktop folder, select Settings and Administration, Utilities and then Databases.

     Locate the settings for the SQL Server database, and ensure that these parameters are set as shown here: DATABASE_TYPE=MSSQLS, SUPPORTS_COLLECTIONS=YES. Rebuild the OAM for the table.

Note:

• No changes are required to deployed systems, only the generation of OAMs is affected.

• Table does not need to be reimported.

• New configurations running with SQL Server always use collections, so a table accessed at execution time must have the same owner as at import time.

Original Behavior

If the table does not belong to dbo (Microsoft and LANSA both recommend that all tables belong to dbo), the result may be that the table may not be found at runtime, or a different table (with the same name) may be accessed instead.

Any SQL Server user may automatically create tables as dbo using the stored procedure sp_addalias. For example: sp_addalias user1, dbo. Refer to your SQL Server documentation for more details.

Using SQL Server datatype Smalldatetime

SQL Server's Smalldatetime supports a very limited range of dates, therefore do not attempt to use the system variable *TIMESTAMP_HIVAL with fields of this type.

Also See

Handling of Timestamp or DateTime columns

PC Other Tables