NullReferenceException when creating a table with DateTime Column(SMO, C#)

Question

My application is used to copy tables from one database and duplicate them to another, I'm using smo and C#. My code:

 private static void createTable(Table sourcetable, string schema, Server destinationServer, 
        Database db)
    {
        Table copiedtable = new Table(db, sourcetable.Name, schema);

        createColumns(sourcetable, copiedtable);

        copiedtable.AnsiNullsStatus = sourcetable.AnsiNullsStatus;
        copiedtable.QuotedIdentifierStatus = sourcetable.QuotedIdentifierStatus;
        copiedtable.TextFileGroup = sourcetable.TextFileGroup;
        copiedtable.FileGroup = sourcetable.FileGroup;

        copiedtable.Create();
    }

private static void createColumns(Table sourcetable, Table copiedtable)
    {

        foreach (Column source in sourcetable.Columns)
        {
            Column column = new Column(copiedtable, source.Name, source.DataType);
            column.Collation = source.Collation;
            column.Nullable = source.Nullable;
            column.Computed = source.Computed;
            column.ComputedText = source.ComputedText;
            column.Default = source.Default;

            if (source.DefaultConstraint != null)
            {
                string tabname = copiedtable.Name;
                string constrname = source.DefaultConstraint.Name;
                column.AddDefaultConstraint(tabname + "_" + constrname);
                column.DefaultConstraint.Text = source.DefaultConstraint.Text;
            }

            column.IsPersisted = source.IsPersisted;
            column.DefaultSchema = source.DefaultSchema;
            column.RowGuidCol = source.RowGuidCol;

            if (server.VersionMajor >= 10)
            {
                column.IsFileStream = source.IsFileStream;
                column.IsSparse = source.IsSparse;
                column.IsColumnSet = source.IsColumnSet;
            }

            copiedtable.Columns.Add(column);
        }
    }

The project perfectly well works with North wind database, however, with some tables from AdventureWorks2014 database I get the following inner exception at copiedtable.Create();:

NullReferenceException: Object reference not set to an instance of an object.

I suspect, that AdventureWorks datetime column may be causing the problem (Data is entered like: 2008-04-30 00:00:00.000)

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Tetsuya Yamamoto, Jul 25 '17 at 09:45

score 0 · Accepted Answer · answered Jul 25 '17 at 12:21

I have solved this problem myself and it was quite interesting. I couldn't find any null values neither in the Table itself, nor in it's columns. Then I realized, that AdventureWorks2014 DB used User defined Data Types and XML Schema collections. As I haven't copied them, they couldn't be accessed and the creation of the table failed. It was only necessary to copy XML Schema Collections and User Defined Data Types to second database:

private static void createUserDefinedDataTypes(Database originalDB, Database destinationDB)
    {
        foreach (UserDefinedDataType dt in originalDB.UserDefinedDataTypes)
        {
            Schema schema = destinationDB.Schemas[dt.Schema];
            if (schema == null)
            {
                schema = new Schema(destinationDB, dt.Schema);
                schema.Create();
            }
            UserDefinedDataType t = new UserDefinedDataType(destinationDB, dt.Name);
            t.SystemType = dt.SystemType;
            t.Length = dt.Length;
            t.Schema = dt.Schema;
            try
            {
                t.Create();
            }
            catch(Exception ex)
            {
                throw (ex);
            }

        }

    }
private static void createXMLSchemaCollections(Database originalDB, Database destinationDB)
    {
        foreach (XmlSchemaCollection col in originalDB.XmlSchemaCollections)
        {
            Schema schema = destinationDB.Schemas[col.Schema];
            if (schema == null)
            {
                schema = new Schema(destinationDB, col.Schema);
                schema.Create();
            }
            XmlSchemaCollection c = new XmlSchemaCollection(destinationDB, col.Name);

            c.Text = col.Text;
            c.Schema = col.Schema;


            try
            {
                c.Create();
            }
            catch(Exception ex)
            {
                throw (ex);
            }

        }

    }

NullReferenceException when creating a table with DateTime Column(SMO, C#)

1 Answers1