PRB: Host_name() Function Returns NULL Value

• Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

After upgrading from version 1.x to 4.2, an application or stored procedure that uses the system function host_name() to insert a value into a row generates the following error message (error 515):

   Attempt to insert the value NULL into column '%.*s', table '%.*s';
   column does not allow nulls. Update fails.

CAUSE

The system function host_name() has changed behavior between version 1.x and 4.2. The host_name() function is supposed to return the value that was set by the front-end application by calling DBSETLHOST. If this was not set by the application, the return value depends on the version of the SQL Server; version 1.x returns a string of 30 spaces, and version 4.2 returns a NULL value.

RESOLUTION

There are several ways to address this situation. The application could be changed to call DBSETLHOST to provide a value. Secondly, the column that is used to store the host_name could be changed to allow NULLs, or, in the case of a stored procedure, the ISNULL function could be used to provide a value when host_name returns NULL.