Migrating Oracle to SQL Server using SSMA–Error O2SS0041 Illegal Identifier

When converting Oracle schema to SQL Server using SSMA, you may encounter an error when your table contains a DATE column with default value. This blog post describes the reason for the error and what you should do when you encounter this error.

Consider the following example

-- Oracle source table:
CREATE TABLE TEST.T1
(
      COL1 DATE DEFAULT to_date('01/02/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
      COL2 DATE DEFAULT sysdate
);

-- Converted table using SSMA:
CREATE TABLE
[dbo].[t1]
(
   /*
   *   SSMA error messages:
   *   O2SS0041: Identifier 'sysdb.ssma_oracle.to_date2('01/02/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS')' cannot be prefixed with a database name in this context.
   */
   [COL1] datetime2(0)  NULL,
   [COL2] datetime2(0) DEFAULT sysdatetime()  NULL
)

SSMA converts Oracle’s to_date function with an emulator function (stored in SYSDB database when you install SQL Server Migration Assistant Extension Pack): sysdb.ssma_oracle.to_date.

The emulation function is used to ensure the date is stored according to the specified format. For example:

-- Oracle original statement
SELECT to_date('01/02/2010 00:00:00', 'DD/MM/YYYY HH24:MI:SS') FROM DUAL;

-- converting using SQL’s CAST statement will result in the following (using default SQL_Latin1_General_CP1_CI_AS collation)
SELECT CAST('01/02/2010 00:00:00' as datetime)
-- result: 2010-01-02 00:00:00.000 (January 2, 2010)

-- converting using SSMA emulator will result in the same value with Oracle (regardless database collation setting)
SELECT sysdb.ssma_oracle.to_date2('01/02/2100 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
-- result: 2100-02-01 00:00:00.0000000 (February 1, 2010)

However, when to_date is used as default value in the table definition, the expression refers to the emulator function in a separate database – which is not permitted in SQL Server:

USE test2
GO
CREATE FUNCTION  dbo.fn_const(@const INT) RETURNS INT
AS
BEGIN
    RETURN @const
END
GO
USE test
go
CREATE FUNCTION  dbo.fn_const(@const INT) RETURNS INT
AS
BEGIN
    RETURN @const
END
Go
CREATE TABLE tbl1 (Col1 INT DEFAULT dbo.fn_const(1))
-- successfully created
CREATE TABLE tbl2 (col1 INT DEFAULT test2.dbo.fn_const(1))
/*
Msg 128, Level 15, State 1, Line 1
The name "test2" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
*/

When you encounter the SSMA error, you can proceed with the conversion and add the default value post conversion:

ALTER TABLE [dbo].[t1] ADD DEFAULT CAST('02/01/2010 00:00:00' AS DATETIME2) FOR [COL1]

Note that you have to make sure to set the default value with string format consistent with your collation setting.