INF: SELECT INTO or Trans Mgr Can Unexpectedly Update NULL Col

The information in this article applies to:

• Microsoft SQL Server, version 4.21

SUMMARY

NULL column values may be updated when you use SQL Transfer Manager or the SELECT INTO statement to move data, due to a nullable user-defined data type having a future-only default binding at the source.

This is the expected behavior because the tables you are creating use the defaults as bound.

MORE INFORMATION

When SQL Transfer Manager generates the creation script for the destination, it first creates the default data types, then any defaults that need to be bound to the user-defined data types; it then binds them to the data types and creates the tables.

SELECT INTO creates the new table from current settings which show the user-defined data type as having a default bound to it.

In the following scenario, the data movement causes the firing of the user-defined default, replacing NULL values with the default value:

Source

1. Create a user-defined data type; nullable.

2. Create a table which uses the new data type, making the user-defined data type column nullable also.

3. Insert data into the table, make sure to add rows with NULL values for the specified user-defined data type column.

4. Create a default and bind it only to the user-defined data type, using the future only option.

5. Create a new table, similar to step 2, and compare the cdefault and tdefault columns in syscolumns and systypes, respectively.

   The new table should contain a column binding and the original table from step 2 should not. Accordingly, when you insert values into the second table, you should be able to cause the default to fire, where as it should never fire on the first table.

   To retrieve a list of user-defined data types that are in use and have defaults bound to them but not to specific columns, the following select statement may help:

         select object_name(c.id), c.name, t.name
         from    syscolumns c, systypes t
         where t.tdefault != c.cdefault and
               t.usertype = c.usertype and
               (c.cdefault = 0 or t.tdefault = 0)
   
   

Destination

1. Establish a new clean and empty database.

   Once both the source and destination are prepared, use SQL Transfer Manager to login to both the source and destination. Do not select the all objects option. Select the include dependencies, include drops, and include data options. Transfer the table from source to destination.

   At the destination you will see that the user-defined data type was created, then the default bound to it, and finally the table was created. This can be verified in the script which SQL Transfer Manager built.

   When the data is moved, the NULL data from the source invokes the default, and the data changes to reflect the default value.

Work Around

In order to move data which falls into the 'future only' scenario without modifying the NULL values, you need to generate scripts from the SQL Object Manager. Modify the scripts so the defaults are not bound, then create the new objects at the destination. Use SQL Transfer Manager, but do not include drops to move the data.

By not including drops, you avoid the recreation of the objects which you have previously completed. Once the data is moved, bind the defaults accordingly.

For the SELECT INTO case, you need to identify the user-defined data type bindings and unbind them before you perform the command. Be sure you replace the bindings correctly after the SELECT INTO operation is complete.

You should place the database in single user mode while you complete the process so other data modifications that depend on the default are not inadvertently affected.